Import Flat File via SSMS to SQL Server fails

Question

When importing a seemingly valid flat file (csv, text etc) into a SQL Server database using the SSMS Import Flat File option, the following error appears:

Microsoft SQL Server Management Studio

Error inserting data into table. (Microsoft.SqlServer.Import.Wizard)

Error inserting data into table. (Microsoft.SqlServer.Prose.Import)

Object reference not set to an instance of an object. (Microsoft.SqlServer.Prose.Import)

The target table may contain rows that imported just fine. The first row that is not imported appears to have no formatting errors.

What's going wrong?

Predella

Answer 1

Check the following:

• that there are no blank lines at the end of the file (leaving the last line's line terminator intact) - this seems to be the most common issue
• there are no unexpected blank columns
• there are no badly escaped quotes

It looks like the import process loads lines in chunks. This means that the lines following the last successfully loaded chunk may appear to have no errors. You need to look at subsequent lines, that are part of the failing chunk, to find the offending line(s).

This cost me hours of hair pulling while dealing with large files. Hopefully this saves someone some time.

Predella

Answer 2

If the file you're importing is already open, SSMS will throw this error. Close the file and try again.

Gnosticism

Answer 3

Make sure when you are creating your flat-file IF you have text (varchar) value in any of your columns, DO NOT select your file to be comma "," delimited. Instead, select vertical line "|" or something that you are SURE it can't be in those values. the comma is supper common to have in nvarchar filed.

I have this issue and none of the recommendations from other answers helped me! I hope this saves someone some times and it took me hours to figure it out!!!

Hinch

Answer 4

None of these other ones worked for me, however this did:

When you import a flat file, SSMS gives you a brief summary of the data types within each column. Whenever you see a nvarchar that's in an int or double column, change it to int or double. And change all nvarchars to nvarchar(max). This worked for me.

Adai

Answer 5

I've been working with csv data for a long time. I encountered the similar problems when I first started this job, however as a novice, I couldn't obtain a precise fault from the exceptions.

Here are a few things you should look at before importing anything.

1. Your csv file must not be opened in any software, such as Excel.
2. Your csv file cells should not include comma or quotation symbols.
3. There are no unnecessary blanks at the end of your data.
4. There is no usage of a reserved term as data. In Excel, open yourfile and save it as a new file.

Glottal

Answer 6

After considering all the suggestions, if anyone is still having issues, check the length of the DataType for your columns. It took hours for me to figure this out but increasing the nvarchar length from (50) to (100) worked for me.

Ciera

Answer 7

One thing that worked for me : You can change the error range to 1 in "Modify colums"

You get an error message with the specific line that's problematic in your file instead of "ran out of memory"

Quantic

Answer 8

I fixed these errors by playing around with the data type. For instance, change my tinyint to smallint, smallint to int, and increased my nvarchar() to reasonable values, else I set it to nvarchar(MAX). Since most of the real-life data do have missing values, I checked allowed missing values in all columns. Everything then worked with a warning message.

Actuality

Answer 9

Make sure the account running the SQL service has rights to the file and folder. Easy solution is to drop the csv in the folder of the SQL Server's default backup location.

Posology

Answer 10

My situation is one column has very long text data. Then, I chose the VARCHAR(MAX) instead of VARCHAR(50). It allows the system to store longer text data.

Supportable