Keep the cell format from datatable to csv

Help Studio
, ,
1

Hello,

Once again, I need help.
I have a datatable with columns
date | Society | Site | Article | Price
I need to keep the format of cells specially the SITE columns which have a format with 4 digits.
example
SITE
0107
0909
3456
etc

When I write my DataTable into a CSV file whit the Write CSV activity, the output of this CSV file becomes
SITE
107
109
etc
But I need to keep the leading zeros because the site has to have 4 digits.

Can anyone know why it is like this ?

Do I have, maybe, to Write Excel file then convert to CSV to keep the format ?
Any idea will be appreciated
thank you

@ppr

2

How are you checking the result csv to make sure it is losing the trailing zeroes? i hope it is in notepad, cause if you open in excel, then this problem is only visual…

2 Likes
3

Not in Notepad.

I see that the zeros are missing because I open the CSV file with Excel.
And Also when I’m charging the datas into SAP, it refuses my transaction because the format is not 4digits.

4

So you are reading from a CSV originally? Or how are you creating this datatable?

I would try using a write line activity to verify that the row/column in question is in the format you expect originally. If it isn’t, then you’ll need to do some datatable maintenance to get it in the correct format. I would recommend ensuring that the type is ‘string’ for this column/field because if it is integer or other numeric type, then all leading zeros are going to be removed. You can convert from integer to 4-digit string by creating a new column in the datatable, copying over from oldColumn to newColumn using .ToString(“0000”) to indicate you want the string to be 4 digits long. Then delete the old column, rename the new column to be the same as the old column, and set the ordinal of the new column to be the same as the old column.

It sounds complicated, but takes less than a second for the robot to do, and is only ~4-8 activities total in your program

3 Likes
5

so all is good, when you write into sap use values like this:
row(0).ToString().PadLeft(4, "0")

2 Likes
6

Thank you I’m trying this, but i’m not sure how to insert this to my process.
Do I have to use an assign activity, and format all the SITE column like this ?

Can you give me details please (sorry, i’m beginner).

Thank you @bcorrea

7

See an example, i want to write one of those numbers to a text box in a SAP form, i will do like this:

varNumber = 101
varTextToWrite = varNumber.ToString().PadLeft(4, "0")

Result in the form will be: “0101”, as that function will add zeroes to the left of the number until they will have 4 digits.

8

It’s not working coz I need to charge the csv file in SAP. And I don’t know how to convert the data inside the column “Site” from 3 to 4 digits.
I change the typeargument from the column from object to string. But I have always an error message saying that it cannot convert string to integer or to Char.

9

I followed what you suggested me.
It works when I use the output datatable, it shows that the SITE columns is now with 4 digits.
When I write it to csv and I open my csv file with excel, it is still with 3 digits. BUT when I open the csv file with Notepad, there are 4 digits ! I don’t understand why.
Because then, I need to charge the csv file into SAP. So the problem remains the same.
Thank you for your help

10

If it has 4 digits in notepad then you will be able to send it to SAP. It just shows 3 in excel due to excel changing the format, the actual CSV itself is accurate

1 Like
11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.