cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heldermavrolert
New Member

Power Automate Crashes when reading CSV file

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.

1 ACCEPTED SOLUTION

Accepted Solutions
momlo
Impactful Individual
Impactful Individual

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.

 

 

 

momlo_0-1669543553613.png

 

 

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:

momlo_2-1669543834325.png

 

Result:

momlo_1-1669543771342.png

momlo_3-1669543900612.png

 

//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:

 

momlo_4-1669544223082.png

 

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:

 

 

momlo_5-1669544378921.png

 

momlo_6-1669544412518.png

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

 

momlo_9-1669545028221.png

 

 

View solution in original post

8 REPLIES 8
Phil_R
Regular Visitor

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.

Phil_R_0-1669437533587.png

 

MichaelBoruta
Resolver II
Resolver II

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.

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

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.

momlo
Impactful Individual
Impactful Individual

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.

 

 

 

momlo_0-1669543553613.png

 

 

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:

momlo_2-1669543834325.png

 

Result:

momlo_1-1669543771342.png

momlo_3-1669543900612.png

 

//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:

 

momlo_4-1669544223082.png

 

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:

 

 

momlo_5-1669544378921.png

 

momlo_6-1669544412518.png

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

 

momlo_9-1669545028221.png

 

 

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.

Jorge0
New Member

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.

momlo
Impactful Individual
Impactful Individual

Hi @Jorge0 

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?

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.

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 (3,679)