Importing large .csv files into Excel

[NatThoelecke]

I need to find a way to import very large .csv files into Excel to parse for data. The problem is the files will generate more than the maximum 65536 rows allowed per sheet in Excel. Is there a way to have VB parse the data from a .csv to allow for excess rows to be imported to a second (or third or fourth) sheet in a workbook without losing any rows of data?

[jvbd02]

Not a solution to your problem I'm afraid, but an alternative - import into access and then parse for data. The results of that parse can then be quickly and easily exported elsewhere...

Hope it helps.

[jvbd02]

Apologies - quite a cryptic answer if you've never done it before. So:

1 Create your import spec

- Right click on the Access Database window, select Import...
- Navigate to the file you want to import.
- Follow the wizard through to the last stage, but don't click on Finish.
- Click on Advanced - save the spec, Click OK on the Advanced screen, click Cancel on the import screen.

2 Code to import the csv file

private Sub cmd1_Click()
DoCmd.TransferText acImportDelim, "NameOfImportSpec", "TableNameToImportTo", "NameAndPathOfCSVFile", True ' or false if no field names
End Sub

3 Manipulate data accordingly into another table (or whatever - in a similar scenario I used another table).

4 Code to export the table of results to Excel

private Sub cmd2_Click()
DoCmd.RunSQL "SELECT * INTO [Excel 8.0;DATABASE=" & "NameAndPathOfDestinationXLSfile" & "].[data] FROM [TableWithManipulatedData];"
End Sub