Combine multiple source files into single file

When downloading from the Source screen

You can go to the Source Format and select individual sources, then used the download button within the source. This generated a tab-deliminated text file when I did this project. The PowerShell script below combines all the TXT files into a CSV and adds a column with the text file name. It will save it in the same folder as “Combined Data.csv”

Make sure to update the $InputDirectory and the $OutputFile in the script to your local file address

Steps:

Place all text files in one folder. Ensure all the .txt files are in the same directory.
Open PowerShell as Administrator.
Run the following PowerShell script:

# Set the directory containing the text files
$InputDirectory = "C:\Path\To\TextFiles"
$OutputFile = "C:\Path\To\Output\CombinedData.csv"

# Get all text files in the directory
$TextFiles = Get-ChildItem -Path $InputDirectory -Filter "*.txt"

# Initialize an array to store data
$CombinedData = @()

# Process each text file
foreach ($File in $TextFiles) {
    # Import the text file as a tab-delimited file
    $Data = Import-Csv -Path $File.FullName -Delimiter "`t"

    # Add a new column to the data with the file name
    $Data | ForEach-Object { $_ | Add-Member -MemberType NoteProperty -Name "SourceFile" -Value $File.Name }

    # Append the data to the array
    $CombinedData += $Data
}

# Export the combined data to a CSV file
$CombinedData | Export-Csv -Path $OutputFile -NoTypeInformation

Write-Host "Combined data with SourceFile column has been saved to $OutputFile"

When pulled from the SFTP

Login to the SFTP using FileZilla and locate the files you need (somewhere in incoming/processed). Highlight the files you need and use FileZilla to download to your local machine.

Slate only retains the last 30 days of files on the SFTP server.

Once you have the files saved (CSV or XLS), you can use Power Query in Excel to combine them:

Option 1: Using Power Query in Excel (Recommended for simplicity)

Open a New Workbook:
- Start a new Excel workbook where the consolidated data will be stored.
Import Data Using Power Query:
- Go to the Data tab and click on Get Data → From Folder.
- Browse to the folder containing your Excel files and click OK.
Combine and Load:
- A preview will appear showing all the files in the folder. Select Combine → Combine & Load.
- Power Query will automatically identify the sheets with matching headers and combine them into a single table.
Save and Refresh:
- Once combined, you can refresh the query if more files are added to the folder in the future.