topic Re: Try to sync .csv file to ms sql in Qlik Replicate

Try to sync .csv file to ms sql

Aaron_Liu — Wed, 21 Feb 2024 09:26:08 GMT

Hi Sirs,

My customer want to add a new file(.csv file) as source and they has custom code page word , I was generated a .csv file to sync to MS SQL(that .csv file as attachment include custom code page word) , but the custom code page word not success sync.

Please see the attachment file 'test_customcodepage.jpg' and 'EndPoint_Setting.jpg' and 'Column_Setting.jpg' and 'SQL_QueryResult.jpg'

Please tell us how to solve this problem.
Thanks.
Aaron 2024-2-21

Re: Try to sync .csv file to ms sql

john_wang — Wed, 21 Feb 2024 09:32:58 GMT

Hello @Aaron_Liu ,

Thanks for reaching out to Qlik Community!

I do not see the file contains Chinese chars. Could you please check with that file codepage and what's the chars? BIG5 chars? or something else.

Thanks,

John.

Re: Try to sync .csv file to ms sql

Aaron_Liu — Wed, 21 Feb 2024 09:36:55 GMT

Hi John,

It is used UTF-8 chars. I reload the file as attachment. (or You can see the test_customcodepage.jpg)

Thanks.

Aaron 2024-2-21

Re: Try to sync .csv file to ms sql

john_wang — Wed, 21 Feb 2024 14:19:27 GMT

Hi @Aaron_Liu ,

This is because your source file is inaccurate. There are no valid UTF8 Chinese chars.

I've made a sample file with UTF8 encoding file and it works for me, replicate from File Source to SQL Server target, the Chinese chars are replicated correctly:

Regards,

John.

Re: Try to sync .csv file to ms sql

Aaron_Liu — Thu, 22 Feb 2024 03:23:45 GMT

Hi John,

I try other normally chinese word, it is success to sync to MS SQL.as below pic

Original "test_customcodepage.csv" include the custom code page word (難字/自造字) that was in utf-8 scope. but those word cannot sync to MS SQL. as below pic

Thanks.
Aaron 2024-2-22

Re: Try to sync .csv file to ms sql

john_wang — Thu, 22 Feb 2024 03:28:03 GMT

Hello @Aaron_Liu ,

Thanks for the feedback. Are you able to write these chars (難字/自造字) into SQL Server manually? By any ODBC tools , or SSMS.

Regards,

John.

Re: Try to sync .csv file to ms sql

DesmondWOO — Thu, 22 Feb 2024 10:11:26 GMT

Hi @Aaron_Liu ,

My Windows is using Chinese (Hong Kong SAR) language. Open your test_customcodepage.csv, Chinese characters are shown as below:

So your customized Chinese characters overlap the code points which are defined in the HK codepages.

I ran Replicate to load above Chinese characters into NVARCHAR. Result is as below:

If your system doesn't haven customized font, you won't be able to display those Chinese characters. To verify, you may use master.dbo.fn_varbintohexstr(CAST([column_name] AS varbinary)) to check the hex values of records.

Regards,
Desmond

Re: Try to sync .csv file to ms sql

Aaron_Liu — Fri, 23 Feb 2024 06:18:21 GMT

Hi John, Hi Desmond,

Thanks for your reply. You are right!! My designe test case data has inaccurate, because original CSV file content customized Chinese characters was copy and paste from other file, discuss with Gavin who said that's wrong doing, should be "type" into the CSV file, not used copy and paste.

So I re-gen a CSV file as attachment that was sync success.

Thanks for your support!!!

Aaron 2024-2-23

Re: Try to sync .csv file to ms sql

john_wang — Fri, 23 Feb 2024 14:01:59 GMT

Hello @Aaron_Liu ,

Thanks for your new demo file. Now it works in my labs. After replicated to SQL Server target I got the chars/graphics correctly:

Feel free to let us know if you need any additional assistance.

Good luck,

John.