08.07.2016

9609

PowerShell: Spalten tauschen und neue Spalten in CSV datei erzeugen

Hallo ,

ich nutze folgendes PowerShell Skript:

$folder = 'C:\Users\Laptop\Desktop\Import'  
$out = 'C:\Users\Laptop\Desktop\Export\EXPORT.csv'  
gci $folder -Filter *.csv -recurse | %{
    $raw = ((gc $_.FullName) | select -Skip 7) 
    $csv = $raw[0..($raw.GetUpperBound(0)-1)]| ConvertFrom-CSV -Delimiter ";" -Header "MesswertA","Land","MesswertB","MesswertC","MesswertD","Capacity","ReasonA","TimeStamp","MesswertE","PublicationTimeStamp","ModificationTimeStamp" # CSV durch Semikolon getrennt, Überschriften der erzeugten Dateien werden festgelegt, die letzte Zeile jeder eingelesenen Datei wird gelöscht, da Prüfsumme.  
    $csv | %{$_."ModificationTimeStamp" = get-date $_."ModificationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
    $csv | %{$_."PublicationTimeStamp" = get-date $_."PublicationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
    $csv | %{$_."TimeStamp" = get-date $_."TimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
    $csv | export-csv $out -Append -Delimiter ";" -Notype -Encoding UTF8   
    
 }

Ich habe folgendes Problem. Ich bearbeite und fasse zwei Typen von CSV Dateien zusammen, die sich in der Anordnung und Anzahl der Spalten unterscheiden.

CSV1: Header: "MesswertA","Land","MesswertB","MesswertC","MesswertD","Capacity","ReasonA","TimeStamp","MesswertE","PublicationTimeStamp","ModificationTimeStamp"
CSV2: Header: "MesswertA","Land","MesswertB","MesswertC","MesswertD","Capacity","TimeStamp","MesswertE","PublicationTimeStamp","ModificationTimeStamp"

Der Unterschied ist, dass in CSV1 in der 7. Spalte ein extra Wert steht, der in CSV2 nicht steht. Wenn ich nun alle Dateien über -recurse einlese und auch das Datum umformatieren lasse, dann funktioniert es so natürlich nicht. In CSV2 ist der "TimeStamp" in Spalte 7 und nicht wie bei CSV 1: in Spalte 8. Dementsprechend kann keine Umformatierung vorgenommen werden.

Meine Frage ist: Wie kann ich in dieses Skript einbauen, dass er CSV2: mit einer Spalte "ReasonA" ausstattet, die genau wie bei CSV 1 Spalte 7 darstellt. Zudem sollte diese Spalte mit Nullen gefüllt werden (eine Null pro Zeile). Die CSV Dateien sind eindeutig erkennbar an dem Namen, das heißt CSV1: heißt ....DEAT und CSV2: .... (also ohne DEAT).
Das Skript soll quasi alle Dateien in dem Ordner einlesen, erkennen um welchen CSV Typ es sich handelt, die Datumsformatierung für CSV Typ 1 wie gewohnt vornehmen und bei CSV2 erst die Spalte mit Nullen erzeugen und dann die Datumsformatierung vornehmen.

Das Ergebnis wäre eine CSV Datei: Die das richtige Datumsformat enthält und eine einheitliche Headerstruktur aufweist.

VG
Rippchen

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 309325

Url: https://administrator.de/forum/powershell-spalten-tauschen-und-neue-spalten-in-csv-datei-erzeugen-309325.html

Ausgedruckt am: 28.04.2025 um 18:04 Uhr

16 Kommentare

Neuester Kommentar

Hi.
With

$csv | add-member -MemberType NoteProperty -Name  'NameOfColumn' -Value 'ValueOfColumn' -Force

you can add additional columns to a csv. To fill it with a "0" simply supply the -value property of add-member. Thats all you need.

Regards

Hi, thank you for your answer.

This works not perfect. Now I get a new column on the end. But i want a new column between "Capacity" and "TimeStamp".

 $folder = 'C:\Users\Laptop\Desktop\Import'  
$out = 'C:\Users\Laptop\Desktop\Export\EXPORT.csv'  
gci $folder -Filter *.csv -recurse | %{
    $raw = ((gc $_.FullName) | select -Skip 7) 
    $csv = $raw[0..($raw.GetUpperBound(0)-1)]| ConvertFrom-CSV -Delimiter ";" -Header "MesswertA","Land","MesswertB","MesswertC","MesswertD","Capacity","TimeStamp","MesswertE","PublicationTimeStamp","ModificationTimeStamp"   
    $csv | add-member -MemberType NoteProperty -Name  'NameOfColumn' -Value 'ValueOfColumn' -Force  
    $csv | %{$_."ModificationTimeStamp" = get-date $_."ModificationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
    $csv | %{$_."PublicationTimeStamp" = get-date $_."PublicationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
    $csv | %{$_."TimeStamp" = get-date $_."TimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
    $csv | export-csv $out -Append -Delimiter ";" -Notype -Encoding UTF8   
    
 }

What did i wrong?

Ordering columns (in reality these are no columns but "properties") is the easiest thing .... You do it with a simple select-object statement with the names of all your columns in the desired order, before you export the object !

$csv | select Column1,Column2,Column3,........ | export-csv ..............

and so on. Really easy

Hello highload,

thank you. It works fine.

But now I have an another problem.

I recognised that the different columns are not special for the CSV typ 1 or 2. Each CSV file can have the column layout A or B.

column A Header Layout :"INFO","MesswertA","Land","MesswertB","MesswertC","MesswertD","Capacity","ReasonA","TimeStamp","MesswertE","PublicationTimeStamp","ModificationTimeStamp"

column B Header Layout : "INFO,"MesswertA","Land","MesswertB","MesswertC","MesswertD","Capacity","TimeStamp","MesswertE","PublicationTimeStamp","ModificationTimeStamp"

The next step is:

The script has to do:

1: Read the csv. Files
2. look at the Header: Is it Typ A or B
3. If Typ A: Formate the date and export
4. If Typ B: First make a new column "ReasonA", then ordering the columns, then formate the date and export

The actual script is:

 $folder = 'C:\Users\Laptop\Desktop\Import'  
$out = 'C:\Users\Laptop\Desktop\Export\EXPORT.csv'  
gci $folder -Filter *.csv -recurse | %{
    $raw = ((gc $_.FullName) | select -Skip 7) 
    $csv = $raw[0..($raw.GetUpperBound(0)-1)]| ConvertFrom-CSV -Delimiter ";" -Header "MesswertA","Land","MesswertB","MesswertC","MesswertD","Capacity","TimeStamp","MesswertE","PublicationTimeStamp","ModificationTimeStamp"   
    $csv | add-member -MemberType NoteProperty -Name  "ReportA" -Value ' ' -Force  
    $csv | %{$_."ModificationTimeStamp" = get-date $_."ModificationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
    $csv | %{$_."PublicationTimeStamp" = get-date $_."PublicationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
    $csv | %{$_."TimeStamp" = get-date $_."TimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
    $csv | select "MesswertA","Land","MesswertB","MesswertC","MesswertD","Capacity","ReportA","TimeStamp","MesswertE","PublicationTimeStamp","ModificationTimeStamp" | export-csv $out -Append -Delimiter ";" -Notype -Encoding UTF8   
    
 }

In each importet csv file is the header in the 4. row before the first 7 rows are deleted.
In the script must somethink like. Search for "reasonA" in row 4.

If .... then ....
If not .... then ....

My PowerShell skills are to low for that.

Please help.

# check if property exists ...
if ($csv.ReasonA -ne $null){
   # Type A
}else{
  # Type B
}

Hello,

I tested the script for some other files and now i get the error, that the script can`t find dates in the specific columns.

The Error only exist if:

-First comes a "type B" and then a "type A" file or if there is only a "type B file"
-not if there is only a "type A"

the script is:

$folder = 'C:\Import'  
$out = 'C:\Export\Export.csv'  
gci $folder -Filter *.csv -recurse | %{
    
    if ($csv.ReasonA -ne $null){
        
        $raw = ((gc $_.FullName) | select -Skip 7) 
        $csv = $raw[0..($raw.GetUpperBound(0)-1)]| ConvertFrom-CSV -Delimiter ";" -Header "CAR","Country","FUEl","NUMStartDate","NUMEndDate","Cost","ReasonA","TimeStamp","MIX","PublicationTimeStamp","ModificationTimeStamp"  
        $csv | %{$_."NUMStartDate" = get-date $_."NUMStartDate" -Format 'dd-MM-yyyy HH:mm:ss'}  
        $csv | %{$_."NUMEndDate" = get-date $_."NUMEndDate" -Format 'dd-MM-yyyy HH:mm:ss'}  
        $csv | %{$_."TimeStamp" = get-date $_."TimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
        $csv | %{$_."PublicationTimeStamp" = get-date $_."PublicationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}  
        $csv | %{$_."ModificationTimeStamp" = get-date $_."ModificationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}       
           
    } else{

        $raw = ((gc $_.FullName) | select -Skip 7) 
        $csv = $raw[0..($raw.GetUpperBound(0)-1)]| ConvertFrom-CSV -Delimiter ";" -Header "CAR","Country","FUEl","NUMStartDate","NUMEndDate","Cost","TimeStamp","MIX","PublicationTimeStamp","ModificationTimeStamp"  
        $csv | %{$_."NUMStartDate" = get-date $_."NUMStartDate" -Format 'dd-MM-yyyy HH:mm:ss'}  
        $csv | %{$_."NUMEndDate" = get-date $_."NUMEndDate" -Format 'dd-MM-yyyy HH:mm:ss'}  
        $csv | %{$_."TimeStamp" = get-date $_."TimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
        $csv | %{$_."PublicationTimeStamp" = get-date $_."PublicationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}  
        $csv | %{$_."ModificationTimeStamp" = get-date $_."ModificationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
        $csv | add-member -MemberType NoteProperty -Name  "NonavailabilityReason" -Value '' -Force   
        
    }

$csv | select "CAR","Country","FUEl","NUMStartDate","NUMEndDate","Cost","ReasonA","TimeStamp","MIX","PublicationTimeStamp","ModificationTimeStamp"| export-csv $out -Append -Delimiter ";" -Notype -Encoding UTF8  

[System.GC]::Collect()
}

the error code is:

Get-Date : Der Parameter "Date" kann nicht an das Ziel gebunden werden. Ausnahme beim Festlegen von "Date": "NULL kann nicht in   
den Typ "System.DateTime" konvertiert werden."  
In Zeile:43 Zeichen:56
+ ... "ModificationTimeStamp" = get-date $_."ModificationTimeStamp" -Format ...  
+                                        ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : WriteError: (:) [Get-Date], ParameterBindingException
    + FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.PowerShell.Commands.GetDateCommand

please help.

Rippchen

If you don't check if there is a valid date value which can be interpreted as a date, this error is normal because a "null" value can't be converted into a date ! So check if the date columns are filled with valid data. Not every date time string value can be automatically converted to a date time value. Some formats need the format string to be passed as a parameter.

You are right, but i have already checked that. In the Timestamp column is "2012-08-26T00:01:49+02:00" and in the following column (MIX) is "0".

I think the problem is that the script "thinks" that there is a "ReasonA" column. Because of that, the script thinks that the property "Timestamp" stands there were the property "Mix" stands.

The property "ReasonA" stands bevore the property "Timestamp" so in this case the Timestamp stands were the property "Mix" in a type B file stands.

Do you now what i mean?

Zitat von @Rippchen:
I think the problem is that the script "thinks" that there is a "ReasonA" column. Because of that, the script thinks that the property "Timestamp" stands there were the property "Mix" stands.

No this is never possible, because you access your columns(in reality = properties) by property name!!!

The property "ReasonA" stands bevore the property "Timestamp" so in this case the Timestamp stands were the property "Mix" in a type B file stands.

See comment above, this is not possible because you access the properties by name. The position is totally irrelevant !! The property names are defined by the heading column of the CSV file.

We don't know how your files look like exactly

So if you want further help from me, contact me via personal message than i can make you an offer for a script.

Good luck.

Ok, but why I get this error ?

Here are the files:

http://www.file-upload.net/download-11795423/TypeA.csv.html
http://www.file-upload.net/download-11795422/TypeB.csv.html

Your script will never work because the variable $csv does not exist at the if check

, that's your error!!!!!!
You first have to check if the file has enough columns and then decide which way to go.

Thanks for your answere.

I changed it into:

$folder = 'C:\Import'  
$out = 'C:\Export\Export.csv'  
gci $folder -Filter *.csv -recurse | %{
    
if ($folder.ReasonA -ne $null){
        $raw = ((gc $_.FullName) | select -Skip 7) 
        $csv = $raw[0..($raw.GetUpperBound(0)-1)]| ConvertFrom-CSV -Delimiter ";" -Header    "CAR","Country","FUEl","NUMStartDate","NUMEndDate","Cost","ReasonA","TimeStamp","MIX","PublicationTimeStamp","ModificationTimeStamp"  
                
   } else{

        $raw = ((gc $_.FullName) | select -Skip 7) 
        $csv = $raw[0..($raw.GetUpperBound(0)-1)]| ConvertFrom-CSV -Delimiter ";" -Header "CAR","Country","FUEl","NUMStartDate","NUMEndDate","Cost","TimeStamp","MIX","PublicationTimeStamp","ModificationTimeStamp"  
} 
        $csv | add-member -MemberType NoteProperty -Name  "NonavailabilityReason" -Value '' -Force   
        
    }
$csv | %{$_."NUMStartDate" = get-date $_."NUMStartDate" -Format 'dd-MM-yyyy HH:mm:ss'}  
$csv | %{$_."NUMEndDate" = get-date $_."NUMEndDate" -Format 'dd-MM-yyyy HH:mm:ss'}  
$csv | %{$_."TimeStamp" = get-date $_."TimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
$csv | %{$_."PublicationTimeStamp" = get-date $_."PublicationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}  
$csv | %{$_."ModificationTimeStamp" = get-date $_."ModificationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}  

$csv | select "CAR","Country","FUEl","NUMStartDate","NUMEndDate","Cost","ReasonA","TimeStamp","MIX","PublicationTimeStamp","ModificationTimeStamp"| export-csv $out -Append -Delimiter ";" -Notype -Encoding UTF8  

[System.GC]::Collect()
}
   

but it doesn`t work. The problem is that i can`t delete the first rows bevor i do the if check and my ps skills are to low.

$folder.ReasonA

???? Why did you try this ?? $folder is a plain string and never has a property ReasonA OMG

You are really funny boy

$folder = 'C:\Import'  
$out = 'C:\Export\Export.csv'  
gci $folder -Filter *.csv -recurse | %{
    $raw = gc $_.Fullname | select -skip 7
    if ($raw.split(';').count -eq 11){  
        $csv = $raw[0..($raw.GetUpperBound(0)-1)]| ConvertFrom-CSV -Delimiter ";" -Header "CAR","Country","FUEl","NUMStartDate","NUMEndDate","Cost","ReasonA","TimeStamp","MIX","PublicationTimeStamp","ModificationTimeStamp"  
    }else{
        $csv = $raw[0..($raw.GetUpperBound(0)-1)]| ConvertFrom-CSV -Delimiter ";" -Header "CAR","Country","FUEl","NUMStartDate","NUMEndDate","Cost","TimeStamp","MIX","PublicationTimeStamp","ModificationTimeStamp"  
        $csv | add-member -MemberType NoteProperty -Name  "ReasonA" -Value '' -Force  
    } 
    $csv | add-member -MemberType NoteProperty -Name  "NonavailabilityReason" -Value '' -Force  
    $csv | %{$_."NUMStartDate" = get-date $_."NUMStartDate" -Format 'dd-MM-yyyy HH:mm:ss'}  
    $csv | %{$_."NUMEndDate" = get-date $_."NUMEndDate" -Format 'dd-MM-yyyy HH:mm:ss'}  
    $csv | %{$_."TimeStamp" = get-date $_."TimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}   
    $csv | %{$_."PublicationTimeStamp" = get-date $_."PublicationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}  
    $csv | %{$_."ModificationTimeStamp" = get-date $_."ModificationTimeStamp" -Format 'dd-MM-yyyy HH:mm:ss'}  
    $csv | select "CAR","Country","FUEl","NUMStartDate","NUMEndDate","Cost","ReasonA","TimeStamp","MIX","PublicationTimeStamp","ModificationTimeStamp"| export-csv $out -Append -Delimiter ";" -Notype -Encoding UTF8  
}

Thank you very very much

now it works fine.

Can you please explain what this part does?

($raw.split(';').count -eq 11)

the "count -eq11" counts the columns (if 11 columns, ...)?

but what means the "$raw.split(';')" ?

It splits the first row (after skipping 7) into an array by using the semicolon as delimiter, then it counts the number of elements in the array with the property count to get the number of columns.