U.S. CitiesCity-Data Forum Index
Go Back   City-Data Forum > General Forums > Science and Technology > Computers
 [Register]
Please register to participate in our discussions with 1.5 million other members - it's free and quick! Some forums can only be seen by registered members. After you create your account, you'll be able to customize options and access all our 15,000 new posts/day with fewer ads.
Jump to a detailed profile or search
site with Google Custom Search

Search Forums  (Advanced)
Business Search - 14 Million verified businesses
Search for:  near: 
Reply Start New Thread
 
Old 06-03-2008, 12:32 PM
 
2 posts, read 11,961 times
Reputation: 10
Default Excel Module to extract data from a .txt file

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...
Reply With Quote Quick reply to this message

 
Old 06-03-2008, 01:15 PM
Bo Bo won $500 in our forum's Most Engaging Poster Contest - Tenth Edition (Apr-May 2014). 

Over $84,000 in prizes has already been given out to active posters on our forum and additional contests are planned
 
Location: San Antonio
14,411 posts, read 20,489,693 times
Reputation: 9551
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.
Reply With Quote Quick reply to this message
 
Old 06-03-2008, 01:24 PM
 
Location: Moon Over Palmettos
5,971 posts, read 11,940,724 times
Reputation: 4757
Quote:
Originally Posted by Bowie View Post
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.
Reply With Quote Quick reply to this message
 
Old 06-03-2008, 02:58 PM
 
18,949 posts, read 21,365,619 times
Reputation: 26511
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

Last edited by Tek_Freek; 06-03-2008 at 03:14 PM..
Reply With Quote Quick reply to this message
 
Old 06-04-2008, 12:13 AM
 
Location: Denver
7,995 posts, read 9,652,493 times
Reputation: 3835
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.
Reply With Quote Quick reply to this message
 
Old 06-04-2008, 07:49 AM
 
18,949 posts, read 21,365,619 times
Reputation: 26511
Quote:
Originally Posted by Mach50 View Post
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...
Reply With Quote Quick reply to this message
 
Old 06-04-2008, 08:33 AM
 
Location: Hopewell New Jersey
1,374 posts, read 4,953,206 times
Reputation: 893
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

Reply With Quote Quick reply to this message
 
Old 06-04-2008, 04:11 PM
 
Location: Somewhere in northern Alabama
11,397 posts, read 25,413,162 times
Reputation: 13825
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
Reply With Quote Quick reply to this message
 
Old 06-04-2008, 05:52 PM
 
5,244 posts, read 3,014,706 times
Reputation: 1797
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.
Reply With Quote Quick reply to this message
 
Old 06-04-2008, 07:20 PM
 
Location: Hopewell New Jersey
1,374 posts, read 4,953,206 times
Reputation: 893
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
Reply With Quote Quick reply to this message
Please register to post and access all features of our very popular forum. It is free and quick. Over $68,000 in prizes has already been given out to active posters on our forum. Additional giveaways are planned.

Detailed information about all U.S. cities, counties, and zip codes on our site: City-data.com.


Reply
Please update this thread with any new information or opinions. This open thread is still read by thousands of people, so we encourage all additional points of view.

Quick Reply
Message:


Over $84,000 in prizes was already given out to active posters on our forum and additional giveaways are planned!

Go Back   City-Data Forum > General Forums > Science and Technology > Computers

All times are GMT -6.

2005-2014, Advameg, Inc.

City-Data.com - Archive 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25 - Top