Results 1 to 12 of 12
Thread: CSV file (2003)

20041118, 17:35 #1
 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

20041118, 18:32 #2
 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 (Mid4AM), 1 (48AM), 2 (8AMNoon), 3 (Noon4PM), 4 (48PM) and 5 (8PMMid) 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

20041118, 18:41 #3
 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

20041118, 19:33 #4
 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.

20041118, 19:45 #5
 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

20041118, 20:30 #6
 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

20041118, 20:54 #7
 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 datetime 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

20041118, 23:28 #8
 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

20041119, 12:48 #9
 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

20041119, 14:45 #10
 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

20041119, 19:52 #11
 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

20041120, 07:51 #12
 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 05 for each time frame
1+INT(MOD(A2,1)*6)
Converts the 05 to 16
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 (124 = 1, 48=2, etc)
Steve