Sehr kniffliges Problem - Powershell regular expression :-(
Aus einem Messgerät fließen eine bestimmte Anzahl von Daten durch Messungen. Wegen der ggf. Masse an Dateien, importieren wir aktuell die Dateien per Hand in Excel und ändern dann auch alles per Hand in Excel.
Um das zu automatisieren und viel schneller handeln zu können, würde ich gerne mit Powershell dieses umsetzen.
Ich habe also eine Textdatei, die von einem Instrument ausgegeben wird, um sie dann nach Wunsch in Excel zu öffnen (einzulesen).
Diese Datei sieht nach der Ausgabe durch das Instrument z.B. so aus:
Mein Problem ist nun aber folgendes.
Aus dieser Datei müssen vor dem Export nach Excel folgende Dinge verändert werden:
1) soll die 2. und 3. Zeile gelöscht werden
2) müssten die Zeichen wie R_Type1,R_Type2, ... und andere ebenso entfernt werden. Das Problem hierbei ist nun aber, dass auch die dann sich darunter befindlichen Werte mit gelöscht werden müssen, da sonst beim Öffnen in Excel die Spalten nicht mehr stimmen.
Also als Beispiel sollte nach dem Import aus der ersten Zeile z.B. die Zahl 158 mit dem Wert "u/L" aus der wegen Punkt 1) gelöschten Zeilen, dann Zeile 2 übereinanderstimmen, ebenso dann die entsprechenden Werte darunter, in dem Fall 94,95,95,95,94,95
3) dazu kommt, dass man sehen kann: es bestehen dort doppelte Kommas, die beim Import in Excel leere Spalten verursachen. Diese sollten ebenso vermieden werden.
Ich habe mal eine Datei soweit editiert, wie sie am Ende aussehen soll, damit man sie einfach in Excel importieren kann.
Ich habe zwar verschiedene Ansätze (get-content...) usw., aber leider bin ich in Powershell nicht so professionell drauf, um dieses zu einem Wunschergebis umsetzen zu können.
Kann mir hier jemand helfen ?.
Um das zu automatisieren und viel schneller handeln zu können, würde ich gerne mit Powershell dieses umsetzen.
Ich habe also eine Textdatei, die von einem Instrument ausgegeben wird, um sie dann nach Wunsch in Excel zu öffnen (einzulesen).
Diese Datei sieht nach der Ausgabe durch das Instrument z.B. so aus:
,,,,,,,,,,,,,,,,,,,, 21,, 22,, 40,, 57,, 59,, 80,, 102,, 158,, 210,, 220,, 311,, 312,, 313,, 413,, 418,, 435,, 452,, 510,, 570,, 588,, 661,, 678,, 684,, 686,, 690,, 691,, 698,, 701,, 708,, 712,, 714,, 731,, 734,, 750,, 767,, 781,, 798,, 989,, 990,, 991,, 992,, 993,, 994,
,,,,,,,,,,,,,,,,,,,,C,,C,,C,,C,,C,,C,,,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,,C,
,,,,,,,,,,,,,,,,,,,,1,,1,,1,,1,,1,,1,,,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,,1,
R_Type1,R_Type2,S_Type,S_No.,R_No.,Pos,S_ID,M_Date,Age,Unit,Sex,S_Date,C1,C2,C3,C4,C5,Cup,Ope_D,N,U/L ,,U/L ,,mmol/L,,U/L ,,mmol/L,,U/L ,,ug/mL ,,U/L ,,mg/L ,,U/L ,,U/L ,,U/L ,,U/L ,,g/L ,,mmol/L,,mmol/L,,umol/L,,kU/L ,,U/L ,,U/L ,,umol/L,,g/L ,,U/L ,,U/L ,,umol/L,,mmol/L,,mmol/L,,mmol/L,,mg/L ,,umol/L,,mmol/L,,U/L ,,umol/L,,mAbs ,,mmol/L,,mmol/L,,mmol/L,,mmol/L,,mmol/L,,mmol/L,,,,,,,
1,1,1, 1, 1334,1,1 ,2014/07/30 13:41:00,,,,, , , , , ,1,, 8,,,,,,,,,,,,,,, 94,,,, 55,,,,,,,,,,,,,,,,,,,, 27.7,,,,,, 47,, 49,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 8,, 6,, 2,
1,2,1, 1, 1334,1,1 ,2014/07/30 13:41:00,,,,, , , , , ,1,, 1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 29.1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
1,1,1, 2, 1334,2, ,2014/07/30 13:41:00,,,,, , , , , ,1,, 8,,,,,,,,,,,,,,, 95,,,, 55,,,,,,,,,,,,,,,,,,,, 28.1,,,,,, 46,, 48,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 9,, 6,, 2,
1,1,1, 3, 1334,3, ,2014/07/30 13:41:00,,,,, , , , , ,1,, 8,,,,,,,,,,,,,,, 95,,,, 55,,,,,,,,,,,,,,,,,,,, 27.8,,,,,, 47,, 47,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 9,, 6,, 2,
1,1,1, 4, 1334,4, ,2014/07/30 13:41:00,,,,, , , , , ,1,, 8,,,,,,,,,,,,,,, 94,,,, 55,,,,,,,,,,,,,,,,,,,, 27.8,,,,,, 47,, 48,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 9,, 5,, 2,
1,1,1, 5, 1334,5, ,2014/07/30 13:41:00,,,,, , , , , ,1,, 8,,,,,,,,,,,,,,, 95,,,, 56,,,,,,,,,,,,,,,,,,,, 27.9,,,,,, 46,, 48,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 9,, 7,, 2,
1,1,1, 6, 1335,1,6 ,2014/07/30 11:09:00,,,,, , , , , ,1,, 15,,,,,,,,,,,,,,,,,,,,,,,,,,, 32.6,, 6.43,,,,,,,, 84,,,,,, 47.8,,,,,, 96,,,, 2.18,,,,,, 17.2,, 1.15,, 53.20,,,,,, 5.66,, 1.17,, 2.11,, 110,, 3.62,, 76.1,,,,,,,
1,1,1, 7, 1335,2, ,2014/07/30 11:09:00,,,,, , , , , ,1,, 15,,,,,,,,,,,,,,,,,,,,,,,,,,, 32.5,, 6.52,,,,,,,, 86,,,,,, 48.4,,,,,, 97,,,, 2.15,,,,,, 16.9,, 1.13,, 53.83,,,,,, 5.71,, 1.18,, 2.12,, 112,, 3.67,, 77.7,,,,,,,
1,1,1, 8, 1335,3, ,2014/07/30 11:09:00,,,,, , , , , ,1,, 15,,,,,,,,,,,,,,,,,,,,,,,,,,, 31.6,, 6.47,,,,,,,, 84,,,,,, 48.0,,,,,, 97,,,, 2.22,,,,,, 17.0,, 1.16,, 52.56,,,,,, 5.68,, 1.17,, 2.10,, 111,, 3.67,, 77.8,,,,,,,
1,1,1, 9, 1335,4, ,2014/07/30 11:09:00,,,,, , , , , ,1,, 15,,,,,,,,,,,,,,,,,,,,,,,,,,, 32.1,, 6.38,,,,,,,, 85,,,,,, 47.2,,,,,, 96,,,, 2.15,,,,,, 16.7,, 1.15,, 53.21,,,,,, 5.60,, 1.18,, 2.10,, 112,, 3.69,, 77.9,,,,,,,
1,1,1, 10, 1335,5, ,2014/07/30 11:09:00,,,,, , , , , ,1,, 15,,,,,,,,,,,,,,,,,,,,,,,,,,, 32.1,, 6.47,,,,,,,, 84,,,,,, 49.1,,,,,, 99,,,, 2.19,,,,,, 16.8,, 1.17,, 53.60,,,,,, 5.61,, 1.17,, 2.11,, 112,, 3.70,, 77.9,,,,,,,
Mein Problem ist nun aber folgendes.
Aus dieser Datei müssen vor dem Export nach Excel folgende Dinge verändert werden:
1) soll die 2. und 3. Zeile gelöscht werden
2) müssten die Zeichen wie R_Type1,R_Type2, ... und andere ebenso entfernt werden. Das Problem hierbei ist nun aber, dass auch die dann sich darunter befindlichen Werte mit gelöscht werden müssen, da sonst beim Öffnen in Excel die Spalten nicht mehr stimmen.
Also als Beispiel sollte nach dem Import aus der ersten Zeile z.B. die Zahl 158 mit dem Wert "u/L" aus der wegen Punkt 1) gelöschten Zeilen, dann Zeile 2 übereinanderstimmen, ebenso dann die entsprechenden Werte darunter, in dem Fall 94,95,95,95,94,95
3) dazu kommt, dass man sehen kann: es bestehen dort doppelte Kommas, die beim Import in Excel leere Spalten verursachen. Diese sollten ebenso vermieden werden.
Ich habe mal eine Datei soweit editiert, wie sie am Ende aussehen soll, damit man sie einfach in Excel importieren kann.
;;;; 21; 22; 40; 57; 59; 80; 102; 158; 210; 220; 311; 312; 313; 413; 418; 435; 452; 510; 570; 588; 661; 678; 684; 686; 690; 691; 698; 701; 708; 712; 714; 731; 734; 750; 767; 781; 798; 989; 990; 991; 992; 993; 994;
S_Type;S_No.;S_ID;M_Date;"U/L";"U/L";"mmol";"U/L";"mmol";"U/L";"ug/mL";"U/L";"mg/L";"U/L";"U/L";"U/L";"U/L";"g/L";"mmol/L";"mmol/L";"umol/L";"kU/L";"U/L";"U/L";"umol/L";"g/L";"U/L";"U/L";"umol/L";"mmol/L";"mmol/L";"mmol/L";"mg/L";"umol/L";"mmol/L";"U/L";"umol/L";"mAbs";"mmol/L";"mmol/L";"mmol/L";"mmol/L";"mmol/L";"mmol/L";;;
1;1;1;30/07/2014;;;;;;;;94;;55;;;;;;;;;; 27.7;;;47;49;;;;;;;;;;;;;;;;;8;6;2
1;1;1;30/07/2014;;;;;;;;;;;;;;;;;;;; 29.1;;;;;;;;;;;;;;;;;;;;;;;
1;2; ;30/07/2014;;;;;;;;95;;55;;;;;;;;;; 28.1;;;46;48;;;;;;;;;;;;;;;;;9;6;2
1;3; ;30/07/2014;;;;;;;;95;;55;;;;;;;;;; 27.8;;;47;47;;;;;;;;;;;;;;;;;9;6;2
1;4; ;30/07/2014;;;;;;;;94;;55;;;;;;;;;; 27.8;;;47;48;;;;;;;;;;;;;;;;;9;5;2
1;5; ;30/07/2014;;;;;;;;95;;56;;;;;;;;;; 27.9;;;46;48;;;;;;;;;;;;;;;;;9;7;2
1;6;6;30/07/2014;;;;;;;;;;;;;; 32.6; 6.43;;;;84;;; 47.8;;;96;; 2.18;;; 17.2; 1.15; 53.20;;; 5.66; 1.17; 2.11;110; 3.62; 76.1;;;
1;7; ;30/07/2014;;;;;;;;;;;;;; 32.5; 6.52;;;;86;;; 48.4;;;97;; 2.15;;; 16.9; 1.13; 53.83;;; 5.71; 1.18; 2.12;112; 3.67; 77.7;;;
1;8; ;30/07/2014;;;;;;;;;;;;;; 31.6; 6.47;;;;84;;; 48.0;;;97;; 2.22;;; 17.0; 1.16; 52.56;;; 5.68; 1.17; 2.10;111; 3.67; 77.8;;;
1;9; ;30/07/2014;;;;;;;;;;;;;; 32.1; 6.38;;;;85;;; 47.2;;;96;; 2.15;;; 16.7; 1.15; 53.21;;; 5.60; 1.18; 2.10;112; 3.69; 77.9;;;
1;10; ;30/07/2014;;;;;;;;;;;;;; 32.1; 6.47;;;;84;;; 49.1;;;99;; 2.19;;; 16.8; 1.17; 53.60;;; 5.61; 1.17; 2.11;112; 3.70; 77.9;;;
Kann mir hier jemand helfen ?.
Please also mark the comments that contributed to the solution of the article
Kommentar vom Moderator Dani am Aug 07, 2014 um 11:06:47 Uhr
Verschoben.
Content-Key: 245836
Url: https://administrator.de/contentid/245836
Printed on: April 25, 2024 at 15:04 o'clock
7 Comments
Latest comment
Hallo,
statt diese Aufgabe mit Regular Expressions zu lösen, sollte das doch besser mit dem Programm erledigt werden, das dafür am besten geeignet ist - Excel selbst. Da sich Excel per ActiveX-Schnittstelle "fernsteuern" lässt, ist das auch kein Problem.
Deshalb hier mein Vorschlag in VBScript, das ich immer noch gegenüber PowerShell bevorzuge:
Das Script erzeugt als Ausgabe eine neue Datei mit dem Namen
Es wird davon ausgegangen, dass die Eingabedateien mit der Codepage 1252 codiert sind.
Gruß
Friemler
[EDIT]
[/EDIT]
[EDIT2]
Die Reihenfolge der Parameter, die an das VBScript übergeben werden, ist beliebig. Die Parameter
[/EDIT2]
{EDIT3]
[/EDIT3]
statt diese Aufgabe mit Regular Expressions zu lösen, sollte das doch besser mit dem Programm erledigt werden, das dafür am besten geeignet ist - Excel selbst. Da sich Excel per ActiveX-Schnittstelle "fernsteuern" lässt, ist das auch kein Problem.
Deshalb hier mein Vorschlag in VBScript, das ich immer noch gegenüber PowerShell bevorzuge:
Option Explicit
'-----------------------------------
' Konstanten für Datei Ein-/Ausgabe
'-----------------------------------
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
'-----------------------------------
' Globale Variablen deklarieren
'-----------------------------------
Dim strScriptPath, strScriptName
Dim strInFile, strOutFile, strConfigFile
Dim dicColHeaders, dicCodeNumbers
Dim arrColumnsToDelete
Dim colArgs, objFSO, objExcel
'-----------------------------------
' Konfiguration
'-----------------------------------
'Standardname der Datei, die Informationen zum Ändern von Zellinhalten enthält
strConfigFile = "config.ini"
'-----------------------------------
' Hauptprogramm
'-----------------------------------
'Liste der Programmparameter holen
Set colArgs = WScript.Arguments
'Objekt für den Zugriff auf Dateisystem-Funktionen erzeugen
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Vollständigen Pfad ermitteln, unter dem dieses Script gestartet wurde
'und den Namen der Scriptdatei daraus extrahieren
strScriptPath = WScript.ScriptFullName
strScriptName = objFSO.GetBaseName(strScriptPath)
'Prüfen, ob alle benötigten Parameter übergeben wurden
'und ggf. Standardwerte setzen
If ParseCommandLine Then
'Leeres Array anlegen, in dem die Titel
'der zu löschenden Spalten gespeichert werden
arrColumnsToDelete = Array()
'Dictionary-Objekte für die Speicherung der Konfiguration erzeugen
Set dicCodeNumbers = CreateObject("Scripting.Dictionary")
Set dicColHeaders = CreateObject("Scripting.Dictionary")
'Die Konfigurationsdatei laden
If LoadConfigFile(strConfigFile) Then
'Wenn kein Fehler aufgetreten ist, die CSV-Datei laden
If OpenCSVFile(strInFile) Then
'Wenn kein Fehler aufgetreten ist, die gewünschten Änderungen ausführen, ...
Call ProcessCSVFileContent
'...die Datei speichern, ...
Call SaveCSVFile(strOutFile)
'...und Excel beenden
objExcel.Quit
Else
'Falls das Laden der CSV-Datei fehlschlug, eine Fehlermeldung anzeigen
Call ShowFileLoadError(strInFile)
End If
Else
'Falls das Laden der Konfigurationsdatei fehlschlug, eine Fehlermeldung anzeigen
Call ShowFileLoadError(strConfigFile)
End If
Else
'Falls kein Pfad zu einer Eingabedatei angegeben wurde, eine Fehlermeldung anzeigen
Call ShowParamsError
End If
'-----------------------------------
' Unterprogramme
'-----------------------------------
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
'/ Gewünschte Änderungen an der CSV-Datei ausführen
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
Sub ProcessCSVFileContent
On Error Resume Next
Dim objActiveSheet, objColumn, objCell
Dim strColumnHeader, strCol1Sample, strCodeNum, arrDateParts
'Aktives Arbeitsblatt holen
Set objActiveSheet = objExcel.ActiveSheet
'Zeile 2 und 3 löschen
objActiveSheet.Rows(3).Delete
objActiveSheet.Rows(2).Delete
'Alle Elemente der Löschliste verarbeiten
For Each strColumnHeader In arrColumnsToDelete
'Alle verwendeten Spalten des Arbeitsblatts verarbeiten
For Each objColumn In objActiveSheet.UsedRange.Columns
'Wenn in Zeile 2 der Spalte einer der Spaltentitel aus der
'Löschliste enthalten ist, die komplette Spalte löschen
If objColumn.Cells(2, 1) = strColumnHeader Then
objColumn.Delete
End If
Next
Next
'Stichprobe aus Spalte 1, Zeile 3 entnehmen
strCol1Sample = objActiveSheet.UsedRange.Cells(3, 1)
'Alle Spalten in Zeile 1 verarbeiten
For Each objCell In objActiveSheet.UsedRange.Rows(1).Columns
'Codenummer aus der aktuellen Zelle lesen
strCodeNum = Trim(objCell.Value)
'Codenummern durch Abkürzungen für Inhaltsstoffe ersetzen
'Wenn Spalte 1, Zeile 3 den Wert 2 enthält, wird die Codenummer
'durch den Wert ersetzt, der in der Konfigurationsdatei beim
'Schlüssel <Codenummer>u angegeben ist, ansonsten wird der Wert
'beim Schlüssel <Codenummer> verwendet
If strCol1Sample = "2" And dicCodeNumbers.Exists(strCodeNum & "u") Then
objCell.Value = dicCodeNumbers.Item(strCodeNum & "u")
ElseIf dicCodeNumbers.Exists(strCodeNum) Then
objCell.Value = dicCodeNumbers.Item(strCodeNum)
End If
Next
'Alle Spalten in Zeile 2 verarbeiten
For Each objCell In objActiveSheet.UsedRange.Rows(2).Columns
'Spaltentitel ersetzen
If dicColHeaders.Exists(Trim(objCell.Value)) Then
objCell.Value = dicColHeaders.Item(Trim(objCell.Value))
End If
Next
'Alle Zeilen in Spalte 1 verarbeiten
For Each objCell In objActiveSheet.UsedRange.Columns(1).Rows
'Wenn die Zeilennummer größer als 2 ist...
If objCell.Row > 2 Then
'...Kennungen durch Texte ersetzen
If objCell.Value = "1" Then objCell.Value = "flüssig"
ElseIf objCell.Value = "2" Then objCell.Value = "fest"
End If
Next
'Alle Zeilen in Spalte 4 verarbeiten
For Each objCell In objActiveSheet.UsedRange.Columns(4).Rows
'Wenn die Zeilennummer größer als 2 ist...
If objCell.Row > 2 Then
'...nur die Datumsangabe extrahieren...
arrDateParts = Split(Mid(Trim(objCell.Value), 1, InStr(Trim(objCell.Value), " ") - 1), "/")
'...und das Format von YYYY/MM/DD in DD/MM/YYYY wandeln
If UBound(arrDateParts) = 2 Then
objCell.Value = arrDateParts(2) _
& "/" & arrDateParts(1) _
& "/" & arrDateParts(0)
End If
End If
Next
End Sub
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
'/ Programmparameter einlesen
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
Function ParseCommandLine
On Error Resume Next
'Rückgabewert initialisieren
ParseCommandLine = False
'Abbruch wenn keine Parameter übergeben wurden
If colArgs.Count < 1 Then Exit Function
'Wenn mindestens ein unnamed Parameter übergeben wurde, ...
If colArgs.Unnamed.Count > 0 Then
'...diesen als Pfad zur CSV-Eingabedatei interpretieren und
'den vollständigen Pfad der Datei ermitteln...
strInFile = objFSO.GetAbsolutePathName(colArgs.Unnamed.Item(0))
Else
'...sonst Abbruch und einen Fehler zurückmelden
Exit Function
End If
'Wenn ein named Parameter mit der Bezeichnung fc übergeben wurde, ...
If colArgs.Named.Exists("fc") Then
'...diesen als Pfad zur Konfigurationsdatei interpretieren und
'den vollständigen Pfad der Datei ermitteln...
strConfigFile = objFSO.GetAbsolutePathName(colArgs.Named.Item("fc"))
Else
'...sonst den Pfad zur Konfigurationsdatei aus dem Pfad des Scripts
'und einem Standarddateinamen erzeugen
strConfigFile = objFSO.BuildPath(objFSO.GetParentFolderName(strScriptPath), strConfigFile)
End If
'Wenn ein named Parameter mit der Bezeichnung fo übergeben wurde, ...
If colArgs.Named.Exists("fo") Then
'...diesen als Pfad zur Ausgabedatei interpretieren und
'den vollständigen Pfad der Datei ermitteln...
strOutFile = objFSO.GetAbsolutePathName(colArgs.Named.Item("fo"))
Else
'...sonst den Pfad zur Ausgabedatei aus Pfad und Name der Eingabedatei
' und einer Ergänzung erzeugen
strOutFile = objFSO.BuildPath(objFSO.GetParentFolderName(strInFile), _
objFSO.GetBaseName(strInFile) _
& "_Neu" _
& "." _
& objFSO.GetExtensionName(strInFile))
End If
'Wenn wir bis hierher kommen, haben wir alle benötigten Parameter
'und können mit einer Erfolgsmeldung zurückspringen
ParseCommandLine = True
End Function
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
'/ Konfigurationsdatei einlesen
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
Function LoadConfigFile(strFileName)
On Error Resume Next
Dim objInStream, strLine, arrTokens, intFilePart
'Steuerflag initialisieren
intFilePart = 1
'Konfigurationsdatei zum Lesen öffnen
'Die Datei muss aus drei Abschnitten bestehen, die durch genau eine
'Leerzeile voneinander getrennt sind.
'Der erste Abschnitt enthält die Titel der Spalten, die gelöscht werden sollen.
'Um auch leere Spaltentitel angeben zu können, müssen diese als zwei aufeinander-
'folgende Anführungszeichen eingetragen werden
'Der zweite Abschnitt muss die Zuordnung Codenummern->Inhaltsstoffe enthalten
'und der dritte Abschnitt die Zuordnung Spaltentitel->Neuer Spaltentitel
Set objInStream = objFSO.OpenTextFile(strFileName, ForReading, False)
If Err.Number <> 0 Then
LoadConfigFile = False
Exit Function
End If
'Bis zum Dateiende lesen
Do While Not objInStream.AtEndOfStream
'Eine Zeile lesen
strLine = Trim(objInStream.ReadLine)
'Bei einer Leerzeile wird der aktuelle Dateiabschnitt als beendet angesehen
If strLine = "" Then
intFilePart = intFilePart + 1
'Kommentarzeilen werden überlesen
ElseIf Left(strLine, 1) = "#" Then
'Nichts machen
'Dateiabschnitt 1 parsen
ElseIf intFilePart = 1 Then
'Neues Element im Array anlegen
ReDim Preserve arrColumnsToDelete(UBound(arrColumnsToDelete) + 1)
'Zeileninhalt speichern und dabei die umgebenden Anführungszeichen löschen
arrColumnsToDelete(UBound(arrColumnsToDelete)) = UnQuote(strLine)
'Dateiabschnitte 2 und 3 parsen
Else
'Die Zeile anhand des Gleichheitszeichens in Tokens zerlegen
arrTokens = Split(strLine, "=")
'Falls in der Konfigurationsdatei nach dem Gleichheitszeichen nichts steht,
'soll der zu dem Schlüssel gehörende Wert wohl gelöscht werden
If UBound(arrTokens) = 0 Then
ReDim Preserve arrTokens(1)
arrTokens(1) = ""
End If
'Dafür sorgen, dass das Gleichheitszeichen
'auch Bestandteil des Wertes sein kann...
arrTokens(1) = Join(Slice(arrTokens, 1, UBound(arrTokens)), "=")
'...und die Schlüssel/Wert-Paare aus den verschiedenen Dateiabschnitten
'in verschiedenen Dictionary-Objekten speichern, aber nur, wenn der
'Schlüssel noch nicht im Dictionary existiert
Select Case intFilePart
Case 2
If Not dicCodeNumbers.Exists(Trim(arrTokens(0))) Then
Call dicCodeNumbers.Add(Trim(arrTokens(0)), Trim(arrTokens(1)))
End If
Case 3
If Not dicColHeaders.Exists(Trim(arrTokens(0))) Then
Call dicColHeaders.Add(Trim(arrTokens(0)), Trim(arrTokens(1)))
End If
End Select
End If
Loop
'Eingabedatei schließen
objInStream.Close
'Ergebnis zurückliefern
LoadConfigFile = True
End Function
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
'/ CSV-Datei in Excel laden
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
Function OpenCSVFile(strFileName)
On Error Resume Next
Dim objActiveWorkBook, objActiveSheet
Dim arrColumnFormats(200), intCnt, bolResult
bolResult = False
'Array mit Format-Codes für die ersten 200 Spalten erzeugen
'Die Spalten sollen als Nur-Text eingelesen werden
For intCnt = 0 to UBound(arrColumnFormats)
arrColumnFormats(intCnt) = 2 'xlTextFormat
Next
'Excel-Instanz starten
Set objExcel = CreateObject("Excel.Application")
'Unsichtbar schalten
objExcel.Visible = False
'Beim Schließen von Excel keine Nachfrage zum Speichern anzeigen
objExcel.DisplayAlerts = False
'Arbeitsmappe hinzufügen und aktives Arbeitsblatt holen
Set objActiveWorkBook = objExcel.WorkBooks.Add(-4167) 'xlWBATWorksheet
Set objActiveSheet = objActiveWorkBook.WorkSheets(1)
'CSV-Datei einlesen
With objActiveSheet.QueryTables.Add("TEXT;" & strFileName, objActiveSheet.Range("A1"))
.Name = "Messergebnis"
.FieldNames = False 'Erste Zeile enthält keine Spaltennamen
.RowNumbers = False 'Erste Spalte enhält keine Zeilennummern
.FillAdjacentFormulas = False 'Nicht nach Formeln suchen, die aktualisiert werden müssen
.PreserveFormatting = False 'Die Formatierung der Zellen in den ersten 5 Zeilen nicht für neue Zeilen übernehmen
.RefreshStyle = 0 'xlOverwriteCells, Zelleninhalte überschreiben
.AdjustColumnWidth = True 'Spaltenbreite an den Inhalt anpassen
.RefreshPeriod = 0 'Automatische Aktualisierung der Daten ausschalten
.TextFilePlatform = 1252 'Codepage der Eingabedatei, 1252 ist die Standard-Windows-Codpage, 65001 steht für UTF-8
.TextFileStartRow = 1 'Daten ab Zeile 1 einlesen
.TextFileParseType = 1 'xlDelimited, Spalteninhalte sind durch Trennzeichen separiert
.TextFileTabDelimiter = False 'Tab ist erlaubtes Feld-Trennzeichen: nein
.TextFileSemicolonDelimiter = False 'Semikolon ist erlaubtes Feld-Trennzeichen: nein
.TextFileCommaDelimiter = True 'Komma ist erlaubtes Feld-Trennzeichen: ja
.TextFileSpaceDelimiter = False 'Leerzeichen ist erlaubtes Feld-Trennzeichen: nein
.TextFileOtherDelimiter = "" 'Kein alternatives Trennzeichen
.TextFileConsecutiveDelimiter = False 'Aufeinanderfolgende Trennzeichen nicht als ein Trennzeichen behandeln
.TextFileTextQualifier = 1 'xlTextQualifierDoubleQuote, Anführungszeichen sind Begrenzer für Text-Inhalte
.TextFileColumnDataTypes = arrColumnFormats 'Format-Codes der Spalten
.TextFileTrailingMinusNumbers = False 'Auch negative Zahlen als Text importieren
'Wenn bisher kein Fehler aufgetreten ist, die Datei laden
'und warten bis der Ladevorgang abgeschlossen ist
If Err.Number = 0 Then
bolResult = .Refresh(False)
End If
End With
'Wenn der Ladevorgang fehlschlug, Excel beenden und das Objekt vernichten
If Err.Number <> 0 Or Not bolResult Then
objExcel.Quit
objExcel = Nothing
End If
'Ergebnis zurückliefern
OpenCSVFile = (Err.Number = 0 And bolResult)
End Function
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
'/ Geänderte CSV-Datei mit eigener Routine speichern
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
Sub SaveCSVFile(strFilePath)
On Error Resume Next
Dim objOutStream
Dim objActiveSheet, objRow, objCell
Dim strLine
'Aktives Arbeitsblatt holen
Set objActiveSheet = objExcel.ActiveSheet
'Zieldatei öffnen
Set objOutStream = objFSO.CreateTextFile(strFilePath, True)
'Alle Zeilen des Arbeitsblatts verarbeiten
For Each objRow In objActiveSheet.UsedRange.Rows
strLine = ""
'Alle Zellen der aktuellen Zeile verarbeiten
'und zu einem String zusammensetzen
For Each objCell In objRow.Cells
'Den Inhalt der nicht-leeren Zellen aus Zeile 2 in Anführungszeichen
'einschließen, alle anderen Zellen ohne Anführungszeichen speichern
If objCell.Row = 2 And Trim(objCell.Value) <> "" Then
strLine = strLine & """" & Trim(objCell.Value) & """;"
Else
strLine = strLine & Trim(objCell.Value) & ";"
End If
Next
'Zeileninhalt in die Zieldatei schreiben,
'dabei das Semikolon am Zeilenende entfernen
objOutStream.WriteLine Left(strLine, Len(strLine) - 1)
Next
'Zieldatei schließen
objOutStream.Close
End Sub
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
'/ Entfernt umschließende Anführungszeichen
'/ aus einem String
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
Function UnQuote(ByRef strInString)
On Error Resume Next
If Left(strInString, 1) = """" And Right(strInString, 1) = """" Then
If Len(strInString) = 2 Then
UnQuote = ""
Else
UnQuote = Mid(strInString, 2, Len(strInString) - 2)
End If
Else
UnQuote = strInString
End If
End Function
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
'/ Erzeugt aus einem Teilbereich des Eingabearrays
'/ ein neues Array
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
Function Slice(ByRef arrIn, intStart, intEnd)
On Error Resume Next
Dim intCnt, arrOut
arrOut = Array()
If intEnd >= intStart Then
ReDim arrOut(intEnd - intStart)
For intCnt = intStart To intEnd
arrOut(intCnt - intStart) = arrIn(intCnt)
Next
End If
Slice = arrOut
End Function
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
'/ Fehlermeldung für fehlende Parameter anzeigen
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
Sub ShowParamsError
On Error Resume Next
MsgBox "Sie müssen den Pfad zu einer Eingabedatei übergeben.", _
vbCritical+vbOKOnly, _
strScriptName
End Sub
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
'/ Fehlermeldung für Dateiladefehler anzeigen
'/ / / / / / / / / / / / / / / / / / / / / / / / / / /
Sub ShowFileLoadError(strFilePath)
On Error Resume Next
MsgBox "Fehler beim Laden der Datei" & vbCrLf _
& vbCrLf _
& strFilePath & vbCrLf, _
vbCritical+vbOKOnly, _
strScriptName
End Sub
Einstellungen
In Zeile 31 wird der Name der Konfigurationsdatei festgelegt, der die Informationen für die auszuführenden Änderungen enthält. Die Datei wird im gleichen Verzeichnis erwartet, in dem das Script gespeichert ist.Anwendung
Den Code bitte mit der Dateiendungvbs
speichern. Die Eingabedateien können per Drag&Drop (Icon der Eingabedatei mit der Maus auf das Icon des Scripts ziehen und "fallen lassen") an das Script übergeben werden oder durch folgende Kommandozeile von einer Konsole aus:cscript /nologo "PfadUndNameDesScripts.vbs" "PfadUndNameDerEingabedatei.csv"
NameDerEingabedatei_Neu.csv
.Es wird davon ausgegangen, dass die Eingabedateien mit der Codepage 1252 codiert sind.
Bemerkung
Deine Messwerte werden mit einem Punkt als Dezimaltrennzeichen gespeichert. Wenn Du die Ausgabedaten mit Excel weiterverarbeiten musst, ist das ungünstig, da Excel auf einem Windowssystem mit deutschem Gebietsschema solche Werte teilweise als Datum interpretiert. In dem Fall muss man die Speicherroutine nochmal überarbeiten. Das Einlesen mit meinem Script funktioniert, weil die Einleseroutine Excel mitteilt, dass die ersten 200 Spalten (sollte auch für die Zukunft ausreichen ) als Text formatiert sind.Gruß
Friemler
[EDIT]
Änderungen
- Script an neue Anforderungen angepasst (Konfigurationsdatei auswerten)
- Die Aufzählung der zu löschenden Spalten wird jetzt ebenfalls aus der Konfigurationsdatei gelesen
- Das Script überliest jetzt Zeilen, die mit dem Zeichen # beginnen. Somit können auf diese Weise Kommentare in die Konfigurationsdatei eingebaut werden.
Hinweis zum Aufbau der Konfigurationsdatei
Die Datei muss aus drei Abschnitten bestehen, die durch genau eine Leerzeile voneinander getrennt sind.- Der erste Abschnitt muss die Titel der Spalten enthalten, die gelöscht werden sollen. Um auch leere Spaltentitel angeben zu können, müssen diese als zwei aufeinanderfolgende Anführungszeichen eingetragen werden
- Der zweite Abschnitt muss die Zuordnung Codenummern->Inhaltsstoffe enthalten
- Der dritte Abschnitt muss die Zuordnung Spaltentitel->Neuer Spaltentitel enthalten
Beispiel
#
# Das ist ein Kommentar
# Hier beginnt Abschnitt 1
#
R_Type1
R_Type2
R_No.
Pos
Age
Unit
Sex
S_Date
C1
C2
C3
C4
C5
Cup
Ope_D
N
""
#
# Hier beginnt Abschnitt 2
#
21=LACT
22=ACT
40=FACT
40u=uFACT
57=DIMER
#
# Hier beginnt Abschnitt 3
#
#S_Type=BezeichnungA
# oder auch
S_Type=S_Type=BezeichnungA
S_No.=BezeichnungB
S_ID=BezeichnungC
M_Date=Datum
[EDIT2]
Änderungen
Das Script kann jetzt mit folgender Befehlszeile aufgerufen werdencscript /nologo "PfadZumScript.vbs" "PfadZurEingabedatei.csv" /fc:"PfadZurKonfigurationsdatei" /fo:"PfadZurAusgabedatei"
/fc:...
und /fo:...
sind optional. Wenn Sie fehlen, verhält sich das Script genauso wie bisher.[/EDIT2]
{EDIT3]
Änderungen
In der Konfigurationsdatei können für die Ersetzung von Codenummern jetzt Schlüssel/Wert-Paare angegeben werden, bei denen einu
an den Schlüssel (die zu ersetzende Codenummer) angehängt ist. Steht (nach dem Löschen von Zeile 1 und 3 sowie dem Löschen der in der Konfigurationsdatei angegebenen Spalten) in Spalte 1, Zeile 3 der Wert 2
, wird der Wert beim Schlüssel mit angehängtem u
zur Ersetzung verwendet, ansonsten der Wert beim Schlüssel ohne angehängtes u
.[/EDIT3]
Hallo,
ich habe das Script in meinem Posting oben entsprechend Deinen Wünschen angepasst. Die Titel der zu löschenden Spalten werden jetzt auch aus der Konfigurationsdatei gelesen. Siehe auch mein [EDIT] des obigen Postings.
Ich bin mir aber nicht sicher, ob ich Dich richtig verstanden habe bzgl. der Ersetzung von Zelleninhalten. In der derzeitigen Version des Script werden aus der Konfigurationsdatei Schlüssel-Werte-Paare gelesen und der String links des Gleichheitszeichens durch den rechts des Gleichheitszeichens ersetzt. Ist das so korrekt?
[EDIT]
Ich habe oben noch eine Änderung nachgeschoben, durch die Du in der Konfigurationsdatei auch Einträge nach folgendem Schema vornehmen kannst:
Damit hat sich das Problem erledigt.
[/EDIT]
Gruß
Friemler
ich habe das Script in meinem Posting oben entsprechend Deinen Wünschen angepasst. Die Titel der zu löschenden Spalten werden jetzt auch aus der Konfigurationsdatei gelesen. Siehe auch mein [EDIT] des obigen Postings.
Ich bin mir aber nicht sicher, ob ich Dich richtig verstanden habe bzgl. der Ersetzung von Zelleninhalten. In der derzeitigen Version des Script werden aus der Konfigurationsdatei Schlüssel-Werte-Paare gelesen und der String links des Gleichheitszeichens durch den rechts des Gleichheitszeichens ersetzt. Ist das so korrekt?
[EDIT]
Ich habe oben noch eine Änderung nachgeschoben, durch die Du in der Konfigurationsdatei auch Einträge nach folgendem Schema vornehmen kannst:
S_Type=S_Type=BezeichnungA
[/EDIT]
Gruß
Friemler
Sicher, Du kannst Dich bei Problemen gerne nochmal melden. Da ich am Wochenende arbeiten muss, kann es aber sein, dass ich mich nicht direkt melde.
Hole Dir bitte nochmals die aktuelle Version des Scripts. Da ich mehrmals Kleinigkeiten verändert habe, könnte es sein, dass Du nicht die aktuellste Version hast.
Auch Dir ein schönes Wochenende.
Gruß
Friemler
Hole Dir bitte nochmals die aktuelle Version des Scripts. Da ich mehrmals Kleinigkeiten verändert habe, könnte es sein, dass Du nicht die aktuellste Version hast.
Auch Dir ein schönes Wochenende.
Gruß
Friemler
Moin,
falls es doch noch via Powershell benötigt wird, und ich das soweit richtig interpretiert habe:
(Kommentare im Code)
Grüße Uwe
falls es doch noch via Powershell benötigt wird, und ich das soweit richtig interpretiert habe:
(Kommentare im Code)
# -------- Variablen --------------
# Inputfile
$fileIN = "C:\temp\data.csv"
# Outputfile
$fileOUT = "C:\temp\data_final.csv"
# Spalten die nicht mit übernommen werden sollen
$skipColumns = @("1","2")
# Ersetzungen in Zeile 1
$replaceTokensRow1 = @{'21'='LACT';'22'='ACT'}
# Ersetzungen in Zeile 2
$replaceTokensRow2 = @{'S_Type'='BezeichnungA';'S_No.'='BezeichnungB'}
# ---------------------------------
# File einlesen
$content = gc $fileIN
# doppelte Komma's entfernen
$content = $content.Replace(",,",",")
# Anzahl der Spalten ermitteln
$colCount = $content.Split(",").Length
# Temporäre Spaltenbeschriftungen erstellen
$header = (1..$colCount) -join ","
# Inhalt neu zusammenstellen
$arr = @(); $arr +=$header; $arr +=$content; $arr +=$content[3..($content.Length - 1)]
# Objekt aus Inhalt erstellen
$obj = $arr | ConvertFrom-Csv -Delimiter "," | select * -ExcludeProperty $skipColumns
#Spaltennamen in Zeile 1 ersetzen
1..$colCount | %{if ($obj.$_ -ne $null -and $replaceTokensRow1.Contains($obj.$_)){$obj.$_ = $replaceTokensRow1[$obj[0].$_] }}
#Spaltennamen in Zeile 2 ersetzen
1..$colCount | %{if ($obj[1].$_ -ne $null -and $replaceTokensRow2.Contains($obj[1].$_)){$obj[1].$_ = $replaceTokensRow2[$obj[1].$_] }}
# Export in CSV
$obj | ConvertTo-Csv -Delimiter ";" -NoTypeInformation | select -Skip 1 | Set-Content $fileOUT
Grüße Uwe