Import from UTF-8 using OPENROWSET/BULK/FILE FORMA

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 2000 Forums

SQL Server Development (2000)

Import from UTF-8 using OPENROWSET/BULK/FILE FORMA

Author

Topic

mugurel.ilie
Starting Member

1 Post

Posted - 2014-04-09 : 11:35:04

Hello,

I have to import into MS SQL 2012 (without SSIS) some data from csv files which come in UTF-8.
I used the following script using Bulk with format file:
insert into Clients
SELECT * FROM OPENROWSET(BULK 'C:\DbFiles\Clients.CSV',
FORMATFILE='C:\DbFiles\Clients.Xml', firstrow=1, codepage='1250') as t1;

The xml file format is:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="#" MAX_LENGTH="9"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="#" MAX_LENGTH="28"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="28" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="ElemTyp" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="gvon" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="gbis" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

The import went fine but I have some problems with special Romanian characters.

How can I import these special chars even if MS SQL Server doesn't support UTF-8 ?

Thank you !

dariva
Starting Member

1 Post

Posted - 2014-12-09 : 12:28:55

Hi!
I am facing the same problema. Before using OPENROWSET to import data from a csv file I was using the BULK INSERT method with the option CODEPAGE = 65001 and it was working fine the non-English characters. Here is the code I was using.

Create table #file_contents_aux (line varchar(300))
exec ('bulk insert #file_contents_aux from "' + @Path + '"' + ' with(CODEPAGE = ''65001'')')

the problem with Bulk INSERT is that when you a lot of rows in your file it usually don't bring than in the same order as it appears in the file. For my application this is important but if it is not for you, you can use the BULK INSERT.

Hope it can help you

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-09 : 12:35:25

"the problem with Bulk INSERT is that when you a lot of rows in your file it usually don't bring than in the same order as it appears in the file. For my application this is important but if it is not for you, you can use the BULK INSERT."

SQL does not store rows in any particular order unless you have a clustered index. That is why you need an ORDER BY clause when you retrieve your data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources