Is it possible to write datatable columns to specific column numbers in Excel?

[chunkeymonkey88]

Hello,

I have a flow that extracts 3 datasets from 3 different Excel sheets using SQL and then uses the 'Write to Excel' function to send that to one Excel file after manipulation (attached screenshot). The first 2 datasets i am writing to Excel have the same names for Columns A, B, C and D, but the third is different so i need to write its first two datatable columns to Excel columns A & B, and its last two datatable columns to Excel columns E & F - is this possible in PAD?

I tried to call out the columns in the Write to Excel function (i.e. Column: A, B, E, F) but it writes to columns A, B, C, D

Thanks in advance for any help you can offer

[Deenuji]

@chunkeymonkey88 

I'm a bit uncertain about the exact requirement. Could you please provide clarification with some examples and, if possible, a screenshot of the flow? This would help me better understand the context. Thank you.

[eetuRobo · Accepted solution]

Try using 'Retrieve data table column into list' -action and storing each data table columns into a list. 

Then use 'Write to Excel worksheet' -action to write each list into each column you want them to.

So you take each column into a list and write each column individually to the excel columns you want them to.

[VishnuReddy1997]

Hi @chunkeymonkey88 ,

Please find the below solution.

Code: 

SET InputFolderPath TO $'''C:\\Users\\Pictures\\Practice\\Inputs\\'''
# Storing Input Files in a Folder. Using Get Files from folder retriving all the files
Folder.GetFiles Folder: InputFolderPath FileFilter: $'''*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> FilesInFolder
# Launching Excel to write the output data
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Pictures\\Practice\\Output.xlsx''' Visible: True ReadOnly: False Instance=> OutputExcelInstance
# Storing Column names in data table
Variables.CreateNewDatatable InputTable: { ^['Column1', 'Column2', 'Column3', 'Column4', 'Column5', 'Column6'], [$'''Plan ID''', $'''Engine''', $'''Engine Type''', $'''Operator''', $'''Shop Visit Type''', $'''Cost'''] } DataTable=> HeadersDataTable
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: OutputExcelInstance Name: $'''Sheet1'''
# Writing Headers to the Excel
Excel.WriteToExcel.WriteCell Instance: OutputExcelInstance Value: HeadersDataTable Column: $'''A''' Row: 1
Excel.ReadFromExcel.ReadAllCells Instance: OutputExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> OutputExcelData
LOOP FOREACH CurrentItemInputExcelFile IN FilesInFolder
    Database.Connect ConnectionString: $'''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%CurrentItemInputExcelFile%;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";''' Connection=> SQLConnection
    @@copilotGeneratedAction: 'False'
Database.ExecuteSqlStatement.Execute Connection: SQLConnection Statement: $'''SELECT *FROM [Sheet1$]''' Timeout: 30 Result=> ExcelQueryResult
    Database.Close Connection: SQLConnection
    SET RowCount TO 0
    LOOP FOREACH CurrentItem_Columns IN ExcelQueryResult
        Excel.GetFirstFreeColumnRow Instance: OutputExcelInstance FirstFreeColumn=> OutputExcelFirstFreeColumn FirstFreeRow=> OutputExcelFirstFreeRow
        IF CurrentItem_Columns.ColumnNames[0] = OutputExcelData.ColumnHeadersRow[0] THEN
            Excel.WriteToExcel.WriteCell Instance: OutputExcelInstance Value: ExcelQueryResult[RowCount][0] Column: $'''A''' Row: OutputExcelFirstFreeRow
        END
        IF CurrentItem_Columns.ColumnNames[1] = OutputExcelData.ColumnHeadersRow[1] THEN
            Excel.WriteToExcel.WriteCell Instance: OutputExcelInstance Value: ExcelQueryResult[RowCount][1] Column: $'''B''' Row: OutputExcelFirstFreeRow
        END
        IF CurrentItem_Columns.ColumnNames[2] = OutputExcelData.ColumnHeadersRow[2] THEN
            Excel.WriteToExcel.WriteCell Instance: OutputExcelInstance Value: ExcelQueryResult[RowCount][2] Column: $'''C''' Row: OutputExcelFirstFreeRow
        END
        IF CurrentItem_Columns.ColumnNames[3] = OutputExcelData.ColumnHeadersRow[3] THEN
            Excel.WriteToExcel.WriteCell Instance: OutputExcelInstance Value: ExcelQueryResult[RowCount][3] Column: $'''D''' Row: OutputExcelFirstFreeRow
        END
        IF CurrentItem_Columns.ColumnNames[2] = OutputExcelData.ColumnHeadersRow[4] THEN
            Excel.WriteToExcel.WriteCell Instance: OutputExcelInstance Value: ExcelQueryResult[RowCount][2] Column: $'''E''' Row: OutputExcelFirstFreeRow
        END
        IF CurrentItem_Columns.ColumnNames[3] = OutputExcelData.ColumnHeadersRow[5] THEN
            Excel.WriteToExcel.WriteCell Instance: OutputExcelInstance Value: ExcelQueryResult[RowCount][3] Column: $'''F''' Row: OutputExcelFirstFreeRow
        END
        Variables.IncreaseVariable Value: RowCount IncrementValue: 1
    END
END
Excel.CloseExcel.CloseAndSave Instance: OutputExcelInstance

has context menu

[chunkeymonkey88]

Hi @eetuRobo many thanks for this great solution, simple to implement and worked like a charm!

[chunkeymonkey88]

Hi @VishnuReddy1997 although i accepted another solution offered by @eetuRobo i also wanted to say a massive thank you for your offer to help and a really detailed explanation of how to accomplish it