csv opened with PAD looks different

[MarcoC8]

Hi

Thanks to the instructions in this topic I've managed to launch the last downloaded .csv-file in Excel. 

However, when the file is opened, tabs are suddenly semicolons and comma's suddenly tabs. As a result, my data looks like this: 

Whereas it should look like this (which is the case if I open the file manually): 

Is there any way to make sure it's shown in the same way when opening it with Power Automate? 

Thanks. 

[Highboy]

If I remember correctly it's because Excel uses the list seperator defined in Windows.
Take a look her on how to change it. https://www.ablebits.com/office-addins-blog/change-excel-csv-delimiter/#default

[MarcoC8]

Thanks for the suggestion. I set the list separator to ";" (instead of ",") but unfortunately that didn't work. 

[MichaelAnnis]

If your settings are not accepting the ; list separator, then this is my suggestion.

Read from csv

replace ; with | (we want to get ; to , but we need it to go somewhere else temporarily so we can move commas)

replace , with ;

replace | with ,

Write to csv file (a new file, not the old one)

Try importing this new file

Good luck

[MarcoC8]

Hi @MichaelAnnis , thank you for your suggestion, but I'm still not getting the expected result. However, your suggestion might take me to a solution in time, but then I'd have to modify the rest of the flow and I don't have enough time for that now, so I'm pausing my automation attempt. 

Maybe I'm being unrealistic, but I think it shouldn't make a difference if a file is opened with Power Automate or by me. I expect the same end result, but apparently that's not the case.

To both of you @Highboy and @MichaelAnnis, thank you very much for your support! 

[MichaelAnnis]

Here's a workaround.  Create a macro workbook (just have to save as .xlsm instead of .xlsx).  Write this macro (thx GPT for creating it.)  In the macro workbook, have A1 say: "FilePath:" and have B1 say: "FileName:".  Then create the following macro:

Sub OpenFile()

' Declare variables
Dim filePath As String
Dim fileName As String

' Set variables
filePath = ThisWorkbook.ActiveSheet.Range("A2").Value ' Get file path from A2
fileName = ThisWorkbook.ActiveSheet.Range("B2").Value ' Get file name from B2

' Combine the file path and file name
Dim fullPath As String
fullPath = filePath & fileName

' Open the file
Workbooks.Open (fullPath)

End Sub

The bot will open the macro workbook

Have the bot write %Files[0].Directory% to A2

Have the bot write %Files[0].Name% to B2

Have the bot run the macro to open the file

This should open the file the same as you open it.

Best of luck!

[Parthiban759]

Change the delimiter from comma to semicolon in the advanced options - Read CSV file as show in the reference image.