Consolidate data from many excel to 1 final excel without changing the Formatting of data

[ansh09]

I need some help on how to consolidate data from many excel (in my case currently its 3 but can be more as well), to one final excel file.

I want that all the sheets from those all individual Excel's should get copied to 1 Final Excel without the Formatting of Data gets hampered. Basically a direct copy and paste sheet by sheet.

I tried using python, but my openpyxl doesnt work as my organization IT has maybe blocked it to get package installed.

Please help me to get the data cosolidation without loosing any formatting of data , how the way it is in individual files it should same come to sheet by sheet to 1 final file.

my all files will have different column structure, there is no standard that the number of columns will be same , i just want to make them consolidated in 1 final file.

my data contains numeric values, there were '0' which i already converted to '-' and those values which were not present for the particular row column combination those cells are been greyed out.

below i am attaching screenshots for reference of my data which i generated using batch macros , currently only 1 sheet per file is there but i need that if i can get help to get a solution for input having more than 1 file and even the files are containing more then 1 sheet.

please help to get the sheets exact to the combines excel without loosing formatting and data.

[jrlindem]

A Batch-Macro is a really nice way to accomplish this.  Another option, if all the files are of the same schema, would be to bring them in using the Directory Tool, filtering down to the ones that are relevant, and then using the Dynamic Import tool taking the full file path.

Now, granted, this method doesn't help preserve formatting, but you could always write the output to a preformatted template (BLOB tool or Run-Command for template handling).  This, for some is a bit simpler to conceptualize than getting into Batch-Macros.  But macro would be a more scalable and stable solution (or required if schemas differ per file).

Hope that helps validate your approach and give you some alternative ideas, -Jay

[ansh09]

i used the batch macro to generate the formatted templates already and there is no issue in it.
just those templates (as visible in screenshots) I require to consolidate them like all files all sheets one by one in final single Excel File.
For that i am not able to work through, any help would be great as i need to combine them without affecting the formatting.

if you can give any sample workflow it would be great, i am using Alteryx Designer 2024.1 version and i need to work by preserving the formatting of data which is already generated in excel file 

the structure of my files cannot be standard as they get generated based on some other logics already built in, i just need the logic how to consolidate by just copy pasting the exact proper sheet from all the file to the one single file. .. please help!!

[jrlindem]

Can you provide a couple of the spreadsheets with fake data and then another one that shows the expected output with the formatting?  From there, I'm sure I or someone else can build out an illustrative solution for you.  -Jay

[ansh09]

Yes, surely I can, I am here uploading 2 spreadsheets(Book1 and Book2) which have the same type of dummy formatted data that i already got out via Alteryx using batch macros. They are all individual files with 1 sheet each; possibly, there can be multiple sheets too sometimes in the data.

I am also uploading the 3rd Spreadsheet (Consolidated.xlsx), which has the data from both the files and all the sheets one after another without affecting the formatting of the data. The whole sheet is copied and pasted as it is in the final file; that's the output I am looking forward to, that it takes all my files, how many of them are, and just starts reading and copying and pasting all the sheets from all the files one after another, without affecting anything. Just the whole data as it is.

Please help me get the solution for this!!

[ansh09]

Can anyone please help to get the solution for the same?
It would be a great help.

[jrlindem]

@ansh09 Thanks for your patience on this.  After reviewing your need and the thread above, it appears that you have been able to get the data into your workflow the but main issue is the retention of the formatting.

Upon further researching and experimenting on my end it doesn't appear there is a good way to handle this through Alteryx.  Since Alteryx wants to process the data and the whole files/tabs, the formatting will be forfeit.

Despite the desire to retain the formatting, if the majority of value comes from the consolidation of the files, I would consider informing your customer of this and see if they're amenable to accepting the merged document in lieu of the formatting.

Sorry I could not be of greater help.  Best of luck and happy Alteryx'ing,  -Jay

[flying008]

Hi, @ansh09 

As your case, you can use powershell code with Run Command tool to get your want.

[ansh09]

Hey @flying008 can you please help me with some idea guide for the PowerShell code or like what that code should be consisting of , which then I should go ahead and run in the Run Command tool?

[flying008]

Hi, @ansh09 

FYI.

# 1- Define variables
$AAA = 'D:\123\abc.xlsx'
$BBB = 'E:\456\eee.xlsx|F:\789\ddd.xlsx'

# Get the directory and file name of the file pointed to by $AAA
$targetFilePath = $AAA
$targetFileName = [System.IO.Path]::GetFileName($targetFilePath)
$targetFileDirectory = [System.IO.Path]::GetDirectoryName($targetFilePath)

# Construct the full save path for the new file
# The new file name will have "-Join" appended to the original file name of $AAA
$newFileNameWithoutExtension = [System.IO.Path]::GetFileNameWithoutExtension($targetFileName) + "-Join"
$newFileName = $newFileNameWithoutExtension + [System.IO.Path]::GetExtension($targetFileName)
$newSavePath = Join-Path -Path $targetFileDirectory -ChildPath $newFileName

# --- Pre-checks ---
# Check if the target file (AAA) exists
if (-not (Test-Path $targetFilePath)) {
    Write-Error "Error: The target file '$targetFilePath' does not exist. Please ensure the file path is correct."
    return
}

# Split the paths in $BBB and filter out non-existent files
$sourceFiles = $BBB -split '\|' | Where-Object { -not [string]::IsNullOrWhiteSpace($_) }
$validSourceFiles = @()
foreach ($file in $sourceFiles) {
    if (Test-Path $file) {
        $validSourceFiles += $file
    } else {
        Write-Warning "Warning: Source file '$file' does not exist and will be skipped."
    }
}

if ($validSourceFiles.Count -eq 0) {
    Write-Warning "Warning: No valid source Excel files found to copy. Operation aborted."
    return
}

# --- Excel Automation Processing ---
$excel = $null
$workbookAAA = $null
$sourceWorkbook = $null

try {
    # Create Excel application object
    Write-Host "Starting Excel application..."
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false       # Do not display the Excel window
    $excel.DisplayAlerts = $false # Turn off alert pop-ups

    # Open the target workbook (AAA)
    Write-Host "Opening target file: '$targetFilePath'..."
    $workbookAAA = $excel.Workbooks.Open($targetFilePath)

    # Iterate through each source file (split from $BBB)
    foreach ($currentSourceFile in $validSourceFiles) {
        Write-Host "Processing source file: '$currentSourceFile'..."
        # Open the source workbook
        $sourceWorkbook = $excel.Workbooks.Open($currentSourceFile)

        # Iterate through all worksheets in the source workbook and copy them to the target workbook
        foreach ($sheet in $sourceWorkbook.Sheets) {
            Write-Host "    Copying worksheet: '$($sheet.Name)'..."
            # Copy the worksheet after the last worksheet in the target workbook
            $sheet.Copy($workbookAAA.Sheets($workbookAAA.Sheets.Count))
        }

        # Close the source workbook without saving any changes
        $sourceWorkbook.Close($false)
        # Release the COM object for the source workbook to avoid memory leaks
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($sourceWorkbook) | Out-Null
        $sourceWorkbook = $null # Clear the variable for the next iteration
    }

    # Save the new file to the specified path
    Write-Host "All worksheets copied. Saving new file to: '$newSavePath'..."
    $workbookAAA.SaveAs($newSavePath)
    Write-Host "New file '$newSavePath' successfully created."

} catch {
    Write-Error "An error occurred during the operation: $($_.Exception.Message)"
} finally {
    # --- Cleanup Phase ---
    # Close the target workbook
    if ($workbookAAA -ne $null) {
        Write-Host "Closing target workbook..."
        $workbookAAA.Close($false) # Do not save changes to the original AAA file
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbookAAA) | Out-Null
        $workbookAAA = $null
    }

    # Quit the Excel application and release COM object
    if ($excel -ne $null) {
        Write-Host "Quitting Excel application..."
        $excel.Quit()
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
        $excel = $null
    }
    
    # Force garbage collection to ensure COM objects are fully released
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()

    Write-Host "Operation complete. All Excel processes have been closed."
}