SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Import from UTF-8 using OPENROWSET/BULK/FILE FORMA
 New Topic  Reply to Topic
 Printer Friendly
Author  Topic Next Topic  

mugurel.ilie
Starting Member

1 Posts

Posted - 04/09/2014 :  11:35:04  Show Profile  Reply with Quote
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

Brazil
1 Posts

Posted - 12/09/2014 :  12:28:55  Show Profile  Reply with Quote
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
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1742 Posts

Posted - 12/09/2014 :  12:35:25  Show Profile  Reply with Quote
"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
Go to Top of Page
   Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000