Solved

CSV to MySQL

Posted on 2003-11-19
20
969 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
Comment Utility
0
 
LVL 13

Expert Comment

by:lozloz
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:baz_can_fix_it
Comment Utility
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
Comment Utility
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
Comment Utility
You can insert NULL instead of an empty string ''
0
 
LVL 1

Author Comment

by:baz_can_fix_it
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:baz_can_fix_it
Comment Utility
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
Comment Utility
Hi  shmert

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

Cheers

0
 
LVL 13

Assisted Solution

by:lozloz
lozloz earned 25 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
i've added in some commas too, it will work this time

loz
0
 
LVL 1

Author Comment

by:baz_can_fix_it
Comment Utility
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
Comment Utility
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
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now