/\/\o\/\/ PowerShelled

This blog has moved to http://ThePowerShellGuy.com Greetings /\/\o\/\/
$AtomFeed = ("Atom.xml")
$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
}


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 


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


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


(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 :


Comments: Post a Comment

Links to this post:

Create a Link



<< Home

Archives

October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006  

$Links = ("PowerShell RC1 Docs"," PowerShell RC1 X86"," PowerShell RC1 X64"," Monad GettingStarted guide"," Monad Progamming Guide"," Monad SDK"," Monad videos on Channel 9"," MSH Community Workspace"," scripts.readify.net "," MonadSource"," www.reskit.net"," PowerShell Blog"," Under The Stairs"," computerperformance powershell Home"," proudlyserving"," MSH on wikipedia"," MSHWiki Channel 9"," Keith Hill's Blog"," Precision Computing"," PowerShell for fun"," MSH Memo (Japanese)"," monadblog")

find-blog -about "PowerShell","Monad" | out-Technorati.
find-blog -contains "","" | out-Technorati.
Google
 
Web mow001.blogspot.com

This page is powered by Blogger. Isn't yours?