Importing comma-delimited csv file, containing commas

Dave · 10-30-2009, 01:20 AM

I'm working on learning how a particular script works. The script imports a comma-delimited text file, and outputs the contents to an HTML table.

The file contains a name field, i.e., "Doe, John" (with comma).

The CSV files are comma-delimited, so I cannot use another character for the delimiter.

Is there a solution to this?

Thanks,
Dave

Village Idiot · 10-30-2009, 01:47 AM

Quote:

Originally Posted by Dave

I'm working on learning how a particular script works. The script imports a comma-delimited text file, and outputs the contents to an HTML table.

The file contains a name field, i.e., "Doe, John" (with comma).

The CSV files are comma-delimited, so I cannot use another character for the delimiter.

Is there a solution to this?

Thanks,
Dave

Encode the commas to their ASCII value (,). HTML pages will display this without any further modification.

Dave · 10-30-2009, 03:45 AM

Thanks! I'll try it, but I know I'll have a follow-up question!

Salathe · 10-30-2009, 11:59 AM

Quote:

Originally Posted by Dave

The file contains a name field, i.e., "Doe, John" (with comma).

Maybe I missed it, but what's the problem? CSV fields can contain commas if they are enclosed in quotes (or some other defined character).

PHP Code:


			
print_r(str_getcsv('foo,"bar,baz"'));

/*

Array

(

    [0] => foo

    [1] => bar,baz

)

*/

Dave · 10-30-2009, 01:48 PM

Right. That's the problem.

The comma-delimited CSV file contains commas in the NAME field (e.g., "Doe, John"), but my delimiter in the program I'm studying is a "," (comma).

Thus, when the file is read, it produces the following in an HTML table:

PHP Code:


			
// NAME    ID     

// Doe     John      9999



// What I need is this:



// NAME           ID     

// Doe, John      9999

Thanks.

Dave · 10-30-2009, 02:04 PM

Just wanted to make sure I'm on the right path. So, I did a "search & replace" in the CSV file and changed the commas in the name field to the ASCII value.

The HTML table then displayed the CORRECTLY-FORMATTED information, so this works.

However, is there a way to do this programmatically with with PHP as each line of the CSV file is read, so that I don't have to touch the CSV file?

Would posting the code help?

Thanks!

Dave

Salathe · 10-30-2009, 02:22 PM

Can you give us a few lines of the CSV file showing the problem fields?

Dave · 10-30-2009, 02:48 PM

Here are a few lines below. Only 2 fields: a name field and an ID field. As you can see, the name field contains commas. When the program reads the CSV file, it generates a third field because it "thinks" that the comma within the name field is a field separator.

Thanks!

PHP Code:


			
/*



Wiley, Jimmie    963144

Johnson, Michelle    1009959

Lynn, Kenya    1183526

Johnson, Gregory    1189881

Dixon, Kendric    1189915



*/

Salathe · 10-30-2009, 03:38 PM

That data appears to be not comma-separated, but tab-separated. The commas shouldn't be an issue if you specify the correct delimiter (comma in CSV, tab in TSV, etc.).

PHP Code:


			
$handle = fopen("test.tsv", "r");
while (($row = fgetcsv($handle, 1000, "\t")) !== FALSE) {
    print_r($row);
}
fclose($handle);

Code:

Array
(
    [0] => Wiley, Jimmie
    [1] => 963144
)
Array
(
    [0] => Johnson, Michelle
    [1] => 1009959
)
Array
(
    [0] => Lynn, Kenya
    [1] => 1183526
)
Array
(
    [0] => Johnson, Gregory
    [1] => 1189881
)
Array
(
    [0] => Dixon, Kendric
    [1] => 1189915
)

Dave · 10-30-2009, 05:04 PM

I'm a little confused. The tab delimiter is not working properly.

I'm attaching a csv file (mycsvfile.txt) which, I think, is comma-delimited. I'm still inappropriately getting 2 separate fields from the "name" field because it contains a comma.

Thanks!

Salathe · 10-30-2009, 08:16 PM

That data is different to what you posted earlier and is indeed a normal CSV file. How are you parsing the data? fgetcsv should have no problem with it at all.

Does this work?

PHP Code:


			
<?php



$fp   = fopen("mycsvfile.txt","r");

$rows = array();

while (($row = fgetcsv($fp)) !== FALSE) {

    $rows[] = $row;

}

fclose($fp);

?>

<table>

 <thead>

  <tr>

   <td>Name</td>

   <td>ID</td>

  </tr>

 </thead>

 <tbody>

<?php foreach ($rows as $row) : list($name, $id) = $row; ?>

  <tr>

   <td><?php echo $name; ?></td>

   <td><?php echo $id; ?></td>

  </tr>

<?php endforeach; ?>

 </tbody>

</table>

Dave · 10-31-2009, 12:18 AM

Yes, thanks very much. That works perfectly!

The script I was using to learn from used the fgets() function. Later in the script, the split() function was used, based on a comma delimiter.

So, in looking back, maybe the author intended the script to be used for only comma-delimited text files, and not comma-delimited csv files. Although, I sorta thought that a comma-delimited text file WAS a csv file, but maybe not...

Alright, well, this was a good learning experience! Thanks for the help.

Dave

Enfernikus · 10-31-2009, 01:11 AM

A CSV file is technically a comma delimited text file but a simple split on commas does not adhere to the full CSV standard.