CSV file (2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

what if the date and time are in the same cell

ex: 11/15/2004 12:45

thanks,
nick

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 #)

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

Reply | Quote

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 #)

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Steve

Reply | Quote

what if the date and time are in the same cell

ex: 11/15/2004 12:45

thanks,
nick

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote