July 4, 2014 at 7:58 am
Hello,
I am trying to insert a csv file into a table using Bulk Insert , but all special(french) characters are not inserted correcly.
Is there a specific code page for the French_CI_AS collation ?
Thank you so much for any answers!
July 4, 2014 at 8:06 am
ralu_k_17 (7/4/2014)
Hello,I am trying to insert a csv file into a table using Bulk Insert , but all special(french) characters are not inserted correcly.
Is there a specific code page for the French_CI_AS collation ?
Thank you so much for any answers!
From http://msdn.microsoft.com/en-us/library/ms188365.aspx: -
Did you specify WITH ( CODEPAGE = 'ACP' ) ?
Are you importing in to an NVARCHAR or VARCHAR column?
July 4, 2014 at 8:10 am
Thanks for the reply!
I have specified CODEPAGE = 'ACP' and I am trying to import into a varchar column, but I modified it to a nvarchar column also to test it with CODEPAGE = 'ACP', but still the wrong characters are replacing the french special characters.
July 4, 2014 at 8:44 am
ralu_k_17 (7/4/2014)
Thanks for the reply!I have specified CODEPAGE = 'ACP' and I am trying to import into a varchar column, but I modified it to a nvarchar column also to test it with CODEPAGE = 'ACP', but still the wrong characters are replacing the french special characters.
OK, first I've produced my own CSV to import (only 5,000 items this time, will try more if it works).
IF OBJECT_ID('tempdb..#testNames') IS NOT NULL
BEGIN;
DROP TABLE #testNames;
END;
WITH t1 ( N ) AS ( SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
t2 ( N ) AS ( SELECT 1 FROM t1 x CROSS JOIN t1 y),
t3 ( N ) AS ( SELECT 1 FROM t2 x CROSS JOIN t2 y),
Tally ( N ) AS (
SELECT TOP 98
ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) )
FROM t3 x
CROSS JOIN t3 y
),
Tally2 ( N ) AS (
SELECT TOP 56
ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) )
FROM t3 x
CROSS JOIN t3 y
),
Combinations ( N ) AS (
SELECT DISTINCT
LTRIM(RTRIM(RTRIM(SUBSTRING(poss, a.N, 2)) + SUBSTRING(frechies, b.N, 1)))
FROM Tally a
CROSS JOIN Tally2 b
CROSS APPLY (
SELECT N'B C D F G H J K L M N P R S T V W Z SCSKKNSNSPSTBLCLFLGLPLSLBRCRDRFRGRPRTRVRSHSMGHCHPHRHWHBWCWSWTW'
) d ( poss )
CROSS APPLY (
SELECT N'AAACCCCDEEEEEGGGGHIIIIKLLL?NNN?OOORRRSSSŠTTTUUUUUUWYŸZZŽ'
) e ( frechies )
)
SELECT TOP 5000
IDENTITY( INT,1,1 ) AS ID,
a.N + b.N AS N
INTO #testNames
FROM Combinations a
CROSS JOIN Combinations b;
SELECT STUFF((SELECT CHAR(13) + N
FROM [#testNames] AS tn
FOR XML PATH(''),
TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
Here's the top 10 rows from my "CSV" file: -
WDBA
TRKBA
WT?BA
PSOBA
PSOBA
PSOBA
TBWBA
PHZBA
PHZBA
PHŽBA
I've saved the file, encoding set to UCS-2 (don't encode to UTF8 as SQL Server doesn't support it in BULK INSERT, AFAIK).
Next, I created a quick table: -
IF OBJECT_ID('dbo.SSC') IS NOT NULL
BEGIN;
DROP TABLE dbo.SSC;
END;
CREATE TABLE dbo.SSC
(
CHARACTERS NVARCHAR(20)
);
GO
Then I ran this: -
BULK INSERT dbo.SSC
FROM 'C:\test.csv'WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '', CODEPAGE = 'ASP' );
GO
Finally, I ran this: -
SELECT TOP 10
*
FROM dbo.SSC;
And the result was : -
(5000 row(s) affected)
CHARACTERS
--------------------
WDBA
TRKBA
WT?BA
PSOBA
PSOBA
PSOBA
TBWBA
PHZBA
PHZBA
PHŽBA
(10 row(s) affected)
July 7, 2014 at 8:27 am
Thanks so much for your reply!
It was the encoding part that I had wrong!
Thank you again! 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply