2

I am using SQL Server 2014 Management Studio to import a very large .CSV file into a table.

So far I have been trying to accomplish this by right-clicking on the database and selecting Import Data, then using the wizard to import from a flat file.

The problem I am having is that every time I try to import I either run into truncation errors (some of my fields exceed nvarchar(max)) or that there are missing fields on some rows.

How can I set the import to just ignore a failed row, instead of failing the entire operation. I've looked everywhere in the options but cannot find where to set this.

Improve this question
5
  • 1
    You have fields in a CSV that exceed 2GB in size? Really? I mean...really?
    – Mark Sinkinson
    Aug 11, 2014 at 14:35
  • do you know how to get the import wizard to skip failed rows instead of aborting?
    – jimmyjambles
    Aug 11, 2014 at 14:41
  • 1
    Not possible with the Import/Export Wizard. It's expecting a valid file. You'd need to use SSIS
    – Mark Sinkinson
    Aug 11, 2014 at 14:46
  • How are they 'missing fields'? If the fields exist but are empty, you can set the field to 'nullable'. If the file is actually missing fields in a line I.e. There are less commas in some lines, then your file is invalid.
    – Mark Sinkinson
    Aug 11, 2014 at 14:47
  • SSIS error when loading data from flat files will help you.
    – Kin Shah
    Aug 11, 2014 at 15:02

1 Answer 1

Reset to default
3

At the end of the import/export wizard you have the option to save your SSIS package (yes that's what the wizard uses behind the scenes). Save your package to a file then open it in SQL Server Data Tools. Go into the data flow and open the source. In the "Error Output" tab you will see an option to ignore failure (for truncation specifically or errors in general). Modify it for the columns you are worried about.

All that being said you are probably(99.999%) not seeing a truncation because the column is to big to fit in a varchar(max). What is probably happening is that SSIS "thinks" your column is shorter than it is. If you go into the connection manager for your CSV file you can modify the "width" of your input columns in the advanced tab.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.