rippchen
Jul 08, 2016
view8798
view16
0
Goto Top

PowerShell: Spalten tauschen und neue Spalten in CSV datei erzeugen

GermansolvedQuestionBatch, ShellDevelopment
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
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 309325

Url: https://administrator.de/contentid/309325

Printed on: April 26, 2024 at 15:04 o'clock

16 Comments
Latest comment
Goto Top
Mitglied: 129813
129813 Jul 08, 2016 updated at 17:44:23 (UTC)
Goto Top
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
Member: Rippchen
Rippchen Jul 08, 2016 at 19:33:10 (UTC)
Goto Top
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?
Mitglied: 129813
129813 Jul 08, 2016 updated at 20:42:47 (UTC)
Goto Top
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 face-smile
Member: Rippchen
Rippchen Jul 09, 2016 at 10:52:07 (UTC)
Goto Top
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.
Mitglied: 129813
Solution 129813 Jul 09, 2016 updated at 11:47:45 (UTC)
Goto Top
# check if property exists ...
if ($csv.ReasonA -ne $null){
   # Type A
}else{
  # Type B
}
Member: Rippchen
Rippchen Jul 26, 2016 at 07:54:40 (UTC)
Goto Top
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
Mitglied: 129813
129813 Jul 26, 2016 updated at 08:11:35 (UTC)
Goto Top
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.
Member: Rippchen
Rippchen Jul 26, 2016 at 08:21:03 (UTC)
Goto Top
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?
Mitglied: 129813
129813 Jul 26, 2016 updated at 08:41:45 (UTC)
Goto Top
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 face-sad

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.
Member: Rippchen
Rippchen Jul 26, 2016 at 08:50:14 (UTC)
Goto Top
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
Mitglied: 129813
129813 Jul 26, 2016 updated at 09:01:44 (UTC)
Goto Top
Your script will never work because the variable $csv does not exist at the if check face-big-smile, that's your error!!!!!!
You first have to check if the file has enough columns and then decide which way to go.
Member: Rippchen
Rippchen Jul 26, 2016 at 14:55:04 (UTC)
Goto Top
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.
Mitglied: 129813
Solution 129813 Jul 26, 2016 updated at 15:33:25 (UTC)
Goto Top
$folder.ReasonA
???? Why did you try this ?? $folder is a plain string and never has a property ReasonA OMG face-big-smile You are really funny boy face-big-smile
$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  
}
Member: Rippchen
Rippchen Jul 28, 2016 at 07:57:40 (UTC)
Goto Top
Thank you very very much face-smile

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(';')" ?
Mitglied: 129813
129813 Jul 28, 2016 updated at 08:08:11 (UTC)
Goto Top
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.
Member: Rippchen
Rippchen Jul 28, 2016 at 08:13:17 (UTC)
Goto Top
Thanks face-smile
GermansolvedQuestionBatch, ShellDevelopment