Results 1 to 12 of 12

Thread: CSV file (2003)

  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CSV file (2003)

    I have a CSV file with dates, times, and integers. They are as follows:
    9/15/2004 13:12,20.1
    9/15/2004 13:16,25.4
    9/15/2004 13:18,68.6
    9/15/2004 13:24,52.3
    and so on down the list

    I need to make a script file in another spread sheet to find the data in the CSV file, sort it into 6 different four hour time slots starting with Midnight - 4a.m., and then average the integers in the particular time slots. I need to return the averages in particular cells in the spreadsheet.

    If anyone can help me with this I would appreciate it.

    Thanks,
    Nick

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CSV file (2003)

    We would need much more details if you want codiing.

    Manually you could just import the CVS and then parse it into columns. The date/time should be together in one column (I will assume col A).
    You could create a "time slot" column to contain 0 (Mid-4AM), 1 (4-8AM), 2 (8AM-Noon), 3 (Noon-4PM), 4 (4-8PM) and 5 (8PM-Mid) using the formula:
    =INT(MOD(A2,1)*6)
    and copy it down.

    To get the summaries of the time slots, you could use a pivot table to extract summary info based on the timeslot column.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV file (2003)

    I am not sure what details you are needing, because I haven't had the chance to work with script files that import CSV data.
    I am not sure where to even start with this script.

    Thanks,
    Nick

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: CSV file (2003)

    I attach a zip file containing an Excel file and a sample csv file.

    The Excel file contains a query which imports data from the csv file. It has a formula to assign a timeband to each time value. The query settings allow this formula to be filled down as data is imported. The averages can be obtained using the appropriate D Function (DAVERAGE).

    There is no VBA or scripting required, unless you need to incorporate a sort.

    Unzip the files to an appropriate folder, open the excel file , select A2 and goto Data, Refresh Data. You should be prompted for the data file, and should select the csv file.

    If this approach suits and you require some further help, post back.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CSV file (2003)

    The details of what you want to do, where you want to put the imported text file, what you want at the end, etc
    What do you start with [is the example lines all there is, 2 columns (date/time and a number) or is there more] and how do you want it to look afterwards?

    Open an excel file and use the macro recorder (tools - macro - record new macro) and manually Open the text file in excel and use the wizard.

    The macro will record the steps and you can view the code.

    You can also look in VB help under the "OpenText" Method of the workbook object.

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Jun 2004
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV file (2003)

    what if the date and time are in the same cell

    ex: 11/15/2004 12:45

    thanks,
    nick

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: CSV file (2003)

    Assuming that the date-time value is interpreted correctly by excel, the formula would remain the same, exceptt point to the column with the date and time.

    If the Date & Time are in column A, then the formula would read =INT(HOUR(A2)/4)+1.

    Andrew C

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CSV file (2003)

    The MOD part of the equation removed the "day part" and only looks at the time part of the value.

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Jun 2004
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV file (2003)

    I will try to explain better and give an example. Attached is a zip file which contains a spreadsheet (AverageEntry.xls) and a CSV file (Data.csv). I need to come up with a script file that will find the CSV file on the hardrive, sort the data into one of the 6, four hour time slots (they are on the AverageEntry spreadsheet), and then average each of the 6 time slots into one single value and place in the appropriate place in the AverageEntry spreadsheet. Any help is appreciated.

    Thanks,
    Nick

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CSV file (2003)

    How about this?
    I imported the csv
    I added a column as I suggested (I modified it to use the number as lookup in a hidden row in pivot sheet
    I created a pivot table pased on the date (row) and the timeslot(col) and avg of the data
    I grouped the date to the DAY in the pivot table

    Steve

  11. #11
    2 Star Lounger
    Join Date
    Jun 2004
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CSV file (2003)

    Thanks for the help.
    I have another question. I am having trouble figuring out what this code is doing. Can you expalin it to me?
    =1+INT(MOD(A2,1)*6) & ") " & INDEX(Sheet3!$B$1:$G$1,1+INT(MOD(A2,1)*6))
    Thanks again,
    Nick

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CSV file (2003)

    MOD(A2,1)
    takes the decimal part of the date/time (this is the "time", the "date part" is the integer quantity since excel stores it in days)

    Int(MOD(A2,1)*6)
    Gets the "4 hr" (=24/6) intervals starting at midnight (=0). It results in numbers from 0-5 for each time frame

    1+INT(MOD(A2,1)*6)
    Converts the 0-5 to 1-6
    1+INT(MOD(A2,1)*6) & ") "
    Adds the ")" at the end so you get something like #) [I did this so the pivot would sort correctly]

    INDEX(Sheet3!$B$1:$G$1,1+INT(MOD(A2,1)*6))
    Reads the description of the time ranges in the header (12-4 = 1, 4-8=2, etc)

    Steve

Posting Permissions

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