Unanswered

csv opened with PAD looks different

(0) Share

Report

Posted on by MarcoC8

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.

Categories:

Power Automate Desktop

All responses (6)

Answers (0)

Sort by

Highboy 1,187 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

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 15 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Like
a
(0)

Report
Copy link

Link copied!

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 15 on at

Like
a
(0)

Report
Copy link

Link copied!

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 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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 56 on at

Like
a
(0)

Report
Copy link

Link copied!

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