Import of HUGE Text Files
Posted by JAF on August 10, 2000 3:47 AM
I have a number of text files that have been imported into Access and I need to get the data from Access into Excel, but th eproblem I have is that some of the files/Access tables have over 65,536 records - one has over 1,000,000!
What I need is some way of putting rows 1 to 65,000 onto Sheet1, rows 65,001 to 130,000 onto Sheet2 and so on until all data has been copied across.
Possible?
JAF
Posted by Michael Liu on August 14, 0100 8:06 AM
What do you plan to do with this data?
Do you really need all those rows of information?
If not, I would suggest building simple queries to
get only the rows that you would need, and hopefully
that would be less than 65536 rows.
Otherwise, you could build a bunch of queries that pull
out 65,000 rows and save each one to an excel file and then
recombine them into one workbook.
Posted by Thomas Venn on August 16, 0100 10:28 AM
If you really want to do this in Excel, you are in a world of hurt. I have done this before, and Excel has a very diffucult time handling all of this data, especially if you try to put it all in one workbook. But the way I did it was to first save it as a external TEXT file, then open it with MSword, then decide which page of the data in MSword would end around row 60,000. I have found with most of my data, page 1,000 is a good ending point. anyway, I would go to page 1,000, then CUT and paste to Excel. then repeat the process again. very manual, time consuming, and frustrating.
Second option is to do it all this in Access. You might want to do a query so that each number of your eight digits is in a single (field), then concatenate the fields to 3 fields so that it is of 4 digit(year) 2 digit(month) and 2 digit(date). then finally do another query to put it all together in MMDDYYYY format so you can validate.
cheers,
Thomas
You may be able to write something similar in Access VB.
Posted by JAF on August 14, 0100 11:34 AM
Unfortunately, I need to check every row of data for possible invalid dates. The text file is an export of mainframe data that contains the date as an 8 digit number (not Julian) - for example 14th August 2000 would be 20000814. I have a macro in Excel that transforms this 8 digit number into a real date (if possible) and reports any errors so that I can track down the incorrect data on the mainframe and get it fixed there.
I have been running queries to extract 65000 rows of data at a time, but it's a bit time consuming and I was wanting to know of there was any way of automating that extraction process.
JAF What do you plan to do with this data?
Posted by Michael Liu on August 14, 0100 2:19 PM
You may be able to write something similar in Access VB.
I am unfamiliar with this dialect of VB, but maybe you can
find a site with people more familiar with Access.
mraccess.com? =^)
Actually, that is a real site and has nothing to do with Access. Unfortunately, I need to check every row of data for possible invalid dates. The text file is an export of mainframe data that contains the date as an 8 digit number (not Julian) - for example 14th August 2000 would be 20000814. I have a macro in Excel that transforms this 8 digit number into a real date (if possible) and reports any errors so that I can track down the incorrect data on the mainframe and get it fixed there. I have been running queries to extract 65000 rows of data at a time, but it's a bit time consuming and I was wanting to know of there was any way of automating that extraction process. : What do you plan to do with this data?
