Not able to load csv files with multi byte characters using dbload

Hi, I am trying to migrate data from SQL Server to Informix database. Using the following bcp command I have exported the data into csv file. bcp "SELECT skillID,profileID,skillName,active,dateInactive from db_cra..Skill" queryout C:\\Skill.csv -w -S 10.76.253.124\\CRSSQL -t"," - T When I am trying to import the data back into IDS using dbload , I am getting the following error. DBLOAD Load Utility INFORMIX-SQL Version 10.00.UC7X1 Copyright IBM Corporation 1996, 2006 All rights reserved Software Serial Number AAA#B000000 In INSERT statement number 1 of raw data file /root/Test/Skill.csv. Row number 1 is bad. , Unexpected error while reading field #1. I have some characters like Chinese,Japanese,German present in the SQL server table, which is trying to migrate. I also tried dbload with the csv files generated using bcp command with -c , -N option etc. None of the options work with International characters. Locale settings where Informix is installed : CLIENT_LOCALE : en_US.utf8 DB_LOCALE : en_US.utf8 Can somebody please help me out?

Jay wrote: > Hi, > I am trying to migrate data from SQL Server to Informix database. > > Using the following bcp command I have exported the data into csv > file. > > bcp "SELECT skillID,profileID,skillName,active,dateInactive from > db_cra..Skill" queryout C:\\Skill.csv -w -S 10.76.253.124\\CRSSQL -t"," - > T > > When I am trying to import the data back into IDS using dbload , I am > getting the following error. > > DBLOAD Load Utility INFORMIX-SQL Version 10.00.UC7X1 > Copyright IBM Corporation 1996, 2006 All rights reserved > Software Serial Number AAA#B000000 > > In INSERT statement number 1 of raw data file /root/Test/Skill.csv. > Row number 1 is bad. > , > > Unexpected error while reading field #1. > > I have some characters like Chinese,Japanese,German present in the SQL > server table, which is trying to migrate. I also tried dbload with the > csv files generated using bcp command with -c , -N option etc. None of > the options work with International characters. > > Locale settings where Informix is installed : > > CLIENT_LOCALE : en_US.utf8 > DB_LOCALE : en_US.utf8 > > Can somebody please help me out? Can you provide an example line and if possible a table schema? Obviously we don't need to see your data, only an example of it that can reproduce the issue. Regards.

On Jun 10, 2:39 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > Jay wrote: > > Hi, > > I am trying to migrate data from SQL Server toInformixdatabase. > > >Usingthe following bcp command I have exported the data intocsv > > file. > > > bcp "SELECT skillID,profileID,skillName,active,dateInactive from > > db_cra..Skill" queryout C:\\Skill.csv-w -S 10.76.253.124\\CRSSQL -t"," - > > T > > > When I am trying to import the data back into IDSusingdbload, I am > > getting the following error. > > >DBLOADLoad Utility INFORMIX-SQL Version 10.00.UC7X1 > > Copyright IBM Corporation 1996, 2006 All rights reserved > > Software Serial Number AAA#B000000 > > > In INSERT statement number 1 of raw data file /root/Test/Skill.csv. > > Row number 1 is bad. > > , > > > Unexpected error while reading field #1. > > > I have some characters like Chinese,Japanese,German present in the SQL > > server table, which is trying to migrate. I also trieddbloadwith the > >csvfiles generatedusingbcp command with -c , -N option etc. None of > > the options work with International characters. > > > Locale settings whereInformixis installed : > > > CLIENT_LOCALE : en_US.utf8 > > DB_LOCALE : en_US.utf8 > > > Can somebody please help me out? > > Can you provide an example line and if possible a table schema? > Obviously we don't need to see your data, only an example of it that can > reproduce the issue. > > Regards. Hi, My table schema is : CREATE TABLE Skill ( skillID int NOT NULL , profileID int NOT NULL , skillName nvarchar (50) NOT NULL , active smallint NOT NULL , dateInactive datetime year to fraction(3) ); ALTER TABLE Skill ADD CONSTRAINT PRIMARY KEY ( skillID, profileID ) CONSTRAINT PK_Skill; csv file I generated using the following bcp command from SQL server. bcp "SELECT skillID,profileID,skillName,active,dateInactive from db_cra..Skill" queryout C:\\Skill.csv -w -S 10.76.253.124\\CRSSQL -t"," - T Content of the csv file: 1,1,sk1,1, 2,1,sk2,1, 3,1,öü,1, Not able to import this file using dbload command. Thanks a lot! Regards Jaya raj

Jay wrote: > On Jun 10, 2:39 pm, Fernando Nunes <domusonl...@gmail.com> wrote: >> Jay wrote: >>> Hi, >>> I am trying to migrate data from SQL Server toInformixdatabase. >>> Usingthe following bcp command I have exported the data intocsv >>> file. >>> bcp "SELECT skillID,profileID,skillName,active,dateInactive from >>> db_cra..Skill" queryout C:\\Skill.csv-w -S 10.76.253.124\\CRSSQL -t"," - >>> T >>> When I am trying to import the data back into IDSusingdbload, I am >>> getting the following error. >>> DBLOADLoad Utility INFORMIX-SQL Version 10.00.UC7X1 >>> Copyright IBM Corporation 1996, 2006 All rights reserved >>> Software Serial Number AAA#B000000 >>> In INSERT statement number 1 of raw data file /root/Test/Skill.csv. >>> Row number 1 is bad. >>> , >>> Unexpected error while reading field #1. >>> I have some characters like Chinese,Japanese,German present in the SQL >>> server table, which is trying to migrate. I also trieddbloadwith the >>> csvfiles generatedusingbcp command with -c , -N option etc. None of >>> the options work with International characters. >>> Locale settings whereInformixis installed : >>> CLIENT_LOCALE : en_US.utf8 >>> DB_LOCALE : en_US.utf8 >>> Can somebody please help me out? >> Can you provide an example line and if possible a table schema? >> Obviously we don't need to see your data, only an example of it that can >> reproduce the issue. >> >> Regards. > > Hi, > My table schema is : > > CREATE TABLE Skill ( > skillID int NOT NULL , > profileID int NOT NULL , > skillName nvarchar (50) NOT NULL , > active smallint NOT NULL , > dateInactive datetime year to fraction(3) > );> > ALTER TABLE Skill > ADD CONSTRAINT PRIMARY KEY ( > skillID, > profileID > ) CONSTRAINT PK_Skill;> > csv file I generated using the following bcp command from SQL server. > > bcp "SELECT skillID,profileID,skillName,active,dateInactive from > db_cra..Skill" queryout C:\\Skill.csv -w -S 10.76.253.124\\CRSSQL -t"," - > T > > Content of the csv file: > > 1,1,sk1,1, > 2,1,sk2,1, > 3,1,öü,1, > > Not able to import this file using dbload command. > > Thanks a lot! > > Regards > Jaya > raj I'm sorry... can you also provide the dbload command file? Are you specifying the correct delimiter? Thanks

On Jun 10, 4:14 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > Jay wrote: > > On Jun 10, 2:39 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > >> Jay wrote: > >>> Hi, > >>> I am trying to migrate data from SQL Server toInformixdatabase. > >>> Usingthe following bcp command I have exported the data intocsv > >>> file. > >>> bcp "SELECT skillID,profileID,skillName,active,dateInactive from > >>> db_cra..Skill" queryout C:\\Skill.csv-w -S 10.76.253.124\\CRSSQL -t"," - > >>> T > >>> When I am trying to import the data back into IDSusingdbload, I am > >>> getting the following error. > >>> DBLOADLoad Utility INFORMIX-SQL Version 10.00.UC7X1 > >>> Copyright IBM Corporation 1996, 2006 All rights reserved > >>> Software Serial Number AAA#B000000 > >>> In INSERT statement number 1 of raw data file /root/Test/Skill.csv. > >>> Row number 1 is bad. > >>> , > >>> Unexpected error while reading field #1. > >>> I have some characters like Chinese,Japanese,German present in the SQL > >>> server table, which is trying to migrate. I also trieddbloadwith the > >>> csvfiles generatedusingbcp command with -c , -N option etc. None of > >>> the options work with International characters. > >>> Locale settings whereInformixis installed : > >>> CLIENT_LOCALE : en_US.utf8 > >>> DB_LOCALE : en_US.utf8 > >>> Can somebody please help me out? > >> Can you provide an example line and if possible a table schema? > >> Obviously we don't need to see your data, only an example of it that can > >> reproduce the issue. > > >> Regards. > > > Hi, > > My table schema is : > > > CREATE TABLE Skill ( > > skillID int NOT NULL , > > profileID int NOT NULL , > > skillName nvarchar (50) NOT NULL , > > active smallint NOT NULL , > > dateInactive datetime year to fraction(3) > > );> > > ALTER TABLE Skill > > ADD CONSTRAINT PRIMARY KEY ( > > skillID, > > profileID > > ) CONSTRAINT PK_Skill;> > > csv file I generated using the following bcp command from SQL server. > > > bcp "SELECT skillID,profileID,skillName,active,dateInactive from > > db_cra..Skill" queryout C:\\Skill.csv -w -S 10.76.253.124\\CRSSQL -t"," - > > T > > > Content of the csv file: > > > 1,1,sk1,1, > > 2,1,sk2,1, > > 3,1,öü,1, > > > Not able to import this file using dbload command. > > > Thanks a lot! > > > Regards > > Jaya > > raj > > I'm sorry... can you also provide the dbload command file? Are you specifying > the correct delimiter? > Thanks Hi, dbloader.sh content is : dbload -d db_cra -c /root/Test/dbload_config -l /root/Test/dbload.log - e 0 -r -n 10000 -X dbload_config : FILE /root/Test/Skill.csv DELIMITER ',' 5; insert into skill; The issue seems to be related with locale setting. In Linux machine where IDS is installed , the locale settings are: CLIENT_LOCALE : en_US.utf8 DB_LOCALE : en_US.utf8 SERVER_LOCALE : en_US.utf8 I am using bcp -w option. Which output the csv file in UTF-16 format. If I manually convert this file into utf-8, dbload is working. So my question right now is how can I export in utf-8 format using bcp utility? OR can I do something so that dbload upload file encoded in UTF-16 format? Thanks, Jayaraj