Error importing .csv file

[rok61415]

Hello,

   I am trying to import a .csv file to access that is ~800 MB and I am getting an error stating the following:

"cannot open database ". it may not be a database that your application recognizes, or the file may be corrupt."

This is followed immediately by an error message stating the file was not imported.

The part I am not understanding is that, while I am working through the import wizard, I can see all the data. The data makes sense and it is delimited appropriately. That makes me think that the error message I am getting is incorrect.

I have tried changing the delimiting character and converting the csv to a txt file as those had worked for me in the past. Does anyone have a work around for this?

Thanks

[#2]

@rok61415 

Details, although pesky, are truly important in trying to understand what we can't see on your computer.

At what point in the import wizard does this error message get raised?

Have you inspected the entire csv file to ensure it's completely consistent in layout and contents?

How many columns does the csv file have?

How many characters in a typical row in the csv file?

[#3]

@George_Hepworth 

Details are important, im just not sure what details are needed. Please see answers below:

At what point in the import wizard does this error message get raised?

     - This error message appears when I click finish at the end of the import text wizard

Have you inspected the entire csv file to ensure it's completely consistent in layout and contents?

     - There are some 13,000,000 rows of data so I have not checked the entirety of the file. But in my random spot checks I cannot find any errors.

How many columns does the csv file have?

     - There are 18 columns

How many characters in a typical row in the csv file?

     - Looks to average between 50 and 80 characters

Thanks for the help!

[#4]

@rok61415 

Thanks. It errors when you have gone through the wizard and attempt to finish it and start the import.

13,000,000 rows? In a csv file.... I would have to do some research, but that sounds like it might be a factor. I am also suspicious about that number, because the maximum number of rows in an Excel worksheet would be 1,048,576. That means this csv file would be at least 12 times too large to have been saved from an Excel file.  

Where is this file generated originally, by the way?

80 characters divided by 18 columns is about 4 to 5 characters per field. not a lot on that dimension.

In fact, thinking about it a bit more, I'm wondering if Access is really the right tool for a 13,000,000 row CSV file. Perhaps SQL Server, or another server database would be more appropriate. 

[#5]

I'm finding references to imports into SQL Server failing at around that number of rows (13,000,000) from a csv. It makes me wonder if there isn't a a way to batch that data into smaller, more manageable chunks.

[#6]

@rok61415 

Two other thoughts about ways to manage this. 

1) Have you tried simply linking to the csv without trying to import it? Can you do that and read the data?

2) Other methods for importing csv files might be more effective, albeit probably not very efficient with 13,000,000 rows. Look into approaches using the File Scripting Object.

[#7]

On the File menu, click Import. In the Import dialog box, click the option for the type of file that you want to import, and then click Import. In the Choose a File dialog box, locate and click the CSV, HTML, or text file that you want to use as an external data range, and then click Get Data.

Regards,
Will