Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Codepage issues on bulk insert.

Author  Topic 

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-16 : 04:27:42
Ok, i am doing a bulk insert but the text file that comes in has some unrecognized characters and is "pushing" my text to be of more characters(2 bytes instead of 1 ?) thus inserting it to the next column.Supposed to be a Greek text file but i also see he strange character if i don't modify the text to another format(i need NOT to modify the file first) I need to fix this.
Details:
A sample"06209YEA9686 ΚΟΡΔΟΠΑΤΗΣ ΔΗΜ/MERGER BEATRICEΦΑΡΩ� 13-15 111 4"
So what i have is: Database collation: SQL_Latin1_General_CP1_CI_AS
I am using nvarchar for the text fields. I am not sure if this is the correct format.
The best i could come with is CODEPAGE = '65001' in the with statement of the bulk insert . That will fix some characters but not all.
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-16 : 04:34:16
What collation are you using for the import?
It seems the characters are converted at the read process.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-16 : 04:44:46
You mean the xml file? I don't use something right now.Should i use a collation in the xml file?
I have also changed the collation to GREEK_CI_AI but will do the same.
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-16 : 05:33:41
Found that if i save the text file as ANSI, it will show correct again when i bulk insert to SQL it will have the strange characters.
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-07-16 : 07:05:20
So i've read somewhere that it may be the xml files that does the problem. The problem here is how would i try to set the fields without an xml file(?). Is that even possible for the data positioning ? How will this play on the with clause, without the xml file?
<FIELD ID="1" xsi:type="CharFixed" LENGTH="9" />
Thanks.
Go to Top of Page
   

- Advertisement -