COPY INTO for Azure Synapse when column type is bit fails if csv file has 'true/false' or any number other than 1 or 0. Is there a way to avoid this error?

Question

question

PromitRoy-1825 asked Jan 19, '21 NAGASANDEEPKUMARKAPA-2322 rolled back May 7, '22

COPY INTO for Azure Synapse when column type is bit fails if csv file has 'true/false' or any number other than 1 or 0. Is there a way to avoid this error?

As per the question title, I'm trying to load some data from a csv file into Azure Synapse Analytics using the COPY INTO statement. My table has a bit column and my csv file has a 'true/false' at the index of this bit column, and my COPY INTO command keeps failing. So I've been trying with a few csv files, and anything that is not a 1/0 results in COPY failure.

Inserting through the online SQL editor works fine though.
INSERT INTO [dbo].[testtable] values (0)
INSERT INTO [dbo].[testtable] values (7)
INSERT INTO [dbo].[testtable] values ('true')

result in False,True,True being inserted into the table successfully.

azure-synapse-analytics

· 1

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.

SaurabhSharma-msft · Jan 19, 2021 at 11:39 PM

@PromitRoy-1825 Thanks for using Microsoft Q&A !!

I am able to reproduce the same and checking internally with the products team on the same. I will get back to you as soon as I hear back from them. Thanks

1 ·

Answer 1 · 2021-01-20T00:07:48.053Z

SaurabhSharma-msft answered Jan 20, '21 NAGASANDEEPKUMARKAPA-2322 rolled back May 7, '22

@PromitRoy-1825 I have received information from products team that they are aware of this issue and actively working to rollout a fix for the same. Thanks for bringing this to our attention.

Thanks
Saurabh

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

· 5

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.

rezaniroomand · Mar 25 at 11:30 AM

I am assuming that the problem is not solved and you need more years to fix it?! :)

1 ·

SaurabhSharma-msft rezaniroomand · Mar 25 at 07:52 PM

Hi @rezaniroomand,

This issue has been fixed and I just tried it on a dedicated production cluster, and it works.

Please try to use appropriate file FIELDQUOTE in COPY statement if you are using bit value in single quotes like below -

Also, if you are not using any quotes then specifying the value as just True in the CSV file should work fine.

Please let me know if you have any concerns.

Thanks
Saurabh

0 ·

image.png (11.4 KiB)

image.png (52.5 KiB)

Konstantin-9879 · Feb 02, 2021 at 07:20 AM

Is there any update about fix?

0 ·

SaurabhSharma-msft Konstantin-9879 · Feb 04, 2021 at 02:03 AM

@Konstantin-9879 Sorry for the delay. Products team is still working on the fix. I will update the thread as soon as it is released.

0 ·

NAGASANDEEPKUMARKAPA-2322 · May 07 at 11:49 AM

Hi ,

Is there any fix for this problem.

In my case I am using external tables to access the CSV files.

For few of external tables i am able to access the files using table which has True/False in the CSV but for some of the others I am unable to do it if i am accessing them for the first time.

But after accessing the other tables which are working fine. Now i am able to access the tables which has thrown error before. So my problem is like on and off.

Thanks if anyone can provide any solution for this.

0 ·

Answer 2 · 2021-05-03T17:01:26.133Z

billym-9548 answered May 3, '21 billym-9548 edited May 3, '21

I'm assuming this thread would be updated after the fix, as discussed above. But still wanted to double check if this is still on the radar. Encountering the exact same behavior as the OP.

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.

question