importing large text files-Excel-- **URGENT**

[Guest_imported]

hi im new here but please help!

im trying to import a text file with around half a million lines, need to import 65000 lines at a time(due to excel's constraints) onto seperate sheets

i was going to do this by importing first 65000 lines, then importing text file starting at 130000, then importing file starting at 195000 etc on a +65000 increment loop.

however when importing text file the maximum value for "startrow" of text file is 32000 . so what do i do now?

help! code would be handy. the filename is ex0206.txt



a speedy response would be much apreciated!


thankyou!


Jake

 

[xlbo]

Why bother - stick it into Access, do what you need there and then export to excel
Not only will it be a pain in the butt to import it into excel but the file will MTL be too big to work with
Rgds
~Geoff~

 

[Guest_imported]

when i import to Access problems occur, and it takes ages to load, i also have very little experience working with Access. if you (or anyone) could provide me with instructions/code on how to do as xlbo said that'd be very much appreciated!


thanks


Jake

 

[xlbo]

If it takes ages to load in Access, it'll MTL take twice as long in excel - Access is built to handle more data than excel. Maybe if you tell us what problems occur when importing to Access, we can help

In access, you can create import specifications that you can run against any file - these are very handy as it will hold all the info you need for a standard import.

During the New>Table>Import wizard, click on the "Advanced" button - this will allow you to create an import spec. that should be step 1. Once this is done, you can create a macro that runs the import spec but prompts for a file name - step 2 - making it dynamic

I can't help you with the process in Access as I don't know what you need to do with the data but I would VERY strongly suggest that you look into other ways of doing this rather than excel - it just ain't built to cope with that kinda size of information Rgds
~Geoff~

 

[Guest_imported]

yeah i guess you're right.

i'll tell you exactly what im doing-see what u think:


im trying to split web log files into daily chunks, so i end up with one text file per day. each log file contains one months data. the first column/field will contain the date value in the format mm/dd/yyyy


i was trying to parse the data from the initial monthly sheet onto seperate sheets(one sheet per day-unless more sheets needed) but of course the monthly sheet is a little (!) big


what do you reckon?


Jake

 

[xlbo]

I reckon you should store it all in access and create a query that creates a new table (make table query) based on an input parameter to specify the date.
This will create a "day" table which you can either export to excel (very easy - functionality is all within access)
OR, once the data is in acess, you can use the data>get external data functionality within excel to query the access table and bring back the results for one day, based on a parameter in a cell or manually entered.
I would also suggest, that to get a monthly summary, you'd need to use Access anyway, you can create a "Crosstab" query based on your monthly file which will aggregate time spent by employee in much the same way as a pivot table does in excel

Hope this gets you going in the right direction
Rgds
~Geoff~

 

[Guest_imported]

thanks a lot geoff, i will try!

if any1 feels the need to give me the code etc to do this it would help a lot!


Jake

 

[xlbo]

You don't need code:
1: Import the file into Access using Table>New>Import
2: Whilst doing this, click on "Advanced" and create an Import spec
3: Create your make table query(ies) in the query panel
4: Go to macros and you can hook up all the above into 1 macro without even writing any code - NB these are Access MACROS which is not the same as VBA coding - they are standard procedures within tAccess that can be grouped together and called in order - very powerful functionality Rgds
~Geoff~

 

[Guest_imported]

not quite sure if im doing it right but each query to get one days data is taking a long toime to perform as its not very efficient--- its checking every record evern though the days are in order, and it doesnt stop checking after its finished the required day's record--if that makes sense

 

[xlbo]

OK - slightly different approach:
How about creating a CROSSTAB query
Users and sites down the side (Row Fields), dates along the top(Column Fields), (Sum Of) timings as values
Once it's in this format, it might fit on an excel sheet...
Rgds
~Geoff~

 

[Guest_imported]

nice idea, but i need it in the format its in so i can use it in some analysis software ive written...


but thanks though geoff