[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 983
  • Last Modified:

CSV to MySQL

Hi Experts

I have a CSV file like this

"310396*1","Artful persuasion","Mills, Harry","BF637.P4.M55 1999"
"310398*1","Marketing","Quester, Pascale G.","HF5415.M3725 2004"
"310399*1","Remote control","Probyn,  Elspeth","P94.A8.R45 2003"
"310407*1","Postmortems from Game developer","Grossman, Austin","QA76.76.C672.P67 2003"
"310408*1","Zap!"
"310411*1","A long and winding road","Monahan, Sean.","PR8237.E72.M66 2003"
"310417*1","The practice of public relations","Seitel, Fraser P.","HM263.S42 2004 HM263.S42 2004"
"310420*1","La Loca de La Casa"

As you can see the csv file is not consistent in the fields, some dont have Author and some don't have a Call No some don't have both..However they always have BRN and Title

I have a script that changes csv files to MySQL statments like this:

/* read file */
$fcontents = file ($filename_input);

/* process each single line */
while (list ($line_num, $line) = each ($fcontents)) {
   
        /* debug info (input) */
        if ($debug_input == 'yes') {
                 echo "<br><b>Line $line_num:</b> " . htmlspecialchars($line) . "<br>";
        }

        /* seperate line into array */
        $without_space = trim (chop ($line));
        $ln = explode ($seperator, $without_space);

        /* construct SQL query (max 30 entries - add more if you wish) */
        $sqlquery = 'INSERT INTO ' . $dbname . ' (BRN, Title, Author, Call) VALUES ('; // INSERT INTO test VALUES("1","0","abc");
         if($ln[0]) { $sqlquery .= '' . $ln[0] . ''; }
         if($ln[1]) { $sqlquery .= ',' . $ln[1] . ''; }
         if($ln[2]) { $sqlquery .= ',' . $ln[2] . ''; }
         if($ln[3]) { $sqlquery .= ',' . $ln[3] . ''; }
        $sqlquery .= ');';
        $sqlquery .= "\n"; // new line
               
        /* debug info (output) */
        if ($debug_output == 'yes') {
                echo $sqlquery;
        }
        /* save SQL query to file (append) */

                $save = fopen ($filename_output, "a");
                fputs($save, $sqlquery);
                flock($save,3);
                fclose($save);
                $save_status = "newfile";
        }
?>


This creates the SQL statment fine...except the problem is I can't run the SQL statment because the column don't match...

for example:

INSERT INTO NTA (BRN, Title, Author, Call) VALUES ("310408*1","Zap!");

is missing Authour and Call...

So, can any one suggest a way to put some characters in the blank fields like "NA" like so

INSERT INTO NTA (BRN, Title, Author, Call) VALUES ("310408*1","Zap!","NA","NA");

This could be done either on the CSV file or on the SQL out file...

Thanx In advance

Cheers
0
baz_can_fix_it
Asked:
baz_can_fix_it
  • 8
  • 4
  • 4
  • +2
2 Solutions
 
aolXFTCommented:
0
 
lozlozCommented:
the problem is in this line:

$sqlquery = 'INSERT INTO ' . $dbname . ' (BRN, Title, Author, Call) VALUES ('; // INSERT INTO test VALUES("1","0","abc");

this is hard coded to have 4 fields input for each line, but your code says to only attach each field if there is content (if($ln[0]) etc. so change each of those lines to this:

if($ln[0]) { $sqlquery .= "'" . $ln[0] . "'"; } else { $sqlquery .= "'NA'"; }
if($ln[1]) { $sqlquery .= "'" . $ln[1] . "'"; } else { $sqlquery .= "'NA'"; }
if($ln[2]) { $sqlquery .= "'" . $ln[2] . "'"; } else { $sqlquery .= "'NA'"; }
if($ln[3]) { $sqlquery .= "'" . $ln[3] . "'"; } else { $sqlquery .= "'NA'"; }

loz
0
 
shmertCommented:
You should also take a look at the PHP fgetcsv function (http://us4.php.net/fgetcsv).  Two big reasons to use it are more efficient execution, and it also is smart about handling escaped quotes.  If one of your values has a quote in it, explode() will split it into 2 fields, one of which ends in a backslash, which could end up doing some bad things to your data.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
baz_can_fix_itAuthor Commented:
lozloz:

Your soloution makes sense...I will be back to work on Tuesday...I'll give it ago...Thanx heaps

shmert

Thank you too I'll have a look at it on Tuesday as well


I'll let you know both on Tuesday....

Cheers
0
 
shmertCommented:
Cool.  Here's how the csv function would work:

/**
* Generates an SQL INSERT statement from a comma-separated file.
* @param String $file location of the csv file
* @param String $table table to insert data into
* @param Array $fields array of columns the csv data represents
* @param String $emptyString string to use if a column is empty (default='')
*/
function csv2sql($file, $table, $fields, $emptyString='') {
        $fp = fopen($file, 'r') or die("unable to open file $file");
        $fieldList = join(', ', $fields);
        $fieldCount = count($fields);
        $comma = '';
        $sql = "INSERT INTO $table ($fieldList) VALUES ";
        while ($line = fgetcsv($fp, 1024)) {
                $sql .= $comma . '(';
                $comma = '';
                for ($i=0; $i<$fieldCount; $i++) {
                        // take the next item from the csv line if available
                        // if not, use the $emptyString parameter
                        $value = $line ? array_shift($line) : $emptyString;
                        $value = addslashes($value);
                        if (!is_numeric($value)) $value = "'$value'";
                        $sql .= $comma . $value;
                        $comma = ', ';
                }
                $sql .= ')';
        }
        fclose($fp);
        return $sql;
}
0
 
ashooooCommented:
You can insert NULL instead of an empty string ''
0
 
baz_can_fix_itAuthor Commented:
Hi LozLoz

Your suggestion produces this

INSERT INTO NTA (BRN, Title, Author, Call) VALUES ('"301949*2","Autistic spectrum disorders","Jordan, Rita.","RC553.A88.J67 1999 RC553.A88.J67 1999"''NA''NA''NA');

It seems like it puts 'NA''NA''NA' for all the statments....

Hi shmert

I am still working on your example...

Cheers

0
 
baz_can_fix_itAuthor Commented:
Hi shmert

I am very new to PHP....does that fgetcsv require any speacial library? I just can't get it to work...I am not getting any errors either....


cheers
0
 
shmertCommented:
No, it's been in PHP since 3.0.8
You need to pass in an array of fields, like so:
<?php
$fields = array('BRN', 'Title', 'Author', 'Call');
$sql = csv2sql($filename_input, $dbname, $fields, 'NA');
?>
0
 
ashooooCommented:
baz, remember that if a field allows null values, you can insert NULL.

Ex.. "Insert into tablename values (NULL, 'last', 'first', 1, 0, 0)"
0
 
baz_can_fix_itAuthor Commented:
Hi Ashooo

I am aware of that...and thats what we are trying to do...my problem is the above script is producing a statment like this:

INSERT INTO NTA (BRN, Title, Author, Call) VALUES ("310408*1","Zap!")

which MySQL obviously won't like (even thou all the fields are set to allow NULL...) so we are trring to make it do this

INSERT INTO NTA (BRN, Title, Author, Call) VALUES("310408*1","Zap!",NULL,NULL)

or

INSERT INTO NTA (BRN, Title, Author, Call) VALUES("310408*1","Zap!","NA","NA")

NA is not any reserved word or anything its jus a word I picked for Not Available...

I hope it makes sense...

Cheers
0
 
baz_can_fix_itAuthor Commented:
Hi  shmert

I'll try it  tomorrow and let you know....

Cheers

0
 
lozlozCommented:
sorry, change it to this:

         if($ln[0]) { $sqlquery .= "'" . $ln[0] . "'"; } else { $sqlquery .= "NULL"; }
         if($ln[1]) { $sqlquery .= ", '" . $ln[1] . "'"; } else { $sqlquery .= ", NULL"; }
         if($ln[2]) { $sqlquery .= ", '" . $ln[2] . "'"; } else { $sqlquery .= ", NULL"; }
         if($ln[3]) { $sqlquery .= ", '" . $ln[3] . "'"; } else { $sqlquery .= ", NULL"; }

if you wanna continue down this path - i forgot to add in the commasloz
0
 
ashooooCommented:
Oh sorry baz, I didnt read your question properly. Anyways, the latest answer by loz is what I would recommend.
0
 
shmertCommented:
Here's a slightly updated version of the cvs2sql function, which now supports NULL values:
/**
* Generates an SQL INSERT statement from a comma-separated file.
* Columns which should not be part of the SQL insert statement should be named a blank string
* in the <code>fields</code> parameter.
* @param String $file location of the csv file
* @param String $table table to insert data into
* @param Array $fields array of columns the csv data represents.
* @param String $emptyString string to use if a column is empty (default is NULL)
* @author Sam Barnum http://www.360works.com
*/
function csv2sql($file, $table, $fields, $emptyString=null) {
        $fp = fopen($file, 'r') or die("unable to open file $file");
        $fieldList = join(', ', array_diff($fields, array('')));
        $fieldCount = count($fields);
        $comma = '';
        $sql = "INSERT INTO $table ($fieldList) VALUES ";
        while ($line = fgetcsv($fp, 1024)) {
                $sql .= $comma . '(';
                $comma = '';
                for ($i=0; $i<$fieldCount; $i++) {
                        // take the next item from the csv line if available
                        // if not, use the $emptyString parameter
                        $value = $line ? array_shift($line) : $emptyString;
                        if (empty($fields[$i])) continue; // ignore this column
                        $value = addslashes($value);
                        if ($value == null) $value = 'NULL';
                        else if (!is_numeric($value)) $value = "'$value'";
                        $sql .= $comma . $value;
                        $comma = ', ';
                }
                $sql .= ')';
        }
        fclose($fp);
        return $sql;
}


Call it like this:

<?php
$fields = array('BRN', 'Title', 'Author', 'Call');
$sql = csv2sql($filename_input, $dbname, $fields);
?>
0
 
baz_can_fix_itAuthor Commented:
Hi LozLoz

All you have changed is NA to NULL?????? what diffrence is that going to make it will be then like this

INSERT INTO NTA (BRN, Title, Author, Call) VALUES ('"301949*2","Autistic spectrum disorders","Jordan, Rita.","RC553.A88.J67 1999"', NULL, NULL, NULL);

What i was trying to sy before is there is a problem with the if else statement....


Cheers
0
 
lozlozCommented:
i've added in some commas too, it will work this time

loz
0
 
baz_can_fix_itAuthor Commented:
Shmert!!!!!! It Works :)

INSERT INTO NTA (BRN, Title, Author, Call) VALUES ('310408*1', 'Zap!', NULL, NULL), ('310411*1', 'A long and winding road', 'Monahan, Sean.', 'PR8237.E72.M66 2003'), ('310417*1', 'The practice of public relations', 'Seitel, Fraser P.', 'HM263.S42 2004 HM263.S42 2004'), ('310420*1', 'La Loca de La Casa', NULL, NULL);


Thanx Heapssssssssssssssssssssssssssssssssssssssssssss


Cheers

Thank you LozLoz and ashoooo as well....


0
 
baz_can_fix_itAuthor Commented:
lozloz

I still get the same thing...sory :( I gave you some points because you kept trying and appricite that...

shmert's gets the Correct answer....

Cheers
0
 
lozlozCommented:
yeh shmert's function is better because it is more efficient at seperating the items of data apart, but i still don't see why my code wouldn't have worked - anyway the problems solved which is all that matters

loz
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now