question

PromitRoy-1825 avatar image
2 Votes"
PromitRoy-1825 asked NAGASANDEEPKUMARKAPA-2322 rolled back

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

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

@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 Vote 1 ·
SaurabhSharma-msft avatar image
1 Vote"
SaurabhSharma-msft answered NAGASANDEEPKUMARKAPA-2322 rolled back

@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

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

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

1 Vote 1 ·

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 -
186937-image.png
187000-image.png

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 Votes 0 ·
image.png (11.4 KiB)
image.png (52.5 KiB)

Is there any update about fix?

0 Votes 0 ·

@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 Votes 0 ·

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 Votes 0 ·
billym-9548 avatar image
0 Votes"
billym-9548 answered billym-9548 edited

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

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