Characterset for bulk insert sql dosent work

team2005
team2005 used Ask the Experts™
on
Hi!

Have this stored procedure, and it works fine

SET QUOTED_IDENTIFIER OFF

go
CREATE PROCEDURE KJE_Kommune_importorter2
@PathFileName varchar(100),
@FileType int
AS

--Step 1: Build Valid BULK INSERT Statement
DECLARE @SQL varchar(2000)
IF @FileType = 1
 BEGIN
  SET @SQL = "BULK INSERT dbo.KJE_KOMMUNE_TMP FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = '"",""') "
 END
ELSE
 BEGIN
  SET @SQL = "BULK INSERT dbo.KJE_KOMMUNE_TMP FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = ',') "
 END

--Step 2: Execute BULK INSERT statement
EXEC (@SQL)


UPDATE dbo.KJE_KOMMUNE_TMP 
SET Kommunenummer =SUBSTRING(Kommunenummer,2,LEN(Kommunenummer)-2),
Kommunenavn =SUBSTRING(Kommunenavn,2,LEN(Kommunenavn)-2)	
	
--Update Existing
UPDATE dbo.KJE_KOMMUNE
SET dbo.KJE_KOMMUNE.Kommunenummer = dbo.KJE_KOMMUNE_TMP.Kommunenummer,
dbo.KJE_KOMMUNE.Kommunenavn = dbo.KJE_KOMMUNE_TMP.Kommunenavn



FROM dbo.KJE_KOMMUNE_TMP
INNER JOIN dbo.KJE_KOMMUNE
   ON dbo.KJE_KOMMUNE_TMP.Kommunenummer = dbo.KJE_KOMMUNE.Kommunenummer

--Insert New
INSERT INTO dbo.KJE_KOMMUNE (Kommunenummer,Kommunenavn) 


SELECT dbo.KJE_KOMMUNE_TMP.Kommunenummer,dbo.KJE_KOMMUNE_TMP.Kommunenavn

FROM dbo.KJE_KOMMUNE_TMP
LEFT OUTER JOIN dbo.KJE_KOMMUNE
   ON dbo.KJE_KOMMUNE_TMP.Kommunenummer = dbo.KJE_KOMMUNE.Kommunenummer
WHERE dbo.KJE_KOMMUNE.Kommunenummer IS NULL


// Sletter TMP tabellen
TRUNCATE TABLE dbo.KJE_KOMMUNE_TMP

Open in new window


Database and tables use characterset that handle 'øæå'

Have tryed to imort a textfile, that locks like

"0101","HALDEN"
"0104","MOSS"
"0105","SARPSBORG"
"0106","FREDRIKSTAD"
"0111","HVALER"
"0118","AREMARK"
"0119","MARKER"
"0121","RØMSKOG"
"0122","TRØGSTAD"

And the data is stored correctly...

But when i use this line of code, that use exec to execute the stored procedure
exec KJE_Kommune_importorter @PathFileName ='C:\testimport\kommune.txt', @FileType = 2 

Open in new window


it dosent store 'øæå' characters ?

What is missing here ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Harish VargheseProject Leader

Commented:
Hello,

You should mention that you are importing a Unicode text file by "DATAFILETYPE='widechar'" option. Try changing your BULK INSERT statements like below:

SET @SQL = "BULK INSERT dbo.KJE_KOMMUNE_TMP FROM '"+@PathFileName+"' WITH (DATAFILETYPE='widechar', FIELDTERMINATOR = '"",""') "

Thanks,
Harish

Author

Commented:
Hi!

Get this error message

Code: 4831 SQL State: 01000 --- Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.
Code: 4831 SQL State: 01000 --- Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.
Project Leader
Commented:
Hello,

That means, your file is not Unicode format. Can you try below options?

1. Convert your text file to Unicode by opening in Excel and Save As type "Unicode Text", and then try executing the proc.

2. instead of OR along with DATAFILETYPE option, use CODEPAGE='ACP' option.

Thanks,
Harish

Author

Commented:
Part 1. wasent right, but part 2 did the trick

Use this code:

DECLARE @SQL varchar(2000)
IF @FileType = 1
 BEGIN
  SET @SQL = "BULK INSERT dbo.KJE_KOMMUNE_TMP FROM '"+@PathFileName+"' WITH (CODEPAGE='ACP', FIELDTERMINATOR = '"",""') "
 END
ELSE
 BEGIN
  SET @SQL = "BULK INSERT dbo.KJE_KOMMUNE_TMP FROM '"+@PathFileName+"' WITH (CODEPAGE='ACP', FIELDTERMINATOR = ',') "
 END

Working great....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial