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
Solved! Go to 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.
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.
yes everything is correct, datatable has correct data only changing while writing
Try putting a single-quote ' in the input before your data in the Write input.
i have thousand record, loop can't apply for that
What...?
How does your Write to Excel action look?
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
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'
Yes, and that SQL statement produces a datatable, which I would like you to loop through and modify:
actually i have thousands rows, so loop is not possible with huge records
What do you mean it is not possible? Do you get an error?
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.
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.
Hi @Pavel_NaNoi
thankyou so much for your reply, it works for me without any loop.
User | Count |
---|---|
13 | |
8 | |
6 | |
6 | |
5 |
User | Count |
---|---|
20 | |
17 | |
17 | |
12 | |
11 |