none
CSV nach Excel: Problem mit Format beim Konvertieren von Zahlen und Sonderzeichen RRS feed

  • Frage

  • Hallo,

    habe folgendes Problem: beim Konvertieren eines CSV zu XLS bekomm ich bei Zahlen welche mit "+"  beginnen eine Summe, führende 0er werden weggelassen oder große zusammengeschriebene Zahlen werden in Exponentialschreibweise angezeigt etc.
    Die Daten sollen aber 1:1 wie sie zwischen den Anführungszeichen im CSV stehen in das Excel-blatt eingefügt werden.

    Eine Möglichkeit, die aber nicht zufriedenstellend ist, wäre das CSV vorher mit Hochkommas zu versehen vor jedem Wert, da Excel diese dann als Text interpretiert. Allerdings werden diese Hochkommas nach dem konvertieren mitangezeigt, erst nachdem man im Excel einen Doppelklick auf die Zelle macht und dann Enter drückt verschwindet dieses Hochkomma auch. Das muss aber alles automatisch funktionieren. Gibts da irgendeine Möglichkeit?
    Vl. eine andere Strategie wie man CSV nach Excel konvertiert?

    Hier mein Code:

    $csv = "C:\sample.csv"
    $NewCSVfile = "C:\new.csv"
    $xls = "C:\sample.xls"
    
    #Hochkommas in CSV einfügen
    $raw = GC $csv
    $content = $raw | ForEach-Object{ ($_.split(";") | ForEach-Object{if($_ -notmatch "`"`""){$_.Insert(1, "'")} else{$_}}) -Join ";"}
    $content | Out-File $NewCSVfile -Encoding UTF8
    
    #Excelapp
    $excel = New-Object -ComObject excel.application
    $excel.DisplayAlerts = $false
    $excel.Visible = $false
    $workbook = $excel.workbooks.Add()
    $workbook.worksheets.Item(2).delete()
    $workbook.worksheets.Item(2).delete()        
    $worksheet = $workbook.worksheets.Item(1)
    $worksheet.name = "blatt1"
    
    $tempcsv = $excel.Workbooks.Open($NewCSVfile)         
    $tempsheet = $tempcsv.Worksheets.Item(1)
    
    $tempSheet.UsedRange.Copy() | Out-Null        
    $worksheet.Paste()
    $tempcsv.close()
    
    $range = $worksheet.UsedRange
    $range.EntireColumn.Autofit() | Out-Null
    $workbook.saveas($xls)


    Dies würde funktionieren, dauer aber viel zu lange (Zelle für Zelle iterieren):

    for($i = 1; $i -le $colcount; $i++){    
        for($j = 1; $j -le $rowcount; $j++){    
            $ws.cells.Item($j, $i).Value() += ""
        }
    }

    Natürlich hab ich vorher auch schon probiert NumberFormat auf Text ("@") umzustellen, funktioniert aber nicht.

    Die CSV-Felder die in Excel Probleme machen sehen in etwa so aus:
    "+49-11-99999";"2313213213213";

    Wäre sehr froh über Lösungsvorschläge!
    Danke

    Dienstag, 5. Februar 2013 14:44

Antworten

  • Hallo speedcar!

    Das ist kein PowerShell Problem, sondern ein Excel Feature!
    Wenn du deine CSV Datei in .txt umbenennst und mit dem Öffnen Dialog von Excel  öffnest, dann macht Excel den Importassistenten auf indem du die Spalte auf Text stellen kannst! Dann Funktioniert der Import sauber.
    (Für COM siehe hier: http://msdn.microsoft.com/en-us/library/office/bb223513%28v=office.12%29.aspx)

    Wenn du aber unbedingt mit PowerShell die Daten nach Excel Pumpen willst dann bleibt dir nur der härtere weg!

    Pumpe die Daten nach PowerShell-Art über die Nutzung von Objekten  nach Excel:

    Beispiel CSV Daten mit Telefonnummern mit Plus-Zeichen das erhalten bleiben soll und führenden Nullen!

    Nachname, Name, Alter, Telefonnr
    Müller,Claudia,22,+49362771288
    Meier,Sabine,19,00128715529
    Schmidt,Kerstin,25,+331282176356927

    Bei mir Funktionierte beide Text Formatierungen, auf die Spalte und auf die Zelle bezogen!

    $csv = "C:\temp\Daten.csv"
    
    # Test CSV Datei anlegen
    'Nachname, Name, Alter, Telefonnr','Mueller,Claudia,22,+49362771288','Meier,Sabine,19,00128715529','Schmidt,Kerstin,25,+331282176356927' | Out-File $csv
    
    
    #Excelapp
    $excel = New-Object -ComObject excel.application
    $excel.DisplayAlerts = $false
    $excel.Visible = $True
    $workbook = $excel.workbooks.Add()
    
    # Blatt 1 umbenennen
    $worksheet = $workbook.worksheets.Item(1)
    $worksheet.name = "blatt1"
    
    # alle übrigen blätter löschen
    ForEach($Wks in $workbook.worksheets) {
        If(-Not ($Wks.Name -eq "blatt1")) {$Wks.delete()}
    }
    
    
    # Spalte 'D' auf Textformat umstellen
    $worksheet.Columns.Item("D:D").NumberFormat = "@"
    
    
    $RowCounter = 1 # Zähler für die Zeilen
    # CSV einlesen und nin die Exceltabelle übertragen
    ForEach($RowObject in (Import-CSV $csv)){
        
        # Zellen Füllen (ich bevorzuge die hier die Cells schreibweise)
        
        $worksheet.Cells.Item($RowCounter,1).value2 = $RowObject.Nachname
        #$worksheet.Range("A$RowCounter").Value2 = $RowObject.Nachname
        
        $worksheet.Cells.Item($RowCounter,2).value2 = $RowObject.Name
        #$worksheet.Range("B$RowCounter").Value2 = $RowObject.Name
        
        $worksheet.Cells.Item($RowCounter,3).value2 = $RowObject.Alter
        #$worksheet.Range("C$RowCounter").Value2 = $RowObject.Alter
        
        # Zelle auf Textformat umstellen
        #$worksheet.Cells.Item($RowCounter,4).NumberFormat = "@"
        #$worksheet.Range("D$RowCounter").NumberFormat = "@"
        
        $worksheet.Cells.Item($RowCounter,4).value2 = $RowObject.Telefonnr
        #$worksheet.Range("D$RowCounter").Value2 = $RowObject.Telefonnr
        
        # Zähler auf die nächste Zeile setzen
        $RowCounter++
    }

    Wenn du diese Daten öfter benötigst und mit PowerShell arbeiten willst dann lege dir ein eine leer Excel Mappe (.xsl oder .xlsx) an, wo die Spalten schon das richtige Format haben. Dann sparst du dir das Formatieren.

    Eine andere Lösung mit der OpenText() Methode:

    $csv = "C:\temp\Daten.txt"

    # Test CSV Datei anlegen
    'Nachname, Name, Alter, Telefonnr','Mueller,Claudia,22,+49362771288','Meier,Sabine,19,00128715529','Schmidt,Kerstin,25,+331282176356927' | Out-File $csv

    # Excel Konstanten und Werte : http://www.datapigtechnologies.com/downloads/Excel_Enumerations.txt $xlWindows = 2 $xlDelimited = 1 $xlDoubleQuote = 1 # Beispiel einer Excel Makrorecorder aufzeichnung # Workbooks.OpenText Filename:="C:\Temp\Daten.txt", Origin:=xlWindows, _ # StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ # ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ # , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ # Array(3, 1), Array(4, 2)), TrailingMinusNumbers:=True #Excelapp $excel = New-Object -ComObject excel.application $excel.DisplayAlerts = $false $excel.Visible = $True # Methode OpenText Parameter # OpenText(Filename,Origin,StartRow,DataType,TextQualifier,ConsecutiveDelimiter,Tab,Semicolon,Comma,Space,Other,OtherChar,FieldInfo,TextVisualLayout,DecimalSeparator,ThousandsSeparator,TrailingMinusNumbers,Local) # Siehe: http://msdn.microsoft.com/en-us/library/office/bb223513%28v=office.12%29.aspx # und: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.opentext.aspx # Formate für die Spalten festlegen # Spalte 4 bekommt das Format 2 = Text $FieldInfo = @((1,1),(2,1),(3,1),(4,2)) # Methode OpenText (restliche Parameter werden nicht gebraucht) $excel.workbooks.OpenText($csv,$xlWindows,1,$xlDelimited,$xlDoubleQuote,$False,$False,$False,$True,$False,$False,$Null,$FieldInfo) #,$Null,$Null,$Null,$True,$Null)

    Guter Excel PowerShell Link: http://www.powershellpraxis.de/index.php/microsoft-office/excel-ueber-com

    Please click “Mark as Answer” if my post answers your question and click “Vote As Helpful” if my Post helps you.
    Bitte markiere hilfreiche Beiträge von mir als “Als Hilfreich bewerten” und Beiträge die deine Frage ganz oder teilweise beantwortet haben als “Als Antwort markieren”.
    My PowerShell Blog http://www.admin-source.info
    [string](0..21|%{[char][int]([int]("{0:d}" -f 0x28)+('755964655967-86965747271757624-8796158066061').substring(($_*2),2))})-replace' '
    German ? Come to German PowerShell Forum!



    • Als Antwort markiert speedcar343 Mittwoch, 6. Februar 2013 14:41
    • Bearbeitet Peter Kriegel Mittwoch, 6. Februar 2013 14:54
    Mittwoch, 6. Februar 2013 08:07

Alle Antworten

  • Hallo speedcar!

    Das ist kein PowerShell Problem, sondern ein Excel Feature!
    Wenn du deine CSV Datei in .txt umbenennst und mit dem Öffnen Dialog von Excel  öffnest, dann macht Excel den Importassistenten auf indem du die Spalte auf Text stellen kannst! Dann Funktioniert der Import sauber.
    (Für COM siehe hier: http://msdn.microsoft.com/en-us/library/office/bb223513%28v=office.12%29.aspx)

    Wenn du aber unbedingt mit PowerShell die Daten nach Excel Pumpen willst dann bleibt dir nur der härtere weg!

    Pumpe die Daten nach PowerShell-Art über die Nutzung von Objekten  nach Excel:

    Beispiel CSV Daten mit Telefonnummern mit Plus-Zeichen das erhalten bleiben soll und führenden Nullen!

    Nachname, Name, Alter, Telefonnr
    Müller,Claudia,22,+49362771288
    Meier,Sabine,19,00128715529
    Schmidt,Kerstin,25,+331282176356927

    Bei mir Funktionierte beide Text Formatierungen, auf die Spalte und auf die Zelle bezogen!

    $csv = "C:\temp\Daten.csv"
    
    # Test CSV Datei anlegen
    'Nachname, Name, Alter, Telefonnr','Mueller,Claudia,22,+49362771288','Meier,Sabine,19,00128715529','Schmidt,Kerstin,25,+331282176356927' | Out-File $csv
    
    
    #Excelapp
    $excel = New-Object -ComObject excel.application
    $excel.DisplayAlerts = $false
    $excel.Visible = $True
    $workbook = $excel.workbooks.Add()
    
    # Blatt 1 umbenennen
    $worksheet = $workbook.worksheets.Item(1)
    $worksheet.name = "blatt1"
    
    # alle übrigen blätter löschen
    ForEach($Wks in $workbook.worksheets) {
        If(-Not ($Wks.Name -eq "blatt1")) {$Wks.delete()}
    }
    
    
    # Spalte 'D' auf Textformat umstellen
    $worksheet.Columns.Item("D:D").NumberFormat = "@"
    
    
    $RowCounter = 1 # Zähler für die Zeilen
    # CSV einlesen und nin die Exceltabelle übertragen
    ForEach($RowObject in (Import-CSV $csv)){
        
        # Zellen Füllen (ich bevorzuge die hier die Cells schreibweise)
        
        $worksheet.Cells.Item($RowCounter,1).value2 = $RowObject.Nachname
        #$worksheet.Range("A$RowCounter").Value2 = $RowObject.Nachname
        
        $worksheet.Cells.Item($RowCounter,2).value2 = $RowObject.Name
        #$worksheet.Range("B$RowCounter").Value2 = $RowObject.Name
        
        $worksheet.Cells.Item($RowCounter,3).value2 = $RowObject.Alter
        #$worksheet.Range("C$RowCounter").Value2 = $RowObject.Alter
        
        # Zelle auf Textformat umstellen
        #$worksheet.Cells.Item($RowCounter,4).NumberFormat = "@"
        #$worksheet.Range("D$RowCounter").NumberFormat = "@"
        
        $worksheet.Cells.Item($RowCounter,4).value2 = $RowObject.Telefonnr
        #$worksheet.Range("D$RowCounter").Value2 = $RowObject.Telefonnr
        
        # Zähler auf die nächste Zeile setzen
        $RowCounter++
    }

    Wenn du diese Daten öfter benötigst und mit PowerShell arbeiten willst dann lege dir ein eine leer Excel Mappe (.xsl oder .xlsx) an, wo die Spalten schon das richtige Format haben. Dann sparst du dir das Formatieren.

    Eine andere Lösung mit der OpenText() Methode:

    $csv = "C:\temp\Daten.txt"

    # Test CSV Datei anlegen
    'Nachname, Name, Alter, Telefonnr','Mueller,Claudia,22,+49362771288','Meier,Sabine,19,00128715529','Schmidt,Kerstin,25,+331282176356927' | Out-File $csv

    # Excel Konstanten und Werte : http://www.datapigtechnologies.com/downloads/Excel_Enumerations.txt $xlWindows = 2 $xlDelimited = 1 $xlDoubleQuote = 1 # Beispiel einer Excel Makrorecorder aufzeichnung # Workbooks.OpenText Filename:="C:\Temp\Daten.txt", Origin:=xlWindows, _ # StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ # ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ # , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _ # Array(3, 1), Array(4, 2)), TrailingMinusNumbers:=True #Excelapp $excel = New-Object -ComObject excel.application $excel.DisplayAlerts = $false $excel.Visible = $True # Methode OpenText Parameter # OpenText(Filename,Origin,StartRow,DataType,TextQualifier,ConsecutiveDelimiter,Tab,Semicolon,Comma,Space,Other,OtherChar,FieldInfo,TextVisualLayout,DecimalSeparator,ThousandsSeparator,TrailingMinusNumbers,Local) # Siehe: http://msdn.microsoft.com/en-us/library/office/bb223513%28v=office.12%29.aspx # und: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.opentext.aspx # Formate für die Spalten festlegen # Spalte 4 bekommt das Format 2 = Text $FieldInfo = @((1,1),(2,1),(3,1),(4,2)) # Methode OpenText (restliche Parameter werden nicht gebraucht) $excel.workbooks.OpenText($csv,$xlWindows,1,$xlDelimited,$xlDoubleQuote,$False,$False,$False,$True,$False,$False,$Null,$FieldInfo) #,$Null,$Null,$Null,$True,$Null)

    Guter Excel PowerShell Link: http://www.powershellpraxis.de/index.php/microsoft-office/excel-ueber-com

    Please click “Mark as Answer” if my post answers your question and click “Vote As Helpful” if my Post helps you.
    Bitte markiere hilfreiche Beiträge von mir als “Als Hilfreich bewerten” und Beiträge die deine Frage ganz oder teilweise beantwortet haben als “Als Antwort markieren”.
    My PowerShell Blog http://www.admin-source.info
    [string](0..21|%{[char][int]([int]("{0:d}" -f 0x28)+('755964655967-86965747271757624-8796158066061').substring(($_*2),2))})-replace' '
    German ? Come to German PowerShell Forum!



    • Als Antwort markiert speedcar343 Mittwoch, 6. Februar 2013 14:41
    • Bearbeitet Peter Kriegel Mittwoch, 6. Februar 2013 14:54
    Mittwoch, 6. Februar 2013 08:07
  • Hallo Peter,

    danke für die Antwort!
    Zu Vorschlag 1: Funktioniert toll, allerdings müsste ich dann die Headerinfo bei jedem CSV dann extra eintragen, ich muss nämlich verschiedene Files auslesen können.
    Vorschlag 2: Gefällt mir schon sehr gut, einzig das Fieldinfoarray müsste irgendwie dynamisch sein, aber damit kann ich schon sehr gut Leben, das kommt jetzt mal so in Einsatz!

    Vielen Dank!

    Mittwoch, 6. Februar 2013 14:41