Facing issue while writing data from data table to Excel

[neerajpokh]

Hi,

I am facing a weird issue while using "Write to excel" activity and writing data from data table using sql script, but zeros are removing.

i have records are like - 00013 

and using sql script to read data

after that using "write to excel" activity but its not writing original data, means removing zeros 

can you please help on this urgently as stucked from last 3 days

[ryule]

Have you confirmed it's reading the data correctly? It could just be an Excel issue (cell type) where you are writing the data, if it is reading it correctly.

[neerajpokh]

yes everything is correct, datatable has correct data only changing while writing

[Henrik_M]

Try putting a single-quote ' in the input before your data in the Write input.

[neerajpokh]

i have thousand record, loop can't apply for that

[Henrik_M]

What...?

How does your Write to Excel action look?

[neerajpokh]

[Henrik_M]

Try adding the single quote to the datatable column to see if it fixes the formatting.

Method:

SET Datatable TO {['R1C1', 'R1C2'], ['R2C1', 'R2C2'] }
LOOP LoopIndex FROM 0 TO Datatable.RowsCount - 1 STEP 1
SET Datatable[LoopIndex][0] TO $'''\'%Datatable[LoopIndex][0]%'''
END

[neerajpokh]

i am using below script

SELECT [Employee ID],[Legal Name - Last Name], [Legal Name - First Name], [Company], [Benefit Group], [Benefit Plan], [Hire Date], [Date of Birth], [Age], [Coverage] FROM [Sheet1$] WHERE ([Benefit Group] <> 'Union'

[Henrik_M]

Yes, and that SQL statement produces a datatable, which I would like you to loop through and modify:

[neerajpokh]

actually i have thousands rows, so loop is not possible with huge records

[Henrik_M]

What do you mean it is not possible? Do you get an error?

[ryule]

Yeah, I agree with Henrik. Or somehow modify your extract SQL to add a leading apostrophe there.

Or, have you confirmed the Excel sheet is set to "Text"? Sometimes that helps keep leading zeros. 

[Pavel_NaNoi · Accepted solution]

Please do something similar to the following to avoid the issue of having to loop through each value to put an ' infront of every value, this should be instantaneous:

What this basically does is that it goes through a column in an excel datable, turns it into an list, then it regex replaces for every beginning of text in a list and replaces it with an ' afterwards it splits this via a new line because when you replace using regex it turns it into a string so this just puts it back into a list like it was, you can then write it and do w/e you want with it as usual.

[yoko2020]

@neerajpokh 

Use predefined excel template and format the column as text.

[neerajpokh]

Hi @Pavel_NaNoi 

thankyou so much for your reply, it works for me without any loop.