Vista Tutorial - parsing csv files with fields that may contain commas

Hi,

What is the best way to parse a csv comma delimeted file which may have
commas in the fields. If there are commas in the field, it is enclosed in
double quotes, ie.

field1,field2,field3
"field1, abc",field2,field3


So, in the second row, the first field would be: "field1, abc"

Thanks in advance,

You can use the Import-Csv Cmdlet to assign the contents of the csv file to
a variable, then you can retrieve the fields as properties of the variable.

$data = import-csv data.csv
$data | select-onject field1
$data | select-object field1 | format-table -hide
$data | foreach-object {$_.field1}--
Kiron

I would add headers to the CSV
Here is my test file
====== TEST FILE =======
Header1,Header2,Header3
this is field1,"fieldtwo,fieldtwo",this is field 3
====== TEST FILE =======

You can then use import-csv to import for parsing
PS> $csvimporttest = import-csv test.csv
PS> $csvimporttest
Header1 Header2
Header3
------ ------
------
this is field1 fieldtwo,fieldtwo
this is field 3

As you can see... the quotes worked as expected.

Then to parse like this
===== CODE =====
$csvimporttest = import-csv test.csv
foreach($object in $csvimporttest)
{
write-host $object.header1
write-host $object.header2
write-host $object.header3
}
===== CODE =====

"Frank" <Frank@discussions.microsoft.com> wrote in message
news:FED131FD-F83D-4E0B-BE0E-7C314E314855@microsoft.com...
> Hi,
>
> What is the best way to parse a csv comma delimeted file which may have
> commas in the fields. If there are commas in the field, it is enclosed in
> double quotes, ie.
>
> field1,field2,field3
> "field1, abc",field2,field3
>
>
> So, in the second row, the first field would be: "field1, abc"
>
> Thanks in advance,
>
>

I seem to get errors unless all fields are there, for example in my first
line, I have the right amount of commas but there is no third field:

field1,"field 2,sfd",
field1,field2,field3

I would get the error:

$test =import-csv test.csv
Import-Csv : Cannot process argument because the value of argument "name" is
invalid. Change the value of the "name" argument and run the operation again.
At line:1 char:18
+ $test =import-csv <<<< test.csv


I have verified that if there was a third field, it would work. Is there a
way around this?

Thanks,



"Brandon Shell" wrote:

> I would add headers to the CSV
> Here is my test file
> ====== TEST FILE =======
> Header1,Header2,Header3
> this is field1,"fieldtwo,fieldtwo",this is field 3
> ====== TEST FILE =======
>
> You can then use import-csv to import for parsing
> PS> $csvimporttest = import-csv test.csv
> PS> $csvimporttest
> Header1 Header2
> Header3
> ------ ------
> ------
> this is field1 fieldtwo,fieldtwo
> this is field 3
>
> As you can see... the quotes worked as expected.
>
> Then to parse like this
> ===== CODE =====
> $csvimporttest = import-csv test.csv
> foreach($object in $csvimporttest)
> {
> write-host $object.header1
> write-host $object.header2
> write-host $object.header3
> }
> ===== CODE =====
>
> "Frank" <Frank@discussions.microsoft.com> wrote in message
> news:FED131FD-F83D-4E0B-BE0E-7C314E314855@microsoft.com...
> > Hi,
> >
> > What is the best way to parse a csv comma delimeted file which may have
> > commas in the fields. If there are commas in the field, it is enclosed in
> > double quotes, ie.
> >
> > field1,field2,field3
> > "field1, abc",field2,field3
> >
> >
> > So, in the second row, the first field would be: "field1, abc"
> >
> > Thanks in advance,
> >
> >
>
>

"Frank"

>I seem to get errors unless all
> fields are there, for example in my first
> line, I have the right amount of commas
> but there is no third field:
>
> field1,"field 2,sfd",
> field1,field2,field3
>

Perhaps Microsoft's Log Parser 2.2

PS> LogParser.exe "SELECT field1 AS Item1 ,field2 AS Item2 , field3 AS
Item3 FRO
M testComma.csv" -i:csv -statsff -headerRowff
Item1 Item2 Item3
----------- ----------- ------
field1 field2 field3
field1, abc field2 field3
field1 field 2,sfd <NULL>
field1 field2 field3
PS>

Or perhaps:

PS> LogParser.exe "SELECT field1 AS Item1 ,field2 AS Item2 ,
REPLACE_IF_NULL(fie
ld3,'Yep blank') AS Item3 FROM
testComma.csv" -i:csv -statsff -headerRowff -
o:nat -colSep:" Field blank? "
Item1 Item2 Item3
----------- ----------- ---------
field1 Field blank? field2 Field blank? field3
field1, abc Field blank? field2 Field blank? field3
field1 Field blank? field 2,sfd Field blank? Yep blank
field1 Field blank? field2 Field blank? field3
PS>

Just another way!

It should work if your header field is filled out.

header1,header2,header3
field1,"field 2,sfd",
field1,field2,field3

9# Import-Csv $pwd\test.csv

header1 header2 header3
------- ------- -------
field1 field 2,sfd
field1 field2 field3

"Frank" <Frank@discussions.microsoft.com> wrote in message
news:BADF24EE-5D76-4152-8FF6-7EFD0F47CA42@microsoft.com...
>I seem to get errors unless all fields are there, for example in my first
> line, I have the right amount of commas but there is no third field:
>
> field1,"field 2,sfd",
> field1,field2,field3
>
> I would get the error:
>
> $test =import-csv test.csv
> Import-Csv : Cannot process argument because the value of argument "name"
> is
> invalid. Change the value of the "name" argument and run the operation
> again.
> At line:1 char:18
> + $test =import-csv <<<< test.csv
>
>
> I have verified that if there was a third field, it would work. Is there
> a
> way around this?
>
> Thanks,
>
>
>
> "Brandon Shell" wrote:
>
>> I would add headers to the CSV
>> Here is my test file
>> ====== TEST FILE =======
>> Header1,Header2,Header3
>> this is field1,"fieldtwo,fieldtwo",this is field 3
>> ====== TEST FILE =======
>>
>> You can then use import-csv to import for parsing
>> PS> $csvimporttest = import-csv test.csv
>> PS> $csvimporttest
>> Header1 Header2
>> Header3
>> ------ ------
>> ------
>> this is field1 fieldtwo,fieldtwo
>> this is field 3
>>
>> As you can see... the quotes worked as expected.
>>
>> Then to parse like this
>> ===== CODE =====
>> $csvimporttest = import-csv test.csv
>> foreach($object in $csvimporttest)
>> {
>> write-host $object.header1
>> write-host $object.header2
>> write-host $object.header3
>> }
>> ===== CODE =====
>>
>> "Frank" <Frank@discussions.microsoft.com> wrote in message
>> news:FED131FD-F83D-4E0B-BE0E-7C314E314855@microsoft.com...
>> > Hi,
>> >
>> > What is the best way to parse a csv comma delimeted file which may have
>> > commas in the fields. If there are commas in the field, it is enclosed
>> > in
>> > double quotes, ie.
>> >
>> > field1,field2,field3
>> > "field1, abc",field2,field3
>> >
>> >
>> > So, in the second row, the first field would be: "field1, abc"
>> >
>> > Thanks in advance,
>> >
>> >
>>
>>

Brandon,

You were absolutely correct. I guess I just have to make sure that the
headers are there. Before, the first record only had the first 2 fields.

Thanks,


"Brandon Shell" wrote:

> It should work if your header field is filled out.
>
> header1,header2,header3
> field1,"field 2,sfd",
> field1,field2,field3
>
> 9# Import-Csv $pwd\test.csv
>
> header1 header2 header3
> ------- ------- -------
> field1 field 2,sfd
> field1 field2 field3
>
> "Frank" <Frank@discussions.microsoft.com> wrote in message
> news:BADF24EE-5D76-4152-8FF6-7EFD0F47CA42@microsoft.com...
> >I seem to get errors unless all fields are there, for example in my first
> > line, I have the right amount of commas but there is no third field:
> >
> > field1,"field 2,sfd",
> > field1,field2,field3
> >
> > I would get the error:
> >
> > $test =import-csv test.csv
> > Import-Csv : Cannot process argument because the value of argument "name"
> > is
> > invalid. Change the value of the "name" argument and run the operation
> > again.
> > At line:1 char:18
> > + $test =import-csv <<<< test.csv
> >
> >
> > I have verified that if there was a third field, it would work. Is there
> > a
> > way around this?
> >
> > Thanks,
> >
> >
> >
> > "Brandon Shell" wrote:
> >
> >> I would add headers to the CSV
> >> Here is my test file
> >> ====== TEST FILE =======
> >> Header1,Header2,Header3
> >> this is field1,"fieldtwo,fieldtwo",this is field 3
> >> ====== TEST FILE =======
> >>
> >> You can then use import-csv to import for parsing
> >> PS> $csvimporttest = import-csv test.csv
> >> PS> $csvimporttest
> >> Header1 Header2
> >> Header3
> >> ------ ------
> >> ------
> >> this is field1 fieldtwo,fieldtwo
> >> this is field 3
> >>
> >> As you can see... the quotes worked as expected.
> >>
> >> Then to parse like this
> >> ===== CODE =====
> >> $csvimporttest = import-csv test.csv
> >> foreach($object in $csvimporttest)
> >> {
> >> write-host $object.header1
> >> write-host $object.header2
> >> write-host $object.header3
> >> }
> >> ===== CODE =====
> >>
> >> "Frank" <Frank@discussions.microsoft.com> wrote in message
> >> news:FED131FD-F83D-4E0B-BE0E-7C314E314855@microsoft.com...
> >> > Hi,
> >> >
> >> > What is the best way to parse a csv comma delimeted file which may have
> >> > commas in the fields. If there are commas in the field, it is enclosed
> >> > in
> >> > double quotes, ie.
> >> >
> >> > field1,field2,field3
> >> > "field1, abc",field2,field3
> >> >
> >> >
> >> > So, in the second row, the first field would be: "field1, abc"
> >> >
> >> > Thanks in advance,
> >> >
> >> >
> >>
> >>
>
>