Solved

CSV to MySQL

Posted on 2003-11-19
20
973 Views
Last Modified: 2008-02-01
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
Comment
Question by:baz_can_fix_it
  • 8
  • 4
  • 4
  • +2
20 Comments
 
LVL 6

Expert Comment

by:aolXFT
ID: 9783832
0
 
LVL 13

Expert Comment

by:lozloz
ID: 9786789
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
 
LVL 11

Expert Comment

by:shmert
ID: 9788490
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:baz_can_fix_it
ID: 9792429
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
 
LVL 11

Expert Comment

by:shmert
ID: 9792481
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
 
LVL 3

Expert Comment

by:ashoooo
ID: 9804128
You can insert NULL instead of an empty string ''
0
 
LVL 1

Author Comment

by:baz_can_fix_it
ID: 9808328
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
 
LVL 1

Author Comment

by:baz_can_fix_it
ID: 9808391
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
 
LVL 11

Expert Comment

by:shmert
ID: 9808470
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
 
LVL 3

Expert Comment

by:ashoooo
ID: 9808812
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
 
LVL 1

Author Comment

by:baz_can_fix_it
ID: 9809041
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
 
LVL 1

Author Comment

by:baz_can_fix_it
ID: 9809057
Hi  shmert

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

Cheers

0
 
LVL 13

Assisted Solution

by:lozloz
lozloz earned 25 total points
ID: 9809635
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
 
LVL 3

Expert Comment

by:ashoooo
ID: 9810942
Oh sorry baz, I didnt read your question properly. Anyways, the latest answer by loz is what I would recommend.
0
 
LVL 11

Accepted Solution

by:
shmert earned 100 total points
ID: 9811095
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
 
LVL 1

Author Comment

by:baz_can_fix_it
ID: 9814923
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
 
LVL 13

Expert Comment

by:lozloz
ID: 9814985
i've added in some commas too, it will work this time

loz
0
 
LVL 1

Author Comment

by:baz_can_fix_it
ID: 9815050
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
 
LVL 1

Author Comment

by:baz_can_fix_it
ID: 9815078
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
 
LVL 13

Expert Comment

by:lozloz
ID: 9815171
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question