+ Reply to Thread
Results 1 to 4 of 4

Parsing data from text file to create scatter plot

  1. #1
    Registered User
    Join Date
    01-11-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Parsing data from text file to create scatter plot

    Hello. If I am posting to the wrong forum please let me know.
    Summary:
    I have a text file which is attached as "rawdata". It contains records of something (let's call it temperature) at different times on different days.
    My goal is to display a graph of temperature versus time so that I can visually analyze trends. I have hundreds of these files, all of different lengths. it is very important that I automate this process as much as possible.

    Detail:
    (Here I describe what I have done so far; if this is inefficient or unnecessary, feel free to tell me)
    I open Excel 2010, click File, Open, and select the file that I want to parse.
    It is a TXT file, so the Text Import Wizard comes up. For step one, I select Fixed Width.
    I select File Origin: MS-DOS (PC-8).
    On step 2 of the wizard, I create column break lines to place all dates in the far left column.
    The next column contains the first column of numbers before the first dash (-). The next column contains only the dash - I will later select "ignore this column" to eliminate them. The next column contains the time stamps.
    I continue adding column breaks in the wizard until all of the data are parsed into columns in the same manner.

    In step 3, I format the first column as "date (DMY)".
    The columns with the dashes I select "do not import".
    Everything else is "general".
    I click "finish", and the resultant workbook is attached, called "import".

    Now, as to what I want to do:
    I want to display the "temperatures" as a graph vs a date/time axis.
    The reason I find this difficult is because the temperatures and times are not in neat columns, but are in 4 columns that go in a left-to-right and top-to-bottom progression and are broken up every few lines.
    (I am interested only in numbers that are displayed immediately to the left of a time-stamp. Therefore, the "record #"s should be ignored. We can delete the rows that say "record #" if that helps and can be done automatically.)

    Can anyone help me do this?
    Thank you so much.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Parsing data from text file to create scatter plot

    Hello John and Welcome to the forum can you please update results expected?
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Registered User
    Join Date
    01-11-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Parsing data from text file to create scatter plot

    Certainly.
    Expected result:
    The goal is a scatter plot of temperature along an axis of time.

    The reason I find this difficult is that the times and temperatures are not in one nice neat pair of columns.

    Further clarification:
    In the example I uploaded, only the first 20 or so times have temperature values, the rest all show zeros - so for example
    120-01:30:29 means temperature was 120 degrees at 1:30 AM and 30 seconds. Many of the temperatures in this particular example are zeros, like
    0-13:29:30, which means zero degrees (or no data) at 1:29 PM and 30 seconds.

    Thanks.

  4. #4
    Registered User
    Join Date
    01-11-2014
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Parsing data from text file to create scatter plot

    Further study has told me I should be using VBA and a FOR loop to re-arrange the data.
    I don't know how to write such a code, but maybe if I put it in plain english, one of you can help.

    FOR (each time a date appears in Column A)
    --Do this on each row
    --until another date appears in Column A
    --Place the date in Column J
    --FOR (each cell that contains a DATE)
    ----Take the contents of that cell and place in the next available cell in column K
    ----Take the contents of the cell immediately to the left of the cell and place it in column L

    If I could translate this english into VBA, I would end up with three columns to the right of the initial data.
    The first column would contain the dates, the second column would contain the times, and the third column would contain the temperature number associated with each timestamp.
    From here it would be easy to graph temperature vs time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to create scatter plot with different data series?
    By mglwd40 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-07-2013, 08:17 PM
  2. Replies: 3
    Last Post: 09-20-2012, 11:35 AM
  3. Replies: 2
    Last Post: 07-26-2012, 09:27 AM
  4. How do I create a data break in a scatter plot?
    By khm7x in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 10-02-2008, 05:56 PM
  5. how do I create a 3 dimensional plot, for example XYZ scatter plo.
    By pleasehelp in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-28-2005, 07:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1