/\/\o\/\/ PowerShelled
This blog has moved to http://ThePowerShellGuy.com Greetings /\/\o\/\/
$PreviousItems = (" PowerShell and Active Directory Part 3 (UserProper... "," PowerShell and MOM 2005 "," PowerShell and Active Directory Part 2 "," PowerShel and Active Directory Part 1 "," TechEd RoundUp "," A big hurray for the Scripting Guy ! "," Teched "," PowerShell Tab Completion Part 4 "," The DFO Show - Introducing Windows PowerShell "," PowerShell Tab Completion Part 3 of more ? "," ")
Wednesday, July 05, 2006
Working with Fixed Length delimited Text files in PowerShell
As on the microsoft.public.windows.powerShell a Question was asked about working with a Fixed Length delimited Text file (Columns lined-out by spaces)Working with fixed length is a bit tricky, but you can use ADO to do this as I already did the functions to get textfile in my CSV series I came up with this Going from my CSV examples here :
working with csv files in msh part one and Part two .
In this post I will show that a one word change in those two functions and creating a schema.ini file will enable us to get Fixed Length delimited Text files into a dataset and from there into a CSV file and from the CSV file into an PsObject to work with.
Here are the 2 functions from the first CSV post on my blog we will use for this :
# I modified the connect-Csv (changed : FMT=FixedLength) to connect-txt function
function connect-txt {
Param ($Path = ".")
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$path;Extended properties='text;HDR=Yes;FMT=FixedLength'"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
$Tables = $Conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)
$Tables | select TABLE_NAME
}
# and the get-DataTable did stay the same :
function get-DataTable {
Param ($name )
$cmd = new-object System.Data.OleDb.OleDbCommand("Select * from [$name]",$Conn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
[void]$da.fill($dt)
$dt.TableName = $name.split("#")[0]
return $dt
}
function connect-txt {
Param ($Path = ".")
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$path;Extended properties='text;HDR=Yes;FMT=FixedLength'"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
$Tables = $Conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)
$Tables | select TABLE_NAME
}
# and the get-DataTable did stay the same :
function get-DataTable {
Param ($name )
$cmd = new-object System.Data.OleDb.OleDbCommand("Select * from [$name]",$Conn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
[void]$da.fill($dt)
$dt.TableName = $name.split("#")[0]
return $dt
}
You can past this 2 functions (more info in CSV posts) onto your commandline and you are ready to go.
Now for a Fixed Length file you need an SCHEMA.INI file in the same directory, and this file needs to have an entry [filename] and it needs to define the colums so lets say we have a textfile like this :
TEST.TXT
Name Value
var1 Bar
Var123 Foo
Var2 FooBar
var1 Bar
Var123 Foo
Var2 FooBar
and we make a schema.ini file defining the columns like this :
SCHEMA.INI
[test.txt]
Format=FixedLength
Col1=Name Text Width 9
Col2=Value Text Width 6
Format=FixedLength
Col1=Name Text Width 9
Col2=Value Text Width 6
now after you past in 2 former functions you can do this :
# Connect to directory and load connection in $global:Conn
PoSH>. connect-txt
TABLE_NAME
----------
Test#txt
# and get a datatable from it
PoSH>get-datatable Test#txt
Name Value
---- ------
var1 Bar
Var123 Foo
Var2 FooBar
# you can work with dataset :
(get-datatable Test#txt).get_Rows() | select name
# or you now can export it to CSV
PoSH>(get-datatable Test#txt).get_Rows() | export-csv -not Test.csv
# and import it again with Import-csv to get an PSObject :
PoSH>import-csv Test.csv
Name Value
---- ------
var1 Bar
Var123 Foo
Var2 FooBar
# and save to an object if you find this easier to work with :
PoSH>import-csv Test.csv | select name
NAME
----
var1
Var123
Var2
PoSH>$Test = import-csv Test.csv
PoSH>. connect-txt
TABLE_NAME
----------
Test#txt
# and get a datatable from it
PoSH>get-datatable Test#txt
Name Value
---- ------
var1 Bar
Var123 Foo
Var2 FooBar
# you can work with dataset :
(get-datatable Test#txt).get_Rows() | select name
# or you now can export it to CSV
PoSH>(get-datatable Test#txt).get_Rows() | export-csv -not Test.csv
# and import it again with Import-csv to get an PSObject :
PoSH>import-csv Test.csv
Name Value
---- ------
var1 Bar
Var123 Foo
Var2 FooBar
# and save to an object if you find this easier to work with :
PoSH>import-csv Test.csv | select name
NAME
----
var1
Var123
Var2
PoSH>$Test = import-csv Test.csv
(changed coloring for # in filenames)
So the connect-csv function with one property changed can now do Fixed length files combined with an Schema.ini file where we can configure the columns on a file by file base.(and a import-TabDelimited version would be not hard to do from here on so I leave that up to you ;-) )
the save and import to CSV file will translate the Dataset to an PsObject with properties for the columns, so you can work with it like an object.(but the DataSet is also great to use see the CSV and other DataSet and DataTable examples on my blog (search also for out-datagrid to show it in a GUI).
an excelent article about using textfiles with ADO can be found here :( It's hidden here :MSDN Library > Web Development > Scripting but ... It's the Scripting Guy again with another great article) : Much ADO About Text Files
those to functions well give us a powerfull way to work with files lined out with spaces from PowerShell and combine and or export them (see also other examples on blog e.g. PowerShell out-DataGrid update and more Dataset utilities for a GUI and More DataTables and joining them in Monad for some combining data ).
Enjoy,
Greetings /\/\o\/\/
Tags : Monad PowerShell
