Not able to load Japanese special characters in csv files using Data Flow task in ADF.

Pratyay Bhattacharjee 20

Hi,

We are trying to load a csv file with Japanese Special Characters using data flow task in Azure Data Factory to Sql Server tables and it fails to render and load Japanese characters.

We notice that the Japanese special characters are substituted with ‘?’ in the database after loading. We tried different options by changing the encoding to other types (such as (Default)UTF-8, UTF-16LE, UTF-8 without BOM etc.) but still it did not help.

But when we use a Data Copy task in ADF to load the data with Japanese special characters, it loads perfectly fine.

However, we still need to use data flow task only in the pipeline as there are some transformations needed on the data which data copy task isn’t able to support. Also, we don’t want to implement a different pipeline design just for this special character issue by replacing the data flow task with something else as that might create issues for code maintenance and we want a standard consistent design to maintain code reusability.

So, this seems to be an Azure product bug that the data flow task in ADF is not able to render and load specific special characters even with UTF-8 encoding.

Can you please provide a resolution or fix for this using Data Flow task in ADF.

Thanks,

Pratyay

Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2023-05-03T22:52:07.53+00:00

Hello Pratyay Bhattacharjee ,

I am checking to see if you got a chance to look into my earlier response. Please let me know if you have any further questions.
Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2023-05-05T02:00:33.6466667+00:00

Hello Pratyay Bhattacharjee ,

I am checking to see if you have any further questions here.
Pratyay Bhattacharjee 20 Reputation points

2023-05-15T15:05:42.6533333+00:00

Hi BhargavaGunnam-MSFT ,

First of all, apologies for the late response.

Please find attached the sample file having Japanese characters in TRANSACTION_NAME column, you can use this data sample if you would like to reproduce the issue at your end.

Also, regarding your first suggestion - I opened the csv file in notepad++ editor and converted the encoding to UTF-8 encoding and reloaded the file but still the Japanese characters were not rendered correctly (still had ‘?’ for the characters).

I have a question regarding the 2<sup>nd</sup> suggestion - replacing the column value with Japanese special characters using expression to Unicode in the derived column transformation in data flow task.

Can you please advise if I need to replace ''with the list of all Japanese special characters.

So, it should be something like this (with only a few Japanese characters in this example) - replace(TRANSACTION_NAME, '亜哀挨愛曖悪握圧扱宛', N'Unicode equivalent')

Can you kindly confirm.

Please let me know if you need anything from my side regarding this or if you would like me to setup a meeting to discuss further on the potential resolutions.

Thanks and Regards,

Pratyay
Pratyay Bhattacharjee 20 Reputation points

2023-05-15T15:07:56.8133333+00:00

Japan_Transactions.txt

I missed to attach the file earlier. Please find attached the sample file having Japanese characters in TRANSACTION_NAME column, you can use this data sample if you would like to reproduce the issue at your end.

Thanks,

Pratyay

Bhargava-MSFT 29,266 Microsoft Employee

Hello Pratyay Bhattacharjee ,

No problem. Thank you for the reply.

I tried to reproduce the issue from my end, but I see the same results using copy activity or with the dataflow(my db collation-SQL_Latin1_General_CP1_CI_AS)

Are you using the default collation here?

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS Collation;

Copy activity Json:

{
    "name": "Japanesecharissue",
    "properties": {
        "activities": [
            {
                "name": "Copy data1",
                "type": "Copy",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "DelimitedTextSource",
                        "storeSettings": {
                            "type": "AzureBlobFSReadSettings",
                            "recursive": true,
                            "enablePartitionDiscovery": false
                        },
                        "formatSettings": {
                            "type": "DelimitedTextReadSettings"
                        }
                    },
                    "sink": {
                        "type": "AzureSqlSink",
                        "writeBehavior": "insert",
                        "sqlWriterUseTableLock": false,
                        "tableOption": "autoCreate",
                        "disableMetricsCollection": false
                    },
                    "enableStaging": false,
                    "translator": {
                        "type": "TabularTranslator",
                        "typeConversion": true,
                        "typeConversionSettings": {
                            "allowDataTruncation": true,
                            "treatBooleanAsNumber": false
                        }
                    }
                },
                "inputs": [
                    {
                        "referenceName": "DS_gen2",
                        "type": "DatasetReference"
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "AzureSqlTable8",
                        "type": "DatasetReference"
                    }
                ]
            }
        ],
        "annotations": []
    }
}

Dataflow Json:

{
    "name": "dataflow4",
    "properties": {
        "type": "MappingDataFlow",
        "typeProperties": {
            "sources": [
                {
                    "dataset": {
                        "referenceName": "DS_gen2",
                        "type": "DatasetReference"
                    },
                    "name": "source1"
                }
            ],
            "sinks": [
                {
                    "dataset": {
                        "referenceName": "AzureSqlTable8",
                        "type": "DatasetReference"
                    },
                    "name": "sink1"
                }
            ],
            "transformations": [],
            "scriptLines": [
                "source(output(",
                "          id as string,",
                "          SalePersonFName as string,",
                "          SalePersonLName as string,",
                "          ProductName as string,",
                "          ItemsSold as string,",
                "          SoldPrice as string,",
                "          SoldDate as string,",
                "          City as string,",
                "          State as string,",
                "          Country as string,",
                "          Region as string",
                "     ),",
                "     allowSchemaDrift: true,",
                "     validateSchema: false,",
                "     ignoreNoFilesFound: false) ~> source1",
                "source1 sink(allowSchemaDrift: true,",
                "     validateSchema: false,",
                "     deletable:false,",
                "     insertable:true,",
                "     updateable:false,",
                "     upsertable:false,",
                "     format: 'table',",
                "     skipDuplicateMapInputs: true,",
                "     skipDuplicateMapOutputs: true,",
                "     errorHandlingOption: 'stopOnFirstError') ~> sink1"
            ]
        }
    }
}

User's image

Pratyay Bhattacharjee 20 Reputation points

2023-05-16T16:26:48.1133333+00:00

Hi BhargavaGunnam-MSFT,

Just wanted to share the good news that we were able to load the Japanese characters in the DB.

Actually the issue was with a transformation logic after the data flow, so we updated the scripts to load the values converting it to Unicode first and it worked with that.

INSERT INTO DBO.STG_ASIA_DAILY (TRANSACTION_NAME) VALUES (N'WM - Annutiy / 一括年金件数');

Thanks for your support with this, you are good to close the ticket now.

Pratyay
Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2023-05-16T16:32:57.4+00:00

Hello Pratyay Bhattacharjee,

Glad to know that you are able to resolve the issue, and thank you so much for letting us know the actual issue as it helps the community look for answers to similar questions.

Have a great day!

Accepted answer

Bhargava-MSFT 29,266 Reputation points Microsoft Employee

2023-05-01T21:29:50.8433333+00:00
Hello Pratyay Bhattacharjee ,

Welcome to the MS Q&A platform.

Could you please send a sample CSV file(please remove any sensitive data) to reproduce the issue from my end?

Meanwhile, please try the below steps:

Open the CSV file in a text editor like Notepad++ and check the encoding of the file. Make sure that it is saved in UTF-8 encoding.

In the Data Flow task, add a Derived Column transformation and use the following expression to convert the column with Japanese special characters to Unicode:

replace(columnName, '', N'Unicode equivalent')

Replace ''with the Japanese special characters,

After the Derived Column transformation, add a Sink transformation to load the data into the SQL Server table.

In the Sink transformation, ensure the encoding is set to UTF-8.

Save and run the pipeline to load the data into the SQL Server table.

Please try and let me know if you see any issues.

I hope this helps.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Share via

Not able to load Japanese special characters in csv files using Data Flow task in ADF.

0 additional answers