Convert CSV Files to Excel (xlsx format) in Power Automate
Vložit
- čas přidán 29. 11. 2021
- Learn how to leverage Power Automate's out of the box actions & Office Scripts to convert CSV documents into Excel (xlsx).
Check out this video ( • Read Raw Excel Data (u... ) if your source file is in XLSX format instead of CSV.
Code for the Office Script:
[starts]----------------------------------------------
function main(workbook: ExcelScript.Workbook, lineCSV: string[]) {
let selectedSheet = workbook.getActiveWorksheet();
const alllines = lineCSV;
let counter = 1;
for (let line of alllines) {
if (line.includes(",")) {
let items = line.split(",");
selectedSheet.getRange("A" + counter+":G"+counter).setValues([[items[0], items[1], items[2], items[3], items[4], items[5], items[6]]]);
counter++;
}
}
workbook.addTable(selectedSheet.getRange("A1:G" + counter), true).setName("ContactInfo");
}
[ends]----------------------------------------------
Follow the steps in this link if you don't see the "Automate" tab:
docs.microsoft.com/en-us/micr....
Please note that Office scripts will time out if you have more than 1000 rows to insert.
Questions? We can help! Contact us a graciela@powergi.net - Věda a technologie
Code for the Office Script:
[starts]----------------------------------------------
function main(workbook: ExcelScript.Workbook, lineCSV: string[]) {
let selectedSheet = workbook.getActiveWorksheet();
const alllines = lineCSV;
let counter = 1;
for (let line of alllines) {
if (line.includes(",")) {
let items = line.split(",");
selectedSheet.getRange("A" + counter+":G"+counter).setValues([[items[0], items[1], items[2], items[3], items[4], items[5], items[6]]]);
counter++;
}
}
workbook.addTable(selectedSheet.getRange("A1:G" + counter), true).setName("ContactInfo");
}
[ends]----------------------------------------------
This is fantastic. I already have a CSV file in Sharepoint and looking to convert to excel. Could you guide ?
@@AaronPaul_google Hi Aaron, thank you for your comment. What will be the trigger for this flow? When a new file is added to Sharepoint site?
Regardless of the answer, For SharePoint files, use the card "get file content" and select the file from the selector. Then that will give you back the text content of the file and you can go straight to the split part.
If your trigger is when a file is selected, use the ID that comes from the trigger to dynamically select files.
Hope this helps!
This is great and I would love to use it but the script gives me an error. Any idea how to solve "Line 7: alllines is not iterable" ?
@Henry Dominguez you solved the problem?
same error im geting
This works great for a large csv file and is much more efficient than using update a row in a table or add a row into a table. Thank you!!
Thanks for sharing this. It’s a very efficient/creative process.
Thankyou so much for this idea, it works A lot faster than add row into a table in Power Automate.
Even though we can use concurrent control and speed up the row addition, it constantly bumps into retries (maybe because two parallel actions are trying to input data into same row in the table)
Appreciate your help!!
remarkable work! thanks for sharing! can't wait to have Automate in Excel enabled by my admin !
Great video to help ..but i suspect if the input content have multiple line ( contain new line inside text of individual item). In this cae it will work ? Beacse you are splitting each line with Newline
Thanks a million, it helped me a lot!!! Awesome!!
Hi, thank you for very helpfull video!
What can I change in the office script to use it for different files?
For example 1 file contains 2 columns and 2 file have 20 collumns, how I can do only required number of columns?
Hi, This is really helpful, but the issue with me is the last column in csv file from SAP contains blank spaces it's not performing the desired action, when I remove that column manually and trigger the flow it's working perfectly.
Any solution to ignore last column or space from CSV file? Thank you in advance!!
Great content!
Absolutely genius!
Thank you so much, this was super helpful!!! however for some reason my characters have quotation marks at the beginning and end of every cell, like they were a part of the string. Would you know how to remove them?
I've got the exact same issue. Any idea how to get rid of those quotes?
Hi. May I know if this method will work to convert XLS and XLSX format?
This process works well for me with one exception. I have one column that the data as a comma and it throws the rest of the cell off because of it. There are double quotes surrounding the info that contain the additional comma. How do I do an additional split for just the one column, it is always in the same position but i have no other delimitator. Its basically a location "city, state" value.
Hi, one of my columns in my CSV file has GPS location(header name is "LAT/LONG", problem is the data in that column comes with a comma, which means the split will push the data to the next column. to fix this I was going to replace the header to become "LAT,LONG" so that the header will be pushed as well & also add an extra column. however I am unable to use the replace function.
my current expression is:
split(base64ToString( outputs ('Get_file_content_using_path')?['body']['$content']),outputs('LineBreak'))
I tried to use:
split(replace(base64ToString( outputs ('Get_file_content_using_path')?['body']['$content'])outputs('LineBreak'), 'LAT/LONG', 'LAT,LONG') but it did not work.
any chance you can help please?
Thank you in advance!
Hi, I did but in my case, the script is creating 2 files. One with the correct information, but the second one use a kind of symbols. Do you know what the possible reason is ? the only way to work successfully is add the UTCnow to the name.
I have an error when I run the script: Line 5: "alllines is not iterable", do you know how to solve it?
Great great video.
Hi, after converting csv to excel I need to append the converted excel to a master excel through power automate. But while appending each time it leave a blank row for every run and the coloumn no.7 is always empty. Is there anything need to change in code. Please let me know asap. Thanks
Any way this can be done without scripts? My client doesn't have excel scripts with their E1 license...
Estoy muy agradecido, muy buena solución.
Gracias Diego!
ExcelScript namespace not found is what i am getting in scriptlab please advise.
Thank you
Can we do this same process for xlsb files?
Hi, how would you update your script to format a specific column? (something like .setNumberFormatLocal("dd/mm/yyyy)"). In my CSV I have a column with dates, all formatted as dates, but in the final XLSX file I get a mix: some of the cells in the column are formatted correctly as a "DATE", but others have just "GENERAL" formatting which behave like "TEXT". (In your case could pop up a similar problem with the currency column...). Thank you very much!
Hi Michal! You can record the action of formatting the column. Then just copy the code and add it at the end of the script. Below is an example how it looks when you record it, it's for column E and you can change it for any other column that you need to add this format to.
selectedSheet.getRange("E:E").setNumberFormatLocal("dd/mm/yyyy");
I got there, but still facing the same problem. I observe that dates (days) starting with zero 0 are formatted correctly (eg. 09/11/2022), but dates with number 1 or 2 are still behaving as a text (eg. 13/11/2022 or 23/11/2022). I have also tried to format the column before the creation of the table, but not helping. It would help if I can record an action like "divide text to the columns" which would imitate the action of clicking the key "enter" for each cell, but this one cannot be recorded. Any idea?
Hi, it seems we found the solution: the original CSV file column had both date and time information. Original CSV column was formated as DATE only and probably this was causing the error. We formated the date column in CSV as GENERAL, then I got all date info in the column with the same format (not half as TEXT and the other half as DATE). Next step in Power automate was to extract and format the date: List rows present in table was giving me result in format "44776.6343171296" which is "03/Aug/2022 15:13" so to get just the date 03/08/2022 I used: addDays('1899-12-30',int(substring(item()?['Date_column'], 0, 5)), 'dd/MM/yyyy').
This has worked for me for over a year and last week something changed and now my first row shows the header column a and then the first column of the second row in column b
Did anyone get a 504 BadGateway error? I am getting one, and the research I have done says it could be that the data set is too large. How big of a file can this handle?
Is it possible to do the same flow without reading csv from attachment but from a folder?
Same here. I have 6 CSV files in a onedrive folder and want t ocombine in to one Excel Workbook
Awesome content. Very useful. Quick question: My flows ran successfully for working on 3000 rows in 23 second, but throw error as “Bad Gateway” after 3000. Can you please help how can I let my flow ran successfully for 6000 data. Thanks!
hi Vivek, will you always have 6000 rows or the number can vary?
The option here is using the function take() and skip() to process the rows in batches
docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#take
docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#skip.
For example if we use batches of 1000, something like below would work
take(skip(outputs('Compose'),mul({counter},1000)),1000)
{counter} would be a control variable that goes up by 1 when you excecute each batch. hope this helps!
you can email me at graciela@powergi.net for further questions!
Thanks for you reply. Not always 6000 but can vary. Maybe sometime more than 6k or sometimes less than 6k.
Can you help me where I should use this technique and how ? Shall i need to do something in excel or in power automate?
@@PowerGI Where to use this function in the flow? Power automate or in script
Hi, great content! but I'm trying to do the opposite, convert an Excel xls file from email to CSV format and then save it on a SharePoint folder. Is it possible?
Hi Nelgar! Thank you for your comment, this video shows something similar:
czcams.com/video/A-ttKpNH5Vw/video.html
Convert xlsx file to SharePoint list.
The first part of the video will be the same, converting to CSV you would need to use the option Create CSV table in Power Automate.
Let me know if this helps
Hmmm!?? Seems like it should work. The header row creates OK except first column gets a leading [" and last column gets a trailing
" and I don't get any data rows added. No error message. Any ideas?
Same, did you ever fix it?
I keep getting a " we were unable to run the script. Please try again. WorkBook not found" HELP please
Hi, how to convert Xlsb to Xlsx?
Will this process need a license or a dedicated machine? to run everytime
Thank you for your comment.
No, all you need is at least an E3 365 license for office. You don't need dedicated machine
That's fantastic. I could make the flow run. However, I have columns with numbers separated by commas as well ($3,000.00). Is there a workaround for that case?
hi Paulo!
Does that column come with a " separator to indicate that there is a comma inside it?
@@PowerGI , sort of. I receive the report from a system on a daily basis. The financial columns are shown like ,“/$3,000.00“/,
A workaround could be splitting by “/$ delimiter to get the financial columns and then doing the split based on the actual comma. How many columns like that do you have? They always show in the report in the same order right?
@@PowerGI , 15 out of 61. Yes, they always show in the same order. I’ll have to split the code using the separators “/ instead for the 15 columns, right? I’ll give a try. If it works, I’ll share the code here. Thank you so much @Power Gi.
Your other columns don't have the " after the comma when it's split right?
Is there any way that the report has a different separator such | or ~ ?
Yes, you would need to get the value between ,“/ and /“, - but this can become quite complex since there are several columns in the file, what I usually do is evaluating the number of items the resulting array has and apply conditionals to determine if the position is a financial column or not. Something like what is shown in thsi link: sharepains.com/2020/09/08/handle-commas-csv-files-power-automate/.
Is this something that could apply to your scenario?
Can you help if we want to insert the columns as the dynamic
Hello!
Have a look at this video I just uploaded!!
czcams.com/video/QHY-CqUetR8/video.html
Hello, I get below error while executing, pls advice. "We were unable to run the script. Please try again.
Office JS error: Line 16: Workbook addTable: A table can't overlap another table.
clientRequestId: 31c40844-b69c-427d-b474-6c8a472862d5"
You have to remove table from worksheet
My CSV does not have header columns. how would I process this without headers? the first row ends up being the header right now.
I figured it out. I just had to change the true to false on this line
// Add a new table at range A1:J2 on selectedSheet
workbook.addTable(selectedSheet.getRange("A1:J" + counter), false).setName ("ContactInfo");
hi Chris, sorry for the delayed reply. Thank you for posting back the solution, hopefully will be useful for somebody else !
I am getting an error when leaving LineBreak empty saying that it needs an input
You have to add 'enter' to inputs
how to get automate excel add-in ?
Please look at this: www.thespreadsheetguru.com/blog/enable-office-scripts-excel-online