October 16, 2017 at 7:11 am
Hello,
I try to load a big CSV file (10M lines, 100 columns!) into a SQL Server 2014 DB .
In input, I have:
- a CSV file, generated by an external company with these parameters (impossible to make it use other settings)
· code page ISO-8859-15 (Latin 9)
· field delimiter: ;
· text delimiter: "
· DOS/Windows end of lines (CRLF)
- SQL "CREATE TABLE" command with right types and lengths for each column
When I try to populate this table with import wizard, I've many 0xc02020f4 errors:
"The column "xxx" cannot be processed because more than one code page (28605 and 1252) are specified for it. (SQL Server Import and Export Wizard)" :crazy:
How can I deal with this problem ?
Should I create a DB or table with special collation ?
The table has 100 columns, I'd like to get a solution at higher level than column... ;o)
Thanks by advance.
Regards,
Steph.
October 19, 2017 at 6:37 am
You may need to specify the correct collation for every individual column, if it differs from your database default collation. Not sure if there's a method to specify it for the entire table, but no matter what you do, if you have a collation incompatibility between the source and destination columns, your error will continue to occur.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 25, 2017 at 6:14 am
The "Import Flat File" feature is quite new, and I'm not sure if it resolves collation issues, but maybe worth trying:
http://www.sqlservercentral.com/blogs/discussionofsqlserver/2017/10/11/new-features-in-ssms-173/
October 25, 2017 at 7:01 am
polytropic2310 - Monday, October 16, 2017 7:11 AMHello,I try to load a big CSV file (10M lines, 100 columns!) into a SQL Server 2014 DB .
In input, I have:
- a CSV file, generated by an external company with these parameters (impossible to make it use other settings)
· code page ISO-8859-15 (Latin 9)
· field delimiter: ;
· text delimiter: "
· DOS/Windows end of lines (CRLF)- SQL "CREATE TABLE" command with right types and lengths for each column
When I try to populate this table with import wizard, I've many 0xc02020f4 errors:
"The column "xxx" cannot be processed because more than one code page (28605 and 1252) are specified for it. (SQL Server Import and Export Wizard)" :crazy:
How can I deal with this problem ?
Should I create a DB or table with special collation ?
The table has 100 columns, I'd like to get a solution at higher level than column... ;o)Thanks by advance.
Regards,
Steph.
You can sometimes get around that error when you use a Data Flow task and set the SQL Server destination property AlwaysUseDefaultCodePage to true. I don't think you can do that using the wizard though.
Sue
October 26, 2017 at 3:46 am
Hello,
Thanks for your answers but alas, I still have big troubles trying to import this huge CSV file.
I'm very new with SSIS.
I tried to make a very simple SSIS package with "AlwaysUseDefaultCodePage=true" but I 've errors when importing line number 3996, saying there is a data length problem or an code page problem.
Difficult to know which kind of problem it is because I can't view this enormous flat CSV file (10M lines, 100 columns!).
I've the right "CREATE TABLE ..." command with right type and size for each column of destination table.
I saw that there is in "Flat file connection manager" an "Advanced" tab to specify type and length of each column but is there another mean to specify types and lengths without manually modifying them for the hundred of columns ?
How ?
I've tested the [ Suggest types ] button but it is not reliable with a sample of data. To get reliable result, I should select the 10M lines, and it would be very sloooooow !
In short, what is the best practice to import a huge CSV file into a table, knowing type and length of its columns ?
Regards,
Steph.
(Sorry for my bad english :o( )
October 26, 2017 at 3:01 pm
polytropic2310 - Thursday, October 26, 2017 3:46 AMHello,Thanks for your answers but alas, I still have big troubles trying to import this huge CSV file.
I'm very new with SSIS.
I tried to make a very simple SSIS package with "AlwaysUseDefaultCodePage=true" but I 've errors when importing line number 3996, saying there is a data length problem or an code page problem.
Difficult to know which kind of problem it is because I can't view this enormous flat CSV file (10M lines, 100 columns!).I've the right "CREATE TABLE ..." command with right type and size for each column of destination table.
I saw that there is in "Flat file connection manager" an "Advanced" tab to specify type and length of each column but is there another mean to specify types and lengths without manually modifying them for the hundred of columns ?
How ?
I've tested the [ Suggest types ] button but it is not reliable with a sample of data. To get reliable result, I should select the 10M lines, and it would be very sloooooow !In short, what is the best practice to import a huge CSV file into a table, knowing type and length of its columns ?
Regards,
Steph.
(Sorry for my bad english :o( )
The problem may simply be that what you think is good data is not actually good, and perhaps does not conform to the datatype for it's column. The only way to solve that is to set ALL the columns as varchar with a length long enough to cover all the possibilities. You can then dump that into a staging table with the exact same set of all varchar columns, and then validate each column in a transformation, but in a way that detects errors. It's a lot of work, but there's no good alternative for a file of that size.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply