powershell / merging .txt-files into csv adding date and filename

Question

it's me again, as i mentioned yesterday i'm new to Powershell (now 3 days) and i hope you can help me again.

What I want: I want to merge different txt-files into one csv-file PLUS every line which is added should start with the actual date (yyyy-mm-dd) and the filename.

Expectation_Image

WhatIamActuallyGetting_Image

So what I've got so far:

New-Item Shoppinglist_$(get-date -f yyyyMMdd_hhmm).csv -ItemType File
$txtFiles = Get-ChildItem -Name *.txt

$desiredColumns = 'Date','Filename','Substance','Information','Comment'

ForEach ($file in $txtFiles) {
$csv = Import-Csv -path $file -Delimiter "`t"
$outcsv=$csv | Select-Object $desiredColumns

#I Think the mistake is somewhere here, but i habe no idea to fix it. :(
Select-Object *, @{Name = 'Date'; Expression = {(Get-Date -format s)}}
Select-Object *, @{Name = 'Filename'; Expression = {(GetFileName)}}


$outcsv | Export-Csv Shoppinglist_$(get-date -f yyyyMMdd_hhmm).csv -NoTypeInformation -Delimiter ";" -Append 
}

I hope there is someone outside in the world who can help me. :)

Two things. One: You're showing us what you want, but not what you're actually getting. That would be helpful. Two: GetFileName is a .NET method, not a Powershell CmdLet so I'm pretty sure you can't use it the way you seem to be doing in the second Select-Object statement. — Laage, Jul 24 '20 at 09:58
Oh, i didn't know about the 'what i'm actually getting'-thing, i'll edit it in a couple of seconds. :) — TAL, Jul 27 '20 at 06:00

score 0 · Accepted Answer · answered Jul 24 '20 at 13:27

You are right to use calculated properties, but are overthinking this a bit. Also, Get-ChildItem returns FileInfo or DirectoryInfo objects. (unless you specify switch -Name, in that case it returns only the names of the items in the path).

These objects have useful properties, such as FullName, Name, LastWriteTime, etc.
Since you only want files returned, you can use the -File switch.

This assumes both input files have the exact same columns as in your example:

# the folder where the input files are and where the output csv file should be saved
$path     = 'D:\Test'
$today    = '{0:yyyy-MM-dd}' -f (Get-Date)
$txtFiles = Get-ChildItem -Path $path -Filter '*.txt' -File

$csv = foreach ($file in $txtFiles) {
        Import-Csv -Path $file.FullName -Delimiter "`t" | 
        Select-Object @{Name = 'Date'; Expression = {$today}},
                      @{Name = 'Filename'; Expression = {$file.Name}}, *
}

$fileOut = Join-Path -Path $path -ChildPath ('Shoppinglist_{0:yyyyMMdd_HHmm}.csv' -f (Get-Date))
$csv | Export-Csv -Path $fileOut -Delimiter ";" -NoTypeInformation

This assumes both input files have at least the 3 desired columns: 'Substance','Information' and 'Comment'

# the folder where the input files are and where the output csv file should be saved
$path     = 'D:\Test'
$today    = '{0:yyyy-MM-dd}' -f (Get-Date)
$txtFiles = Get-ChildItem -Path $path -Filter '*.txt' -File

$csv = foreach ($file in $txtFiles) {
        Import-Csv -Path $file.FullName -Delimiter "`t" | 
        Select-Object @{Name = 'Date'; Expression = {$today}},
                      @{Name = 'Filename'; Expression = {$file.Name}}, 
                      Substance, Information, Comment
}

$fileOut = Join-Path -Path $path -ChildPath ('Shoppinglist_{0:yyyyMMdd_HHmm}.csv' -f (Get-Date))
$csv | Export-Csv -Path $fileOut -Delimiter ";" -NoTypeInformation

^{If you are using a PowerShell version below 3.0, you cannot use the -File switch. Instead then use: $txtFiles = Get-ChildItem -Path $path -Filter '*.txt' | Where-Object { !$_.PSIsContainer }}

Thank you Theo, this one solves my problem! Have a great day!! <3 — TAL, Jul 27 '20 at 07:27

powershell / merging .txt-files into csv adding date and filename

1 Answers1

Linked