Excel Module to extract data from a .txt file (mouse, sounds, formulas)

[Bertie1234]

Hi,

I have become very stuck trying to solve what for you guys may be a simple problem.
I have a .txt file that contains data in the format below. Each line contains Date Time,Number

Time,Cal
2008/04/03 11:18:37.189,6752.71782
2008/04/03 11:18:41.189,6671.19603
2008/04/03 11:18:45.189,6672.40847
2008/04/04 11:18:49.189,6668.27078
2008/04/04 11:18:53.189,6689.9646
2008/04/04 11:18:58.189,6689.9645

There are 1000's of entries for each day, and each .txt file can cover a period of weeks...
I need to create a form in Excel which can allow me to navigate to the .txt file I need to read, and then for each day give me the Min, Max & Average for the number, and then display the data in Excel in columns:
Date Min Max Average

Any help would really be appreciated and help me some points at work at the same time..

Thanks in advance.
Bertie...

[Bo]

The "Text to Columns" feature in Excel will help you get all of that data into individual cells in an Excel Workbook. You'll find it under the "Data" menu.

Excel will only work well for sorting and searching if you import all of the data. From your description of the number of entries, it sounds like you don't want to go through the manual process of importing all of the data into spreadsheets. So you may need to use something more robust than Excel, like a database application, to ingest and work with the data.

[bibit612]

Originally Posted by Bowie

The "Text to Columns" feature in Excel will help you get all of that data into individual cells in an Excel Workbook. You'll find it under the "Data" menu.

Excel will only work well for sorting and searching if you import all of the data. From your description of the number of entries, it sounds like you don't want to go through the manual process of importing all of the data into spreadsheets. So you may need to use something more robust than Excel, like a database application, to ingest and work with the data.

This is correct, however, it could only contain about 65,000 rows/records. If under this, it would work. If the text file has the same number of columns each time, pre-populate a template with the formulas for Min, Max and Average starting at the column after all the columns would be parsed, so that when you copy-paste the text in column A, your formulas would automatically calculate each function for you.

[Tek_Freek]

I copied the data given to a text file and imported it in two ways. As a fixed field and using delimiters. The result was a mix.

The fixed field worked well except it kept the period between the time and numbers field.

Importing as delimited using the space for date field time field separator and the period as a second delimiter separated the number into two cells because it saw the decimal as a separator.

Text to Columns had the same type of problem.

If you had a way to either strip the period out between the time and number fields in the text file, or simply deleting the time field if it's not required before importing it would be a simpler solution.

Added: You should be able to do either in Word. Place the cursor in front of the time, press Alt and drag the mouse down and to the right of the time to select a block, then delete the block. Or set up a macro to mark and delete the time and period following it. This will give the date and number delimited by the space. There is a way to run this in a loop until end of file is found. If you opt to try it this way I'll hunt down the code. Heck, I'll write the macro and send it to you. Most of it is done with a recording anyway...

Interesting

[Mach50]

Sounds like you may need to learn Visual Basic for Applications and write some code by hand, you could do everything you are asking for programatically.

[Tek_Freek]

Originally Posted by Mach50

Sounds like you may need to learn Visual Basic for Applications and write some code by hand, you could do everything you are asking for programatically.

The best advice so far...

[JBrown]

don't need anything as complicated as Vis basic. A simple basic program...remember it was free long ago will do it very easily or a simple non graphic C thing if more comfortable with that.

something like

get file name
open file
DO till EOF
sort/ etc based upon characters and spaces, stick commas in there so
you 've now got a CSV file
same file under new name
close both files
done


about a dozen lines of code I would guess

[harry chickpea]

This trivial qbasic or QB45 program changes the non-standard
2008/04/03 11:18:37.189,6752.71782
into a comma separated variable file (CSV) in the twelve lines of code requested.

OPEN Name.TXT FOR INPUT AS #1
OPEN OUTPUTFILE.TXT FOR OUTPUT AS #2
DO UNTIL EOF
LINE INPUT TEMP$
THISDATE$ = LEFT$(TEMP$, 10)
THISTIME$ = MID$(TEMP$, 12, 8)
THISNUMBERFRONT$ = MID$(TEMP$,20, 3)
THISNUMBERBACK$ = MID$(TEMP$,25, 30)
THISNUMBER$ = THISNUMBERFRONT$ + THISNUMBERBACK$
PRINT #2, THISDATE$;",";THISTIME$;",";THISNUMBER$
LOOP
CLOSE 1,2

[therewego]

Bertie1234:
Are the periods included to be space delimiters or are they actually part of the data? Given the small amount of data, I took what you had and copy and pasted it into a Word document. I did a Find and replace, to find . and replace with a <press your spacebar>; this is what I got.
2008/04/03 11:18:37 189,6752 71782
2008/04/03 11:18:41 189,6671 19603
2008/04/03 11:18:45 189,6672 40847
2008/04/04 11:18:49 189,6668 27078
2008/04/04 11:18:53 189,6689 9646
2008/04/04 11:18:58 189,6689 9645
I then copy and pasted that back into a text file and imported that data into Excel where it asked me if I wanted to use the spaces as delimiters, yes. then it showed me how the columns would look. I got this:
4/3/2008 11:18:37 1,896,752 71782
4/3/2008 11:18:41 1,896,671 19603
4/3/2008 11:18:45 1,896,672 40847
4/4/2008 11:18:49 1,896,668 27078
4/4/2008 11:18:53 1,896,689 9646
4/4/2008 11:18:58 1,896,689 9645
From there I would imagine you could do all kind of sorts. Hope this helps.

[JBrown]

Bravo Harry , Bravo !!

I just guessed at the dozen lines of code but there you go !!

...you don't need a bloated Vbasic/ Visual_C thing etc to do some simple string manipulation etc


couldn't point you due to this stupid rules thing here but consider it done