Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (True/False) in Azure Synapse

Question

question

NAGASANDEEPKUMARKAPA-2322 asked May 9, '22 KranthiPakala-MSFT commented May 18, '22

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (True/False) in Azure Synapse

The above issue in the question is happening on and off while accessing the external table. In my case we have created external tables using model.json file which has the schema of different tables.

For some of the tables it is throwing the above error when accessed for the first time .

After that I have tried accessing another table which is not throwing the above error. After that I have again tried to access the first table which has thrown the error . Surprisingly now i am able to access the table without any error.

If i schedule the pipeline which continuously query CSV files if the above problem appears then it will be an issue for data loading.

Looking for a solution for the question. Thanks in advance.

azure-data-factory azure-synapse-analytics

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KranthiPakala-MSFT · May 10 at 09:19 PM

Hello @NAGASANDEEPKUMARKAPA-2322,

Thanks for the question and using MS Q&A platform.

As the issue is inconsistent, it will be hard to predict the root cause, but from the error message shared above, it seems like the column(boolean\bit) on the CSV file is having true or false value which is why it is complaining a mismatch or invalid character. This usually occurs wrong data type used in WITH clause or source column values are not relevant to the datatype specified in the query.

To mitigate this issue could you please try to update/change the column(boolean\bit) on the file to 0 or 1 instead of true or false and see if that helps?

Do let us know how it goes.

Thanks

0 ·

NAGASANDEEPKUMARKAPA-2322 KranthiPakala-MSFT · May 11 at 11:08 AM

Hi,

Thanks for the reply. Do you meant to say we need to change the source file schema in json (instead of Boolean true/false to 0/1) .
which will be done while exporting using synapse link to data lake ? Is it possible to do that while exporting ? @KranthiPakala-MSFT

0 ·

NAGASANDEEPKUMARKAPA-2322 KranthiPakala-MSFT · May 13 at 11:13 AM

Hi @KranthiPakala-MSFT ,

Those CSV files are being exported using azure synapse link as it doesn't have the control to change specific column like that. Do you have any other idea how to resolve this or change from true /false to 0/1 ? .
Could you please help in resolving this .

Thanks.

0 ·

question