Unanswered

Power Automate Crashes when reading CSV file

(0) Share

Report

Posted on by heldermavrolert

HI,

My Power Automate keeps crashing when I'm trying to read a CSV file so that I can convert it into .xlsx, it happens when Power Automate is reading the CSV file delimited by ; and it suddenly find a delimiter like this -> ;"

_______________________________________________________________ERROR MESSAGE___________________________________________________

Microsoft.Flow.RPA.Desktop.Modules.SDK.ActionException: Failed to import. Error parsing ---> Microsoft.VisualBasic.FileIO.MalformedLineException: Line 11486 cannot be parsed using the current Delimiters.
in Microsoft.VisualBasic.FileIO.TextFieldParser.ParseDelimitedLine()
in Microsoft.VisualBasic.FileIO.TextFieldParser.ReadFields()
in Microsoft.Flow.RPA.Desktop.Modules.File.Actions.Utilities.ReadFieldsEnhanced(TextFieldParser parser)
in Microsoft.Flow.RPA.Desktop.Modules.File.Actions.ReadFromCSVFileAction.Execute(ActionContext context)
--- End of internal exception stack tracing ---
in Microsoft.Flow.RPA.Desktop.Modules.File.Actions.ReadFromCSVFileAction.Execute(ActionContext context)
in Microsoft.Flow.RPA.Desktop.Robin.Engine.Execution.ActionRunner.Run(IActionStatement statement, Dictionary`2 inputArguments, Dictionary`2 outputArguments)

4977766790215;DCPL3550CDWYY;BRODCPL3550CDWYY;BROTHER MULTIF LASER COLOR A4 DCP-L3550CDW;BROTHER;Color Laser Multifunction;24;0;;27,400;523;589;568;BRODCPL3550CDWYY;https://www.brother. en/multifunction/printers-multifunction-laser-color/dcp-l3550cdw;"Multifunction WiFi LED color laser printer with 50-sheet document feeder and automatic two-sided printingPrinter | Copier | ScannerTray 250 sheets Speed 18 ppm Mobile and Cloud Security "SUBST DCP-9020CDW

____________________________________________________________________________________________________________________________________

Any suggestion on how to fix this issue?

Thanks in advance.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (8)

Answers (1)

Phil_R 4 on at

Like (0)

Report

Have you tried to Launch Excel with the Full filename and path to the CSV file as the Document Path. I had some problems with special encoded CSV files and this has worked well.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

In CSV, it is possible to explicitly define separator instead of letting app guess. Just add
sep=;
As your first line in the CSV file. Sometimes it helps to overcome parsing errors.

Was this reply helpful? Yes No
heldermavrolert 9 on at

Like (0)

Report

I did specify in the configuration to read CSV, the specific delimiter -> ;

Was this reply helpful? Yes No
heldermavrolert 9 on at

Like (0)

Report

But even if I can open it using "Launch Excel" how do I separate with the delimiter? It just opened the file and didn't separate the data.

Was this reply helpful? Yes No
Verified answer

momlo 1,527 Super User 2024 Season 1 on at

Like (1)

Report
Hi @heldermavrolert

By default, Excel uses an OS list separator to separate data, so this means you have a different separator set up in your OS regional setting. You would need to change that to ";" but this will affect all applications using this setting. You might check if Excel has the list separator overwrite function in the setting (i don't remember from the top of my head), but, again, this will affect all excel files.

But even if I can open it using "Launch Excel" how do I separate with the delimiter? It just opened the file and didn't separate the data.

Coming back to the original issue, I understood that you have a csv file with, let's say, 20,000 rows.
Read from CSV reads 11485 lines correctly and fails on line 11486 (according to your error log).
So if we assume this is PAD/VB module bug, my questions would be:
Is line 11486 a malformated, bug, or valid line? Why is there an additional character " in that line?
Do we need this " in the text that we load? Can we replace this " with empty string %''%?
If we can replace with empty, can we replace all " found in that csv with empty?
If we can:
Read text from file (not csv) into variable
Replace text " with %''%
Write text to csv
Read text from csv
But, I on my and I have tested it on my machine and latest PAD and had no issues:

CSV file:

Result:

//EDIT
What is happening: PAD treats " as a special character and expect matching closign "

When you have two matching " it assumes anything between is a text and treats it as a one string:

So what is most likely happening with in your case, you have a single " and PAD treats that as a beginning of a string, and expects to find closing " that never appears.
I am able to reproduce the issue with this file:

//Edit2

How to overcome the issue by removing " from the content of the file.
Another approach would be to use Excel as proposed by @Phil_R , Powershell, or another script to load data into variables in a different way to PAD's default VB module.

Was this reply helpful? Yes No
heldermavrolert 9 on at

Like (0)

Report

Thanks for your help,

I should have specified that there was a single " when the error was occurring, sorry to have some of you wasting your time when testing this problem.

Your solution fixed it, my OS indeed had a different separator and separated correctly when opening the .csv file, but the most useful solution is reading and writing the .csv as text and replacing the " into an empty string %''% instead of relying on my OS delimiter for this and maybe other files that might have the same issue.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

I tried reading the CSV file and replacing its " with %''%, but it just didn't want to work. I was most probably missing something. Ultimately, I didn't need to iterate with it so I just went full ape and used the open application function. The simplest option as excel could actually open it no problem if it was "manual".
Access route: Excel.exe
Argument: Your file route
After app start: Wait to Complete
Wait: Minimum 1 second. Test it

Then I used the associate with excel function to get its instance followed by an excel copy function and then an excel paste applied to a second excel instance that I open after closing the first instance.

It works but if you need to iterate I wouldn't recommend at all as It gets progressively worse because you have to wait for the process each time.

Was this reply helpful? Yes No
momlo 1,527 Super User 2024 Season 1 on at

Like (0)

Report

Hi @Anonymous
I just had a project where i was importing x number of csv files - reading text from file (not from csv), replacing the " with %''% worked fine, so perhaps yet another special character in yours?

Was this reply helpful? Yes No