Menu
I'm trying to insert a large CSV file (several gigs) into SQL Server, but once I go through the Import Wizard and finally try to import the file I get the following error report:
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column ""Title"" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task 1: The "Source - Train_csv.Outputs[Flat File Source Output].Columns["Title"]" failed because truncation occurred, and the truncation row disposition on "Source - Train_csv.Outputs[Flat File Source Output].Columns["Title"]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Train.csv" on data row 2.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - Train_csv returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
I created the table to insert the file into first, and I set each column to hold varchar(MAX), so I don't understand how I can still have this truncation issue. What am I doing wrong?
811
The most common CSV import errors include: The file size is too large - The CSV import tool of the program you're using might have a file size requirement. To reduce the file size, you can delete unnecessary data values, columns, and rows.
varchar [ ( n | max ) ] Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000 or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB).
The best, quickest and easiest way to resolve the comma in data issue is to use Excel to save a comma separated file after having set Windows' list separator setting to something other than a comma (such as a pipe). This will then generate a pipe (or whatever) separated file for you that you can then import.
In SQL Server Import and Export Wizard you can adjust the source data types in the Advanced tab (these become the data types of the output if creating a new table, but otherwise are just used for handling the source data).
The data types are annoyingly different than those in MS SQL, instead of VARCHAR(255) it's DT_STR and the output column width can be set to 255. For VARCHAR(MAX) it's DT_TEXT.
So, on the Data Source selection, in the Advanced tab, change the data type of any offending columns from DT_STR to DT_TEXT (You can select multiple columns and change them all at once).
191
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
