Error reading CSV File/Convert to .xlsx?

[jwlam]

Hi everyone!

I have this CSV file that isn't working out for me. It runs without errors if I save as .xlsx first but I'm hoping to use Power Automate to pull the attachments from emails and then schedule the workflow to run at 7 AM so it's ready first thing in the morning. I don't want to have the end users manually convert to .xlsx and then manually run it after.

Is there a way to convert the file within Designer and then run the workflow?

Thanks!

• CSVFile: A field was missing a closing quote in record 576: "REQUEST DATA: { (ToolId: 2)
• CSVFile: A field was missing a closing quote in record 577: ... "769d724c-4219-4b37-9992-d26c1b8e7933", (ToolId: 2)
• CSVFile: A field was missing a closing quote in record 578: ...NDATION FOR RETIREMENT INCOME PLANNING", (ToolId: 2)
• CSVFile: A field was missing a closing quote in record 579: ... "6119983d-43b8-45e9-a30b-72cea6d52c73", (ToolId: 2)
• CSVFile: A field was missing a closing quote in record 580: ... "9bda2a14-8042-403e-9f55-6e20b94f3065", (ToolId: 2)

[SPetrie]

I sounds like there may be some extra quotes inside the data itself. When you open it in excel and then save it, excel is correcting the data for you or working around it. 

Make sure you have "ignore delimiters in quotes" checked as well.

It may be best to look at the records with issues to see what could be causing the issues by importing the file with no delimiters using \0.

Once you have a workaround for that you should be good to use PA to download the files and run on demand or scheduled.

[jwlam]

It was missing a closing quote it looks like so I used the Single Quotes option and it works.

The other issue that I'm running into now is that the data isn't appearing after row 139. Do you have any ideas on why that is? There's data in that first column that I had to black out but it's just the same data from top to bottom with no null or empty cells (if that matters).

  

[SPetrie]

What does it look like when you import it with \0 for no delimiter?

Does data after row 139 show up? if so can we see what the data looks like with \0 for a few rows before and after 139? (removing anything that shouldnt be shared that is).

If you open the data in a text editor such as notepad or notepad++ what do the same rows look like then?

Are you able to sterilize a portion of the csv and let us look at a sample csv that hasnt been saved in excel? In particular, I would love to be able to see the csv containing some of the rows that are causing issues to be able to try it on my designer as well.