Import Flat File via SSMS to SQL Server fails

import ssms

23,875

Solution 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.

Solution 2

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

Solution 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!!!

Solution 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.

View more solutions

23,875

Author by

Andrew

Updated on July 05, 2022

Comments

Andrew almost 2 years

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?
Daniel Jackson almost 6 years

Didn't work for me. I had only one blank line at the end of mine.
Jeremiah Cooper over 5 years

I removed all blank lines at the tail and it worked! Thanks!!
DataWriter over 5 years

@DanielJackson I had the same problem. I was working with an Excel file converted to text. The worksheet had extra "blank" columns in it. When I removed them, the import succeeded.
user12861 almost 5 years

I had this happen in a file with improperly escaped double quotes.
Andrew almost 5 years

@user12861 I've incorporated your feedback into the answer
Andrew almost 5 years

@DataWriter I've incorporated your feedback into the answer
trpt4him almost 4 years

Wow, thanks, that was it. What a sad error to show for something like that.
Masaba James Moses over 3 years

Thanks a lot, This actually was the one causing the problem. After closing the target file, it worked like charm.