How to read header-line from CSV file

[Kazendar]

How to get the header-line from CSV file ?
and how to define the second field-name ?
to get all records from that field-name.

 

[Zelandakh]

What have you tried? Are you getting an error message? Please post what you are trying to achieve and how far you have got with your work towards your solution.

 

[Kazendar]

I tried to use the most of Import-CSV , so i write this script import-csv.ps1

I know it looks like a puzle, but at least i did my best effort.

There should be a better way and shorter than this, i hope if we can make it to work with any CSV-file, because realy i did not try with other CSV-files, and many thanks for your support.

Note: the ouput of header line comes as sorted, where it should not be like that.

# import-csv.ps1
#
# read from CSV file-format,
# and write to CSV file-format by sorted header-Names
#
clear-host
set-psdebug -strict

$readFile = "test01.csv"
$arrayLines = @()
# read from CSV file-format
$arrayLines = import-csv $readFile

$arrayLines[0] |
get-member -MemberType NoteProperty |
select-object name |
forEach-object `
-begin {
$arrayHeader = @()
$strHeader = ""
} `
-process {
$arrayHeader += $_.name
$strHeader += $_.name + ","
} `
-end {
$outFile = `
$readFile.substring(0, `
$readFile.lastindexofany(".")) + ".out"
# save header-line to CSV output-file
out-file -filePath $outFile `
-inputObject $strHeader -encoding ASCII -force
}

for ($indx = 0; $indx -le $arrayLines.length-1; $indx += 1)
{
$records = ""

for ($rec = 0; $rec -le $arrayHeader.length-1; $rec += 1)
{
$records += $arrayLines[$indx].($arrayHeader[$rec]) + ","
}

# add the records to CSV output-file
out-file -filePath $outFile `
-inputObject $records -encoding ASCII -append
}

break
#-----------------------------------------------------

input CSV-file test01.csv :
No.,Fname,ID Name,Date,start,finish
1,Bank Hes,BAH,10/09/2008,09:24:01,12:20:56
2,Iran Abe,IRA,01/09/2008,16:14:14,19:24:17
3,Dara Man,DAM,10/09/2008,16:15:47,23:06:33

output-file test01.out:
Date,finish,Fname,ID Name,No.,start,
10/09/2008,12:20:56,Bank Hes,BAH,1,09:24:01,
01/09/2008,19:24:17,Iran Abe,IRA,2,16:14:14,
10/09/2008,23:06:33,Dara Man,DAM,3,16:15:47,

 

[EBGreen]

So you have a CSV, but you don't know what the title of the second column is? I don't think I understand why you need this.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Kazendar]

The main reason for this work, is to get the maximum information from any CSV file-format.

the idea starts from, how to know the field-names of the header-line and their counts from CSV file-format.

I start this work by reading properties and got the numbers.

But the problem becomes when the field-names came as sorted, and their places had changed.

I need to know, how many records for every field-name, and when is possible (minimum, average, maximum, ...).

 

[EBGreen]

Well, here are some ideas:

You can use get-member to essentially get a list of the fields. Just filter for NoteProperties.\

To determine how many records there are for each field would require looking for the ones that -ne "". Import-CSV treats empty fields as "".

Getting statistics could be tricky if you don't control the data. You would essentially have to look at each and every field to see if it is numeric.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Kazendar]

Thanks EBGreen,
I used get-member as:
... | get-member -MemberType NoteProperty | etc...
But i got the field-names as sorted, which is mostly not in their original places.

How to determine how many records there are for each field ?

 

[EBGreen]

If you know the names of the fields, do you care about the original order? As for counting the values for each field, just go through each row and maintain a counter variable for each field and increment it if the contents for that field in that row -ne "".

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[crobin1]

I hate to sound repetitive, but have you looked at LogParser from Microsoft? See thread1619-1513137

 

[dm4ever]

What about using something like this to get the header names

$strFilePath = "C:\Temp\Test.csv"
$arrHeaders = (Get-Content $strFilePath | Select-Object -First 1).Split(",")

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

 

[dm4ever]

...oh and if you want to know the number of records...maybe...

( Import-Csv -Path $strFilePath | Measure-Object -Property $arrHeaders[1] ).Count

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid