CSV file into SQL Server

Hello GUyz,

When I do a Bulk insert to import a CSV file into the SQL Server i get the following error.

Msg 511, Level 16, State 1, Line 1

Cannot create a row of size 20809 which is greater than the allowable maximum of 8060.

The statement has been terminated.

Is there a way to get over it. Thank you.

--SRI.

Did you specify both a Row Delimiter and a Column Delimiter?

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Yes. I did it. But doesn't work.|||

Sri. wrote:

Yes. I did it. But doesn't work.

You're going to have to provide plenty more details than, "doesn't work."

Can you provide sample data? How long is each row, typically? What have you tried for the various settings in the source config?|||

It has like 982 columns and 2545 rows.

Some of the data in the rows is really big . It has like more than 200 characters.

SO I went to the advanced properties in the data source and changed the type as Unicode string and changed the OutputColumnwidth to 300 and tried to execute it but it says "Text was truncated". So I tried with bulkInsert but it gave me the error I showed above.

|||If it's truly a comma separated file, then you shouldn't have any issues, unless the data types defined in the source connection aren't long enough. You need to go through the advance properties and select the columns to verify that the lengths are correct.|||

Are you sure your file is properly formatted? All delimiters in each row need to be present in order for the file to be properly parsed.

Try to look for rows that cause problems, either by dividing the file in halves or redirecting rows with errors/truncations.

HTH.

|||

What are the data types of the columns in the SQL Server table? If you define them all as VARCHAR(8000) or NVARCHAR(4000) for example, it is possible to have a valid table definition, but have scenarios where the data being inserted to create a new row (or supplied to update an existing row) exceed the maximum table size.

A single "standard" row in SQL Server cannot exceed the maximum row size. You can get around that by defining your columns as TEXT or NTEXT instead of VARCHAR or NVARCHAR, or by defining them as VARCHAR(MAX) or NVARCHAR(MAX) if you are using SQL Server 2005. These BLOB data types are stored outside the row itself, so you can get around the row size limit.