Reading Text Files

by MLP at 2012-08-20 14:06:19

I’m having fits trying to do something that I believe should be easy, even for a newbie like me. <Smile>.

I have a text file that is pipe delimited. So that you have an example, the following are the first three lines of the file:

H|Seq_Nbr|Data_Key |Plan_Type |Mod_Type |Plan_Name |Mod_Name |Plan_Desc |Mbr_T |Mbr_A |Mbr_B|Mbr_C |Mbr_D
R|1 |Planned.PlanMast.BU29 |BUAT29 |BUAT29 |SD2910050110 |BUAT29 |M Contemporary Dress|Planned |SD2910050110|BU29 |"" |""
R|2 |Planned.PlanMast.BU41 |BUAT41 |BUAT41 |SD4112345678 |BUAT41 |Whatever |Planned |SD4112345678|BU41|"" |""

So there’s a header row, a separator row, and then the records. I need to parse the file finding all the records that occur in a list of mod_types for a given list of data_keys. For example, given the module types BUAT29 and BUAT41, dnd given the data_keys Planned.PlanMast.BU29 and Planned.PlanMast.BU41, I would want all the records where the module type is either BUAT29 or BUAT41 AND where the data_key is either Planned.PlanMast.BU29 or Planned.PlanMast.BU41.

Taking the problem apart, I started by trying to just read the file in like this:

$file = import-csv c:\imp\ssw\admin\tbl_list_planned.tbl -Delimiter ‘|’

When I then display $file to the screen I get exactly what I would expect, something that contains records like:

H : R
Seq_Nbr : 1
Data_Key : Planned.PlanMast.BU29
Plan_Type : BUAT29
Mod_Type : BUAT29
Plan_Name : SD2910050110
Mod_Name : BUAT29
Plan_Desc : M Contemporary Dress
Mbr_T : Planned
Mbr_A : SD2910050110
Mbr_B : BU29
Mbr_C :
Mbr_D :

When, however, I try to use syntax like

I get nothing!

When I use this syntax:


I get "R" which is what I would expect. So why does $file[1].H return something but $file[1].Plan_Type return nothing?

I’m completely perplexed. Thanks in advance for any suggestions
by poshoholic at 2012-08-20 14:17:26
The answer is in the format of the file you are working with. You have identified ‘|’ as a delimiter, so PowerShell respects your wishes and uses only ‘|’ as a delimiter. Any whitespace surrounding values, even headers, is preserved by PowerShell. As a result, your Plan_Type column is actually called "Plan_Type " (note the trailing space). Therefore, to access it like this, you need to use $file[1].'Plan_Type ’ (again, note the trailing space).

I recommend you either remove the undesired whitespace from the file (which you could do with PowerShell) or you could write a routine that would do the same thing as Import-Csv, but being smart enough to trim whitespace around any headers or values in the process.
by MLP at 2012-08-20 14:28:14
Thank you for your quick reply. I’ll do a little more work before asking any further questions.