- 1 year ago
Powershell read in csv columns from large number of files then output to new csv where each file is a new column
Comment
1 / 1
To read CSV columns from a large number of files in PowerShell and output them to a new CSV where each file is a new column, you can use the following script as a starting point:
powershell# Specify the input directory containing the CSV files
$directory = "C:\Path\to\Input\Directory"
# Specify the output CSV file path
$outputFile = "C:\Path\to\Output\File.csv"
# Get the list of CSV files in the input directory
$csvFiles = Get-ChildItem -Path $directory -Filter "*.csv" | Sort-Object
# Create an empty hashtable to store the data
$data = @{}
# Read the columns from each CSV file and store them in the hashtable
foreach ($file in $csvFiles) {
$csv = Import-Csv $file.FullName
$columns = $csv | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty Name
$data[$file.Name] = $columns
}
# Create an array of column names
$columnNames = $data.Values | ForEach-Object { $_ } | Select-Object -Unique
# Create a new CSV file and write the data
$csvData = @()
foreach ($column in $columnNames) {
$row = [ordered]@{}
$row["ColumnName"] = $column
foreach ($file in $csvFiles) {
$row[$file.Name] = $data[$file.Name] -contains $column
}
$csvData += New-Object -TypeName PSObject -Property $row
}
$csvData | Export-Csv -Path $outputFile -NoTypeInformation
Here's how this script works:
- Specify the input directory containing the CSV files (
$directory
) and the output CSV file path ($outputFile
). - Use
Get-ChildItem
to retrieve a list of CSV files in the input directory, sorted by name. - Create an empty hashtable (
$data
) to store the columns for each file. - Iterate over each CSV file and use
Import-Csv
to read its contents. - Use
Get-Member
to get the names of the columns in each CSV file and store them in the hashtable. - Create an array of unique column names from all the files.
- Create an empty array (
$csvData
) to store the data for the output CSV file. - Iterate over each column name and construct a row for each file, indicating whether the column exists in that file.
- Append each row to the
$csvData
array. - Use
Export-Csv
to write the$csvData
array to the output CSV file, without including type information.
Adjust the input directory and output file path to match your requirements, and run the script in PowerShell. It will read the columns from each CSV file and create a new CSV file where each file corresponds to a new column.