Text To Access To Excel

[JimBeam]

I have a file that's in .txt format that I need to have in Excel.

Unfortunately there are 140,000+ lines of data so it won't fit on one Excel tab ( obvioulsy using the older version ).

I thought that if I imported the .txt file into Access and then tried to export it to Excel that it'd create secondary tabs for the line items that wouldn't fit on the 1st tab.

Any ideas on how to do that ?

Or if there's a way to get it from the .txt file right into Excel on multiple tabs w/out copying and pasting like a caveman ?

I use TOAD to export data sometimes and I know it automatically breaks out the extra lines but not sure hw to get Access/Excel to do it.

Any help would be appreciated.

Thanks.

[MisterSmith]

If I am trying to transfer text to Excel and it isn't already in cells, I usually paste everything into Word first and convert it into a table. Then it is a lot easier to paste/transfer to Excel.

However, it might take a little extra work if there is no clear delineation between what you want in the cells.

These are easy to create examples:

Name Date Location Time Space - just use the space as the break point

Name;Date;Location;Time;Space - same here, but use ;

This is tougher:

First Name Last Name Address 1 Address 2 Wife's Maiden Name - there is no clear or easy break for the converter to pick up on so you will have to go in and create one.

[JimBeam]

I'm trying to avoid copying and pasting altogether.

I don't see how it's not an option in Access to export more than 65,000+ rows.

[IMSlacker]

If you're using the text import wizard, there's a "Start import at row:" option. after you import the first 65,536 rows, you can go to another tab open the same file again, and tell the import wizard to start import at row # 65,537. Then do the same thing again, starting with row # 131,073 and so on.

[JimBeam]

OK I got it to take the first 65,536 and put it on tab 1 but when I ask the Wizard to start at line 65,537 of the .txt file it says " Interger Is Not Valid ".

[IMSlacker]

Crap, you're right. That will only work up to row 32,767.

[JimBeam]

I ended up running 3 queries on it in Access.

I assigned a primary key of 1 to 143144 and then made a query to select 1 to 65000, 65001 to 130000 and 130001 to 143144.

I then exported those each to Excel and pasted them in seperate tabs.

All the stuff I found on Google suggested running VB code but I wasn't putting that much time into it.

I would've tought the export from Access would've allowed for the secondary tabs to be created automatically.

Like I said I use TOAD to run SQL queries for data and it breaks it out ever 65,000 items.

Thanks for the time you guys put into it.