COPY INTO error caused by non-ASCII characters

Question

COPY INTO error caused by non-ASCII characters

Hans Boëtius 21

Hello,

I get an error message when executing the COPY INTO statement. The error message is caused by some non-ASCII characters in my file.

The file that I want to process has only a few (32) records. It has a column with a customer name in it. Three records have non-ASCII characters, and only one record is causing the issue.
This record has value TÜV Süd as customer name. If I change TÜV Süd to TUV Sud then the COPY INTO statement runs successfully.
That is strange, because there are two other records with non-ASCII characters. One record has Gebrüder as (part of the) customer name and the other has Österreich as (part of the) customer name.

My statement looks like:
COPY INTO [Schema].[Table]
FROM 'https://******************/input_data/storage_vlde/'
WITH
(
FILE_TYPE = 'CSV'
, FIRSTROW = 1
, DATEFORMAT = 'ymd'
, ENCODING = 'UTF8'
)

The error message is "Bulk load data conversion error (truncation) for row starting at byte offset 641, column 4 (COUNTRY_CD) in data file /*************/input_data/storage_vlde/CPS_Storage_VlDE_202209.csv."

Any suggestions on a possible solution?

Regards, Hans

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-12-09T21:14:56.28+00:00

Hello @Hans Boëtius ,

Welcome to the MS Q&A platform.

It could be that your table column is not big enough to insert the data. Can you please check your column datatypes?

And could you send the table definition and sample CSV file(please remove any PPI data) that you are using here? Once you send the file, I will try to reproduce the issue and get back to you.
Hans Boëtius 21 Reputation points

2022-12-12T09:27:46.19+00:00
Hi,

Thanks for your quick reply.

Attached two files:

Example01 - Original.txt: Original file (with the non-ascii rows) that returns a failure.

Example02 - Removed non-ascii.txt: Adjusted file in which I replaced Ü and ü with U and u. The non-ascii characters Ö and à remain in the file. They are uploaded, but do not show correct in the table.

I did some more testing and I managed to upload the value of 'TÜV süd' once in the table, and it actually showed the value correct (so including the non-ascii values). I tried to redo it again, but that resulted in the same error message. So, it looks quite unpredictable what is happening.

Below the CREATE TABLE statement.269554-example01-original.txt

Regards, Hans

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [FINANCE_MART].[CPS_TMP_IMPORT_TEST]
(
[METRIC_CD] varchar NOT NULL,
[CUSTOMER_ID] varchar NOT NULL,
[CUSTOMER_NAME] nvarchar NOT NULL,
[COUNTRY_CD] char NOT NULL,
[CURRENCY_CD] char NOT NULL,
[FISCAL_PERIOD] decimal NOT NULL,
[IMPORT_VAL] decimal NOT NULL,
[STATUS] varchar NULL
)
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX
)
GO
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-12-12T18:53:15.867+00:00

Hello @Hans Boëtius ,

Thank you so much for the details, and apologies for the inconvenience here

From my end, I can bypass the error by uploading two records at a time with the “example01-orignal.txt’ file.

Attempt 1: Upload the CSV file with the below records and be able to insert the records.
mBespokeStockLC,2222222,TÜVSüd,DE,EUR,202209,4814.41
mBespokeStockLC,1111111,Gebrüder,DE,EUR,202209,142

Attempt 2: Upload the CSV file with the below records and be able to insert the records.
mBespokeStockLC,1111111,Öst,DE,EUR,202209,2957.1
mBespokeStockLC,6666666,S.àr.l.& Co. KG,DE,EUR,202209,315.04

But If I use the original file, it results in the below error.

I reached out to my internal team on this issue. I will get back to you as soon as I hear from them.

Accepted answer

0 additional answers

Your answer

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-12-09T21:14:56.28+00:00

Hello @Hans Boëtius ,

Welcome to the MS Q&A platform.

It could be that your table column is not big enough to insert the data. Can you please check your column datatypes?

And could you send the table definition and sample CSV file(please remove any PPI data) that you are using here? Once you send the file, I will try to reproduce the issue and get back to you.
Hans Boëtius 21 Reputation points

2022-12-12T09:27:46.19+00:00

Hi,

Thanks for your quick reply.

Attached two files:

Example01 - Original.txt: Original file (with the non-ascii rows) that returns a failure.

Example02 - Removed non-ascii.txt: Adjusted file in which I replaced Ü and ü with U and u. The non-ascii characters Ö and à remain in the file. They are uploaded, but do not show correct in the table.

I did some more testing and I managed to upload the value of 'TÜV süd' once in the table, and it actually showed the value correct (so including the non-ascii values). I tried to redo it again, but that resulted in the same error message. So, it looks quite unpredictable what is happening.

Below the CREATE TABLE statement.269554-example01-original.txt

Regards, Hans

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [FINANCE_MART].[CPS_TMP_IMPORT_TEST]
(
[METRIC_CD] varchar NOT NULL,
[CUSTOMER_ID] varchar NOT NULL,
[CUSTOMER_NAME] nvarchar NOT NULL,
[COUNTRY_CD] char NOT NULL,
[CURRENCY_CD] char NOT NULL,
[FISCAL_PERIOD] decimal NOT NULL,
[IMPORT_VAL] decimal NOT NULL,
[STATUS] varchar NULL
)
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX
)
GO
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-12-12T18:53:15.867+00:00

Hello @Hans Boëtius ,

Thank you so much for the details, and apologies for the inconvenience here

From my end, I can bypass the error by uploading two records at a time with the “example01-orignal.txt’ file.

Attempt 1: Upload the CSV file with the below records and be able to insert the records.
mBespokeStockLC,2222222,TÜVSüd,DE,EUR,202209,4814.41
mBespokeStockLC,1111111,Gebrüder,DE,EUR,202209,142

Attempt 2: Upload the CSV file with the below records and be able to insert the records.
mBespokeStockLC,1111111,Öst,DE,EUR,202209,2957.1
mBespokeStockLC,6666666,S.àr.l.& Co. KG,DE,EUR,202209,315.04

But If I use the original file, it results in the below error.

I reached out to my internal team on this issue. I will get back to you as soon as I hear from them.

Answer 1

Bhargava-MSFT 31,261 Microsoft Employee Moderator

Hello @Hans Boëtius ,

After further investigation, we found out the file you shared is not in UTF-8 format. We support only UTF encoding for Synapse SQL. You need to convert it to UTF to load it into Synapse SQL.

I converted the file to UTF-8 by opening it in Notepad and changing the “Encoding,” as shown in the below screenshot.

After that, I successfully loaded it into Synapse SQL using COPY INTO 269720-example01-original-utf8.txt.

Attached is the converted file here for your reference.

------------------------------

Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Hans Boëtius 21 Reputation points

2022-12-13T08:34:18.52+00:00

Hello,

Changing the encoding to UTF-8 did the trick.
Thanks a lot!

Regards, Hans
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2022-12-13T14:09:57.8+00:00

Hello @Hans Boëtius ,
Thank you so much for the confirmation.
If you don't have any further questions, please consider accepting the answer by hitting the Accept answer button, as it helps the community.

Share via

COPY INTO error caused by non-ASCII characters

0 additional answers

Your answer