Hi there and thank you in advance.
I am trying to copy specific columns of data that are not next to each other from an Excel file including their headers and eventually write them into a csv.
What is the best approach?
I have seen suggestions for variables, loops and lists.
You could use SQL to get your desired result.
You can use an excel spreadsheet like a Ace SQL table (Access Database)
see Run Sql Queries in Power Automate Desktop
Copy the 'code' below and paste it into power automate desktop. You will have to fix the errors that show up related to file paths.
SET Excel_File_Path TO $'''H:\\Temp\\SOAnswer.xlsx'''
SET csvFilePath TO $'''H:\\Temp\\SOAnswer.csv'''
Database.Connect ConnectionString: $'''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";''' Connection=> SQLConnection
Database.ExecuteSqlStatement.ConnectAndExecute ConnectionString: SQLConnection Statement: $'''SELECT [Value] & \', \' & [Check] & \', \' & [Additional Data] as CSV
FROM [List1$]''' Timeout: 30 Result=> QueryResult
Database.Close Connection: SQLConnection
Variables.CreateNewList List=> HeadersList
Variables.AddItemToList Item: $'''Value, Check, Additional Data''' List: HeadersList
File.WriteToCSVFile.WriteCSV VariableToWrite: HeadersList CSVFile: csvFilePath CsvFileEncoding: File.CSVEncoding.UTF8 IncludeColumnNames: False IfFileExists: File.IfFileExists.Append ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault
File.WriteToCSVFile.WriteCSV VariableToWrite: QueryResult CSVFile: csvFilePath CsvFileEncoding: File.CSVEncoding.UTF8 IncludeColumnNames: False IfFileExists: File.IfFileExists.Append ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault
# Clean up the "" at the beginning and end of each line
File.ReadTextFromFile.ReadText File: csvFilePath Encoding: File.TextFileEncoding.UTF8 Content=> csvFileContents
Text.Replace Text: csvFileContents TextToFind: $'''\"''' IsRegEx: False IgnoreCase: False ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> csvFileContents
File.WriteText File: csvFilePath TextToWrite: csvFileContents AppendNewLine: True IfFileExists: File.IfFileExists.Overwrite Encoding: File.FileEncoding.Unicode
it should end up looking something like this.
the dummy data from excel looked like this.
The result.
Keep in mind that the SQL used for this is Access SQL flavour, so you won't have all the functionality of MS SQL Server queries, but it sure beats having to run several list extractions and looping through them.
