Convert CSV Files to Excel (xlsx format) in Power Automate

[@PowerGI]

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]----------------------------------------------

[@AaronPaul_google]

This is fantastic. I already have a CSV file in Sharepoint and looking to convert to excel. Could you guide ?

[@PowerGI]

@@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!

[@user-wo3mx2ie1f]

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" ?

[@gabriel29music]

@Henry Dominguez you solved the problem?

[@VedamurthyH]

same error im geting

[@davidesartori4354]

thanks for sharing this solution! this is an example of how far power automate is from making 'advanced' tasks user friendly. assuming that this is one of the best (if not the best) approaches to accomplish this type of file conversions, it's unbelievable how many steps, lines of code, interactions between files/flows have to be used to accomplish this.

[@elizabethforney6872]

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!!

[@simonec98]

Is it possible to do the same flow without reading csv from attachment but from a folder?

[@paddyfore]

Same here. I have 6 CSV files in a onedrive folder and want t ocombine in to one Excel Workbook

[@anubhav.rohatgi]

Hello PowerGI - I can't get the automation to run at 4:00 in the video because the Input field in the Linebreak Compose function is blank. What should go in that field for this to run.

[@KKlopsik]

I have the same!

[@solodev5739]

Thanks for sharing this. It’s a very efficient/creative process.

[@josefinagaccio2362]

I have an error when I run the script: Line 5: "alllines is not iterable", do you know how to solve it?

[@aadelaboobacker5505]

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!!

[@curious-homebody]

remarkable work! thanks for sharing! can't wait to have Automate in Excel enabled by my admin !

[@Kobena007]

Hello Power GI, have you tried converting xlsb files to xlsx file or table?

[@2007pradipta]

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

[@sanchika-t3h]

@PowerGI .. Can we do the same for XLS to XLSX conversion?

[@conflicts21]

Hi. May I know if this method will work to convert XLS and XLSX format?

[@daudi4793]

Hey Power GI. This one is an old video. If I have got columns from A : CG. how can I go about it and how can I format the dates in correct order? When converting from CSV to Excel, the dates need proper formatting. At the moment the dates are like 11/07/2024 (in real they are 07/11/2024).

[@guyboswell2864]

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?

[@FrostiesDoodles]

Same, did you ever fix it?

[@rodrigonkws]

Thanks a million, it helped me a lot!!! Awesome!!

[@hemarajani2711]

Can we do this same process for xlsb files?

[@gregoirepesenti]

hi, my csv is on one column, with delimiter, can we do the same ? thanks

[@zitta109]

How about conversion of XLS to XLSX ?

[@erinann7833]

What if you have dynamic columns. I have a file that can come with 10 columns or max 50 columns. Is there a way to do columns dynamically as well?

[@gauravyy]

ExcelScript namespace not found is what i am getting in scriptlab please advise.

[@mariaelenadiazvelasquez3080]

Great content!

[@sanjuchowdary9553]

Can you help if we want to insert the columns as the dynamic

[@PowerGI]

Hello! Have a look at this video I just uploaded!! kzbin.info/www/bejne/h3m8XnanisqniJo

[@techguru2707]

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!!

[@diegoinfante63]

Estoy muy agradecido, muy buena solución.

[@PowerGI]

Gracias Diego!

[@GeeBeisH]

Any way this can be done without scripts? My client doesn't have excel scripts with their E1 license...

[@ProrokMeow]

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?

[@erinann7833]

i am wondering the same thing

[@LucasDoseck]

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

[@fabianguaduas4409]

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.

[@95roshak95]

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?

[@dougdoomsday]

I've got the exact same issue. Any idea how to get rid of those quotes?

[@BrandoMoto]

I am getting an error when leaving LineBreak empty saying that it needs an input

[@akyamm]

You have to add 'enter' to inputs

[@memez2300]

Absolutely genius!

[@ericfranco4877]

I keep getting a " we were unable to run the script. Please try again. WorkBook not found" HELP please

[@sailearningworld2280]

Will this process need a license or a dedicated machine? to run everytime

[@PowerGI]

Thank you for your comment. No, all you need is at least an E3 365 license for office. You don't need dedicated machine

[@jessicaroche9197]

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.

[@chriscooper8642]

My CSV does not have header columns. how would I process this without headers? the first row ends up being the header right now.

[@chriscooper8642]

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");

[@PowerGI]

hi Chris, sorry for the delayed reply. Thank you for posting back the solution, hopefully will be useful for somebody else !

[@aaronczupryn7368]

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?

[@BiharKiShaan]

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!

[@PowerGI]

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 [e-mail] for further questions!

[@BiharKiShaan]

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?

[@MrSumittiwary]

@@PowerGI Where to use this function in the flow? Power automate or in script

[@masarweh]

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!

[@recliner_36]

Awesome!! Ttthhhhaaannnnnkkkk you so mmmmuuuuuuccccchhhh!!

[@nelgarmartinez5979]

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?

[@PowerGI]

Hi Nelgar! Thank you for your comment, this video shows something similar: kzbin.info/www/bejne/d17XpX6mg61ojNk 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

[@Manojkumar27]

Hi, how to convert Xlsb to Xlsx?

[@pfrmota]

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?

[@PowerGI]

hi Paulo! Does that column come with a " separator to indicate that there is a comma inside it?

[@pfrmota]

@@PowerGI , sort of. I receive the report from a system on a daily basis. The financial columns are shown like ,“/$3,000.00“/,

[@PowerGI]

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?

[@pfrmota]

@@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.

[@PowerGI]

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?

[@dineshsuranga4290]

Thank you

[@michalmihalec7790]

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!

[@PowerGI]

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");

[@michalmihalec7790]

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?

[@michalmihalec7790]

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').

[@mohammedtahakhan3455]

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"

[@akyamm]

You have to remove table from worksheet

[@yilu3219]

how to get automate excel add-in ?

[@PowerGI]

Please look at this: www.thespreadsheetguru.com/blog/enable-office-scripts-excel-online

[@MinimalWhale]

Great great video.

[@Naveenkumar-db4pj]

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