|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Strange characters with Bulk LoadI'm writing a small app to bulk load some data and receiving strange
results. Some of the data contains non-standard alphabetic characters, like the name Jose (with an accent over the e). It is being loaded into the table as JosT. The column is a VARCHAR(50), and it seems to work with regular INSERT statements. Has anyone encountered this type of stuff, or would anyone have an idea of a setting I might need to set to make this problem go away? Thanks Hi Mike,
You may like to try this create table tmpdata(n char(20),a int) BULK INSERT tmpdata DATAFILETYPE ='widechar' FROM 'c:\tmpdata.tbl' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '|\n' ) I hope it solves the problem. With Warm regards Jatinder Singh http://jatindersingh.blogspot.com http://sqloracle.tripod.com Hi js,
Thanks for the input. I should explain myself better (I was trying to get out to run for the train when I wrote that). I'm actually using the ODBC SQL Server Bulk Load API extensions (no BULK INSERT or BCP.EXE) to load the data in (bcp_bind, bcp_sendrow, etc.) For some reason though it's transmutating some of my characters on insert. Thanks Show quote "jsfromynr" <jatinder.1***@gmail.com> wrote in message news:1156483200.222655.46960@h48g2000cwc.googlegroups.com... > Hi Mike, > > You may like to try this > > create table tmpdata(n char(20),a int) > > BULK INSERT tmpdata DATAFILETYPE ='widechar' > FROM 'c:\tmpdata.tbl' > WITH > ( > FIELDTERMINATOR = ',', > ROWTERMINATOR = '|\n' > ) > > I hope it solves the problem. > > With Warm regards > Jatinder Singh > http://jatindersingh.blogspot.com > http://sqloracle.tripod.com > Hi Mike,
I am not an expert on SQL Bulk API etc,but I think the problem is with the unicode characters which in Bulk Insert we can specify. Where we will do the same in case of API ,I am not aware. The option must exist as you rightly said. With Warm regards Jatinder Singh http://jatindersingh.blogspot.com http://sqloracle.tripod.com I'm not sure what the problem is, but I don't think it's a driver
translation problem. When I run the equivalent INSERT statements it inserts the rows with the correct characters in the columns. It's just when I use the Bulk Operations API that it seems to want to change characters around on me. I'll have to do some investigation into the Bulk Operations API options and see if there's one I'm missing that affects character translations or something. Thanks Show quote "jsfromynr" <jatinder.1***@gmail.com> wrote in message news:1156484162.480297.174830@i42g2000cwa.googlegroups.com... > Hi Mike, > > I am not an expert on SQL Bulk API etc,but I think the problem is with > the unicode characters which in Bulk Insert we can specify. Where we > will do the same in case of API ,I am not aware. The option must exist > as you rightly said. > > With Warm regards > Jatinder Singh > http://jatindersingh.blogspot.com > http://sqloracle.tripod.com > I think I resolved the issue. It looks like it was a C++ problem, with
using a (signed) char array to hold my bulk load data. I changed it to an unsigned char array and it appears to be working. Thanks. Show quote "Mike C#" <x**@xyz.com> wrote in message news:uerrCJ8xGHA.480@TK2MSFTNGP06.phx.gbl... > I'm writing a small app to bulk load some data and receiving strange > results. Some of the data contains non-standard alphabetic characters, > like the name Jose (with an accent over the e). It is being loaded into > the table as JosT. The column is a VARCHAR(50), and it seems to work with > regular INSERT statements. Has anyone encountered this type of stuff, or > would anyone have an idea of a setting I might need to set to make this > problem go away? > > Thanks > Correction: Someone gave me a build script for the table that specifies
different collations on a few of the columns. Strange that INSERT statements work 'properly' for the wrong collation, but the Bulk Insert functions don't. I'm going to have to research further and see what options are available for converting these strings to the right collations before inserting. Thanks again. Show quote "Mike C#" <x**@xyz.com> wrote in message news:OQhdjSGyGHA.4392@TK2MSFTNGP04.phx.gbl... >I think I resolved the issue. It looks like it was a C++ problem, with >using a (signed) char array to hold my bulk load data. I changed it to an >unsigned char array and it appears to be working. Thanks. > > "Mike C#" <x**@xyz.com> wrote in message > news:uerrCJ8xGHA.480@TK2MSFTNGP06.phx.gbl... >> I'm writing a small app to bulk load some data and receiving strange >> results. Some of the data contains non-standard alphabetic characters, >> like the name Jose (with an accent over the e). It is being loaded into >> the table as JosT. The column is a VARCHAR(50), and it seems to work >> with regular INSERT statements. Has anyone encountered this type of >> stuff, or would anyone have an idea of a setting I might need to set to >> make this problem go away? >> >> Thanks >> > >  |
|||||||||||||||||||||||
Other interesting topics