Importing Text Files

[Belch]

Hi all,

I have some CSV files (large ones, some with one hundred thousand plus rows) and I'm currently importing them using the QueryTables.Add method (which the macro recorder gave me).
I've since seen you can use the Open method and read in a line at at time using Input, but this will probably take far too long with such a big CSV file. The problem I have with using QueryTables.Add is that it seems to leave Names and Connections all over the place.

Is there any other way to import a large text file quickly, or if I was to just remove the Name and Connection added each time I import a file, would that be sufficient?

Thanks,

[XLGibbs]

You won't be importing them into Excel if they are that large unless you are importing them directly into a pivot table...otherwise they just won't fit.

You may be better off importing them into access OR using them as an external source of data and pointing queries directly at the CSV file rather than importing them anywhere near excel.

[Belch]

I'm using Excel 2007 which can hold over a million rows on one sheet so the size isn't a problem.
However using them as an external source sounds interesting and more efficient - would doing it that way still be acceptable if I was checking every line of the massive CSV file, which I need to do.

[XLGibbs]

Ahh, yes, the 17 billion cell caability of 2007. How could I forget that! LOL

I think you would import the whole file, then do your checking. What are you checking each lne for? If it is a criteria issue, you can use external data an actually query the CSV file as if it were a database using MS QUery That would allow you to only import the rows meeting your search criteria...

[Belch]

I suppose querying it as if it was a database would be better as I'm basically checking for lines that appear in one CSV file and not in another.
I'll have a look into using it as an external source and play about with it, cheers for the heads up.