PHP CSV Import Problem

General PHP Help
#1

So I’m trying to make it so that I can update a MySQL database by importing a CSV file, only problem is I am seeing some of my data has commas, which is causing the data to be imported into the wrong tables. Here’s my existing import code.

[php]if ($_FILES[csv][size] > 0) {

//get the csv file 
$file = $_FILES[csv][tmp_name]; 
$handle = fopen($file,"r"); 
 
//loop through the csv file and insert into database 
do { 
    if ($data[0]) { 
        mysql_query("INSERT INTO songdb (artist, title) VALUES 
            ( 
                '".addslashes($data[0])."', 
                '".addslashes($data[1])."' 
            ) 
        ") or die (mysql_error()); 
    } 
} while ($data = fgetcsv($handle,1000,",","'")); 
// 

//redirect 
header('Location: import.php?success=1'); die;

}
[/php]

Is there a way I can set it to ignore the commas, quotes and apostrophes in the CSV file?

*** EDIT ***

I would also let to set it to ignore the first line in the csv, seeing as how it’s just column information. If that is at all possible.

#2

could you supply some data of the csv file? beeing a comma separated list it will be hard for any parser to know what is a new column and what is simply a comma in a string.

#3

For example some of my data looks like this:

Column 1, Column 2 Item 1, Description Item 2, Description Item, 3, Description Item, 4, Description "Item 5", Description "Item, 6", Description

Obviously because I’m working with a distributor list, i’d rather not post exact data, but I did the best I could to make up what is very similar to what our CSV files usually contain.

#4

Will it always be “Item” … something, or can this change?

#5

The columns are

artist, title.

where “Item” is, will have several records with that same column value.

This script is for a searchable song database. The CSV that was provided was created with Last Name, First Name. However I can’t find a way to import that using my existing code.

#6

Can both column 1 and column 2 contain commas?

Column 1, Column 2 Item 1, Description Item 2, Description, stuff Item, 3, Description Item, 4, Description, other, stuff "Item 5", Description "Item, 6", Description

#7

Yes, both columns can contain commas.

#8

Then you’re out of luck. You somehow need to define some rules for how it works, php and the csv parser can’t possibly know where you want a comma and where you do not want a comma.

If you’re able to find some rules then you can make your own parser.

#9

i was able to find a solution that worked for me last night, however it involved some HEAVY editing of the CSV file. I took a part of the file and did a test import and it worked fine. I guess I’ll just have to edit the entire file. :slight_smile: lol dang. it have over 19,000 records.

#10

That’s what happens when people write data in an invalid format. You should smack the people who made a CSV (Comma Separated Value) which couldn’t be separated on commas. :stuck_out_tongue:

#11

what would you recommend i tell them to use as a separator?

#12

A CSV should be separated with commas, the format of the data is wrong. If strings in a CSV could contain commas the string must be in double quotes.

[Column 1, Column 2 "Item 1", "Description" "Item 2", "Description, with comma!" "Item, 3", "Description, with several, commas!" "Item, 4", "Description" "Item 5", "Description, with comma" "Item, 6", "Description"

would output:

array(2) { [0] => string(8) "Column 1" [1] => string(9) " Column 2" } array(2) { [0] => string(6) "Item 1" [1] => string(11) "Description" } array(2) { [0] => string(6) "Item 2" [1] => string(24) "Description, with comma!" } array(2) { [0] => string(7) "Item, 3" [1] => string(34) "Description, with several, commas!" } array(2) { [0] => string(7) "Item, 4" [1] => string(11) "Description" } array(2) { [0] => string(6) "Item 5" [1] => string(23) "Description, with comma" } array(2) { [0] => string(7) "Item, 6" [1] => string(11) "Description" }

#13

a test

data.csv

Column 1, Column 2 "Item 1", "Description" "Item 2", "Description, with comma!" "Item, 3", "Description, with several, commas!" "Item, 4", "Description" "Item 5", "Description, with comma" "Item, 6", "Description"

test.php
[php]<?php

echo ‘

’;

$file = ‘data.csv’;

$handle = fopen($file, “r”);


while ($columns = fgetcsv($handle, 1000)) {

var_dump($columns);

}[/php]
Sponsor our Newsletter | Privacy Policy | Terms of Service