cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
neerajpokh
Helper I
Helper I

Facing issue while writing data from data table to Excel

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 

neerajpokh_0-1658428801795.png

and using sql script to read data

neerajpokh_1-1658429014280.png

 

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

neerajpokh_2-1658429128603.png

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Pavel_NaNoi
Continued Contributor
Continued Contributor

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:

Pavel_NaNoi_0-1658499103979.png

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.

 

View solution in original post

15 REPLIES 15
ryule
Post Prodigy
Post Prodigy

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

Henrik_M
Super User
Super User

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

neerajpokh
Helper I
Helper I

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

Henrik_M
Super User
Super User

What...?

How does your Write to Excel action look?

neerajpokh_0-1658476376310.png

 

Henrik_M
Super User
Super User

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'

Henrik_M
Super User
Super User

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

Henrik_M_0-1658480183770.png

 

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

Henrik_M
Super User
Super User

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

ryule
Post Prodigy
Post Prodigy

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
Continued Contributor
Continued Contributor

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:

Pavel_NaNoi_0-1658499103979.png

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
Responsive Resident
Responsive Resident

@neerajpokh 

 

Use predefined excel template and format the column as text.

Hi @Pavel_NaNoi 

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

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,426)