PowerShell: CSV to XML

I think all of you know the following issue: A colleague or customer gave you a data file and you want to open the file with your existing programs. And in most cases the format of the file does not match with the required format of the application you want to use. Therefore you have to convert the file into the right format.

There exist many ways to convert data. Within this article, I want to show you how the Windows PowerShell may be used for the conversion of a CSV file into a XML file. Furthermore you will learn how you can use a data filter, to select only the data you are interested in.

 
Execute a PowerShell command

To execute a PowerShell command, you can use the following code:

PowerShell -NoExit -Command „& {<command>}“

 
The NoExit parameter is optional. If you use the parameter the PowerShell editor stays open. Otherwise the editor is closed automatically after the command was executed.

 
Convert CSV to XML

Two cmdlets are necessary to convert the file. The cmdlet Import-CSV is used to read the CSF file and the cmdlet Export-CliXML is used to write data into a XML file. The following code shows the according full command. The two cmdlets are connected with the pipe character to pipeline the date from the first cmdlet to the second one.

Import-CSV C:\myfile.csv | Export-CliXML C:\myfile.xml

 
To following code shows the complete PowerShell command.

PowerShell -NoExit -Command „& { Import-CSV C:\myfile.csv | Export-CliXML C:\myfile.xml }“

 

Filter data during file conversion

It is also possible to filter the date during the file conversion. The following date shows an example of a CSF file with three data sets.

FirstName,LastName,Age

John,Doe,35

Jane,Doe,37

William,Smith,74

 
If you want to filter data, you may use the Where-Object cmdlet. It is also possible to write where because this is an allowed alias for the cmdlet. Within the where statement you have to use comparison operators. The following operators exist.

Operator Description
-lt Less than
-le Less than or equal   to
-gt Greater than
-ge Greater than or   equal to
-eq Equal to
-ne Not equal to
-like Uses wildcards for   pattern matching

 
For example, if you want to select all data sets with persons that are younger than 40, you can write the following where command:

where Age -lt 40

 
This data filter may be easily used within your conversion command. It has to be inserted between the import and export cmdlets.

Import-CSV C:\myfile.csv | where Age -lt 40 | Export-CliXML C:\myfile.xml

 
And now you can create the full PowerShell command with import, export and filter functions.

PowerShell -NoExit -Command „& { Import-CSV C:\myfile.csv | where Age -lt 40 | Export-CliXML C:\myfile.xml }“

 
Summary
The Windows PowerShell offers an easy way to convert data from one file format into another. Furthermore an additional data filter may be used during the file conversion.

Advertisements
Dieser Beitrag wurde unter PowerShell abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s