Leading zeros issue in csv

Question

Leading zeros issue in csv

Sharad Gawade 20

Hi Team,

I am trying to load data from sql table to CSV file through ADF copy activity but after loading data into csv I can see leading zeros from all cell got removed automatically. CSV file created automatically through ADF pipeline so i can not change format to text before ingest data and at both sides source and target column type as string only.

So can you suggest any possible way to load data as it is into csv from table(including leading 0 as well)?

Sharad Gawade 20 Reputation points

2024-01-09T04:02:38.47+00:00

Hi Suba Balaji,

Thanks for response.

In preview option of source dataset, I can see leading zeros and double quote option at sink is already there but still same issue. even after uploading file at blob through sink. In edit option I can see leading zeros is there but after download file at local machine and open it leading zeros removing automatically.
Olaf Helper 47,436 Reputation points

2024-01-09T06:44:46.7733333+00:00

I can see leading zeros from all cell got removed automatically.

Then you tread your data somewhere in the flow as numeric and not as a string; numerics don't have leading zeros.
Sharad Gawade 20 Reputation points

2024-01-09T06:58:49.8333333+00:00

It is just simple copy activity. source as sql table with data type of all columns is nvarchar and target as csv again it's string. so not sure where it become numbers in between.
Sharad Gawade 20 Reputation points

2024-01-10T11:08:03.1466667+00:00

Hi Team, Any possible way to tackle this issue?
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-01-10T12:18:55.1066667+00:00
Hi @Sharad Gawade

Welcome to Microsoft Q&A platform and thanks for posting your question here.
I understand that you are facing an issue with leading zeros being removed automatically from all cells in a CSV file created through ADF pipeline. If the source and target column types are string, then the issue might be due to the default behaviour of Excel to remove leading zeros.

To resolve this issue, you can try the following options:

Use a custom format string in the ADF copy activity to retain leading zeros. You can use the following format string in the "Copy behavior" section of the ADF copy activity:

{"type": "string", "format": "000000"}

This format string will ensure that the output CSV file retains leading zeros for all cells.

Another option is to use a different delimiter in the CSV file. By default, ADF uses a comma (",") as the delimiter. You can try using a different delimiter such as a pipe ("|") or tab ("\t") to see if it resolves the issue.

I hope this helps! Let me know if you have any further questions or concerns.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-01-11T02:56:53.6666667+00:00

@Sharad Gawade We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sharad Gawade 20 Reputation points

2024-01-11T08:13:42.4666667+00:00

Hi Smaran Thoomu, Thanks for your response. Tried both options but it is not working as expected.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-01-12T02:39:27.5166667+00:00

@Sharad Gawade For a deeper investigation and immediate assistance on this issue, if you have a support plan could you please file a support ticket and do share the SR# with us? In case if you don't have a support plan, please let us know here.
Sharad Gawade 20 Reputation points

2024-01-12T07:42:59.0833333+00:00

Hi Smaran Thoomu, I found root cause of this issue. attached screenshot is for notification when I open csv and I clicked on convert and don't show option that's why it was converting into numeric value.Screenshot 2024-01-12 130619.png
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-01-12T08:26:21.09+00:00

@Sharad Gawade Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Accepted answer

1 additional answer

Your answer

Sharad Gawade 20 Reputation points

2024-01-09T04:02:38.47+00:00

Hi Suba Balaji,

Thanks for response.

In preview option of source dataset, I can see leading zeros and double quote option at sink is already there but still same issue. even after uploading file at blob through sink. In edit option I can see leading zeros is there but after download file at local machine and open it leading zeros removing automatically.
Olaf Helper 47,436 Reputation points

2024-01-09T06:44:46.7733333+00:00

I can see leading zeros from all cell got removed automatically.

Then you tread your data somewhere in the flow as numeric and not as a string; numerics don't have leading zeros.
Sharad Gawade 20 Reputation points

2024-01-09T06:58:49.8333333+00:00

It is just simple copy activity. source as sql table with data type of all columns is nvarchar and target as csv again it's string. so not sure where it become numbers in between.
Sharad Gawade 20 Reputation points

2024-01-10T11:08:03.1466667+00:00

Hi Team, Any possible way to tackle this issue?
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-01-10T12:18:55.1066667+00:00

Hi @Sharad Gawade

Welcome to Microsoft Q&A platform and thanks for posting your question here.
I understand that you are facing an issue with leading zeros being removed automatically from all cells in a CSV file created through ADF pipeline. If the source and target column types are string, then the issue might be due to the default behaviour of Excel to remove leading zeros.

To resolve this issue, you can try the following options:

Use a custom format string in the ADF copy activity to retain leading zeros. You can use the following format string in the "Copy behavior" section of the ADF copy activity:

{"type": "string", "format": "000000"}

This format string will ensure that the output CSV file retains leading zeros for all cells.

Another option is to use a different delimiter in the CSV file. By default, ADF uses a comma (",") as the delimiter. You can try using a different delimiter such as a pipe ("|") or tab ("\t") to see if it resolves the issue.

I hope this helps! Let me know if you have any further questions or concerns.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-01-11T02:56:53.6666667+00:00

@Sharad Gawade We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sharad Gawade 20 Reputation points

2024-01-11T08:13:42.4666667+00:00

Hi Smaran Thoomu, Thanks for your response. Tried both options but it is not working as expected.
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-01-12T02:39:27.5166667+00:00

@Sharad Gawade For a deeper investigation and immediate assistance on this issue, if you have a support plan could you please file a support ticket and do share the SR# with us? In case if you don't have a support plan, please let us know here.
Sharad Gawade 20 Reputation points

2024-01-12T07:42:59.0833333+00:00

Hi Smaran Thoomu, I found root cause of this issue. attached screenshot is for notification when I open csv and I clicked on convert and don't show option that's why it was converting into numeric value.Screenshot 2024-01-12 130619.png
Smaran Thoomu 24,110 Reputation points Microsoft External Staff Moderator

2024-01-12T08:26:21.09+00:00

@Sharad Gawade Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Answer 1

Hi @Sharad Gawade

Welcome to Microsoft Q&A platform and thanks for posting your question.

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

Ask: Trying to load data from sql table to CSV file through ADF copy activity but after loading data into csv I can see leading zeros from all cell got removed automatically. CSV file created automatically through ADF pipeline so i cannot change format to text before ingest data and at both sides source and target column type as string only.

So can you suggest any possible way to load data as it is into csv from table(including leading 0 as well)?

Solution: I found root cause of this issue. attached screenshot is for notification when I open csv and I clicked on convert and don't show option that's why it was converting into numeric value.Screenshot 2024-01-12 130619.png

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Subashri Vasudevan 11,226

Hi,

Make sure you are able to see the leading zeros, in source data set data preview option.

If source data is interpreted correctly in source data set, In your CSV sink data set setting (open the data set and find the setting) mention quote character as double quotes and try. This will maintain the data as such including leading zeros.

Please try and let us know.

Thanks

Share via

Leading zeros issue in csv

1 additional answer

Your answer