parsing csv files with fields that may contain commas Frank 12.07.2007 19:00:01 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,

Re: parsing csv files with fields that may contain commas "Kiron" <Kiron[ at ]HighPlainsDrifter.com> 12.07.2007 19:16:29 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

Re: parsing csv files with fields that may contain commas "Brandon Shell" <tshell.mask[ at ]gmail.com> 12.07.2007 19:24:23 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[ at ]discussions.microsoft.com> wrote in message news:FED131FD-F83D-4E0B-BE0E-7C314E314855[ at ]microsoft.com... [Quoted Text] > 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, > >

Re: parsing csv files with fields that may contain commas Frank 12.07.2007 20:02:11 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: [Quoted Text] > 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[ at ]discussions.microsoft.com> wrote in message > news:FED131FD-F83D-4E0B-BE0E-7C314E314855[ at ]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, > > > > > >

Re: parsing csv files with fields that may contain commas "Flowering Weeds" <floweringnoweedsno[ at ]hotmail.com> 12.07.2007 21:46:18 "Frank" [Quoted Text] >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 -stats:off -headerRow:off 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 -stats:off -headerRow:off - 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!

Re: parsing csv files with fields that may contain commas "Brandon Shell" <tshell.mask[ at ]mk.gmail.com> 13.07.2007 01:58:43 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[ at ]discussions.microsoft.com> wrote in message news:BADF24EE-5D76-4152-8FF6-7EFD0F47CA42[ at ]microsoft.com... [Quoted Text] >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[ at ]discussions.microsoft.com> wrote in message >> news:FED131FD-F83D-4E0B-BE0E-7C314E314855[ at ]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, >> > >> > >> >>

Re: parsing csv files with fields that may contain commas Frank 13.07.2007 02:18:02 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: [Quoted Text] > 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[ at ]discussions.microsoft.com> wrote in message > news:BADF24EE-5D76-4152-8FF6-7EFD0F47CA42[ at ]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[ at ]discussions.microsoft.com> wrote in message > >> news:FED131FD-F83D-4E0B-BE0E-7C314E314855[ at ]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, > >> > > >> > > >> > >> > >