When using a Copy Activity the output seems to take the leading zero off - Please help

Mike Kiser 1,531

Hi @Nasreen Akter @MartinJaffer-MSFT @HimanshuSinha-msft
In a COPY Activity I am reading a JSON file which has as the first item is employeeid such as 010004.
Most of the employee id's begin with a ZERO. My issue is that when the Copy Sink is an Excel spreadsheet and the employee is copied without the leading zero, such as 10004. I think Copy this Spreadsheet into a DB Table. In the DB Table the employee id has NO leading zero.

How can I get the leading zero back into the COPY Output so that the employee id is 010004 and the ERP will recognize it?

Thanks!!
Mike

1 answer

Nasreen Akter 10,746 Reputation points

2021-03-17T00:33:30.997+00:00

Hi @Mike Kiser ,

Below link might help in this scenario:
https://provider.www.upenn.edu/computing/da/bo/webi/qna/iv_csvLeadingZeros.html

Thanks!
Please sign in to rate this answer.
Mike Kiser 1,531 Reputation points

2021-03-17T01:52:06.583+00:00

Thanks @Nasreen Akter , this Integration in this pipeline is fully automated; in that way, I need to add the leading zeros inside the pipeline some way. @markus.bohland@hotmail.de ? what would you suggest?

Thanks so much!!
Mike Kiser

Nasreen Akter 10,746 Reputation points

2021-03-17T12:56:09.443+00:00

@Mike Kiser , in your pipeline, if you have CopyActivity to read JSON file and sink it into a CSV (in the CopyActivity--> Mapping--> Type on target side), you can select String Type which will keep the leading zero in the CSV file (you can see the leading zero by opening the csv file in a txt editor not in excel). Now if you read the csv file in any pipeline activity, the value should get with the leading 0. Please see the screenshots below. Thanks! :)

Saurabh Sharma 23,676 Reputation points Microsoft Employee

2021-03-25T14:11:29.37+00:00

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

Saurabh Sharma 23,676 Reputation points Microsoft Employee

2021-03-29T17:29:07.41+00:00

@Mike Kiser I have not heard back from you. Did @Nasreen Akter 's response resolved your issue? If so, please mark as accepted answer. If not, please let me know how I may better assist.
Sign in to comment