How do I make a script that converts a Comma Separated text file .csv to mysql database field?

Go To StackoverFlow.com

-1

THANKS FOR HELPING!!!

Ok... So I am trying to write a script that will take the contents of a .csv file and import them into a single table in a mySQL database. I would like the script to create fields within that table for each of the columns/headers in the .csv file.

My .csv data looks like this (I only included the first two rows):

<?php
    "PROGRAMNAME","PROGRAMURL","CATALOGNAME","LASTUPDATED","NAME","KEYWORDS","DESCRIPTION","SKU","MANUFACTURER","MANUFACTURERID","UPC","ISBN","CURRENCY","SALEPRICE","PRICE","RETAILPRICE","FROMPRICE","BUYURL","IMPRESSIONURL","IMAGEURL","ADVERTISERCATEGORY","THIRDPARTYID","THIRDPARTYCATEGORY","AUTHOR","ARTIST","TITLE","PUBLISHER","LABEL","FORMAT","SPECIAL","GIFT","PROMOTIONALTEXT","STARTDATE","ENDDATE","OFFLINE","ONLINE","INSTOCK","CONDITION","WARRANTY","STANDARDSHIPPINGCOST"
    "TeesforAll.com & WearYourBeer.com","http://www.teesforall.com, http://www.wearyourbeer.com","Product Catalog","10/01/2012","Karate Kid Cobra Kai All Valley '84 Gold Graphic T-Shirt","Karate Kid","Officially licensed Karate Kid Cobra Kai All Valley '84 Gold T-Shirt.  Features the Cobra Kai circle logo with '84 on the front.  100% cotton.","ey_84_Gold_Graphic_TShirt-p-2778.html","Karate Kid","","","","USD","","12.99","","","http://www.jdoqocy.com/click-4110789-10569779?url=http%3A%2F%2Fwww.teesforall.com%2FKarate_Kid_Cobra_Kai_All_Valley_84_Gold_Graphic_TShirt-p-2778.html","http://www.ftjcfx.com/image-4110789-10569779","http://www.teesforall.com/images/Karate_Kid_Cobra_Kai_Gold_Shirt.jpg","Mens - Shirts","","","","","","","","","","","","","","","","YES","","",""

So I figured I would use something like this:

$source_file  = "catalogs/tees4all.txt";
$handle = fopen("catalogs/tees4all.txt", "r");

while (($data = fgetcsv($handle)) !== FALSE) {
  var_dump($data);
}

var_dump of $data OUTPUTS THIS... Basically each row as an array:

array(40) { [0]=> string(11) "PROGRAMNAME" [1]=> string(10) "PROGRAMURL" [2]=> string(11) "CATALOGNAME" [3]=> string(11) "LASTUPDATED" [4]=> string(4) "NAME" [5]=> string(8) "KEYWORDS" [6]=> string(11) "DESCRIPTION" [7]=> string(3) "SKU" [8]=> string(12) "MANUFACTURER" [9]=> string(14) "MANUFACTURERID" [10]=> string(3) "UPC" [11]=> string(4) "ISBN" [12]=> string(8) "CURRENCY" [13]=> string(9) "SALEPRICE" [14]=> string(5) "PRICE" [15]=> string(11) "RETAILPRICE" [16]=> string(9) "FROMPRICE" [17]=> string(6) "BUYURL" [18]=> string(13) "IMPRESSIONURL" [19]=> string(8) "IMAGEURL" [20]=> string(18) "ADVERTISERCATEGORY" [21]=> string(12) "THIRDPARTYID" [22]=> string(18) "THIRDPARTYCATEGORY" [23]=> string(6) "AUTHOR" [24]=> string(6) "ARTIST" [25]=> string(5) "TITLE" [26]=> string(9) "PUBLISHER" [27]=> string(5) "LABEL" [28]=> string(6) "FORMAT" [29]=> string(7) "SPECIAL" [30]=> string(4) "GIFT" [31]=> string(15) "PROMOTIONALTEXT" [32]=> string(9) "STARTDATE" [33]=> string(7) "ENDDATE" [34]=> string(7) "OFFLINE" [35]=> string(6) "ONLINE" [36]=> string(7) "INSTOCK" [37]=> string(9) "CONDITION" [38]=> string(8) "WARRANTY" [39]=> string(20) "STANDARDSHIPPINGCOST" } 


array(40) { [0]=> string(33) "TeesforAll.com & WearYourBeer.com" [1]=> string(54) "http://www.teesforall.com, http://www.wearyourbeer.com" [2]=> string(15) "Product Catalog" [3]=> string(10) "10/01/2012" [4]=> string(56) "Karate Kid Cobra Kai All Valley '84 Gold Graphic T-Shirt" [5]=> string(10) "Karate Kid" [6]=> string(142) "Officially licensed Karate Kid Cobra Kai All Valley '84 Gold T-Shirt. Features the Cobra Kai circle logo with '84 on the front. 100% cotton." [7]=> string(37) "ey_84_Gold_Graphic_TShirt-p-2778.html" [8]=> string(10) "Karate Kid" [9]=> string(0) "" [10]=> string(0) "" [11]=> string(0) "" [12]=> string(3) "USD" [13]=> string(0) "" [14]=> string(5) "12.99" [15]=> string(0) "" [16]=> string(0) "" [17]=> string(150) "http://www.jdoqocy.com/click-4110789-10569779?url=http%3A%2F%2Fwww.teesforall.com%2FKarate_Kid_Cobra_Kai_All_Valley_84_Gold_Graphic_TShirt-p-2778.html" [18]=> string(44) "http://www.ftjcfx.com/image-4110789-10569779" [19]=> string(68) "http://www.teesforall.com/images/Karate_Kid_Cobra_Kai_Gold_Shirt.jpg" [20]=> string(13) "Mens - Shirts" [21]=> string(0) "" [22]=> string(0) "" [23]=> string(0) "" [24]=> string(0) "" [25]=> string(0) "" [26]=> string(0) "" [27]=> string(0) "" [28]=> string(0) "" [29]=> string(0) "" [30]=> string(0) "" [31]=> string(0) "" [32]=> string(0) "" [33]=> string(0) "" [34]=> string(0) "" [35]=> string(0) "" [36]=> string(3) "YES" [37]=> string(0) "" [38]=> string(0) "" [39]=> string(0) "" }

I am hoping that someone can help me get from $data into a database table where the columns and rows match up… with the headers and rows of the CSV!

THANK YOU !!!

2012-04-04 01:29
by DigitalMediaGuy
how about formatting that - NoName 2012-04-04 01:31
LOAD DATA INFILE: http://dev.mysql.com/doc/refman/5.1/en/load-data.htm - OMG Ponies 2012-04-04 01:39
It's called phpMyAdminhohner 2012-04-04 01:43


1

If you want to make this with php try this.

<?

$source_file  = "catalogs/tees4all.txt";
$handle = fopen("catalogs/tees4all.txt", "r");

$col_names = implode(",", fgetcsv($handle)); // Getting comma separated list of col names

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db("yourdbhere");
while (($data = fgetcsv($handle)) !== FALSE) {
    $values = "";
    foreach($data as $key => $value) {
        if ($key != 0) $values .= ", ";
        $values .= "'".mysql_escape_string($value)."'";
    }
    mysql_query('INSERT INTO yourtablehere ('.$col_names.') VALUES ('.$values.')');
}

Or just use http://dev.mysql.com/doc/refman/5.1/en/load-data.html

2012-04-04 01:45
by Daniil Ryzhkov
Thanks for the quick reply... I have been playing with both of your suggestions... However I haven't had much luck with either :-/

On the php side... I am getting a white page... without errors.... and no data going into the db... HOWEVER... I am working in joomla and things are slightly "different"

SO... I really hoped I would figure it out with SQL like you suggested... I am just trying to get the data into the database...

SO... what exactly do I do with this? Run it in phpmyadmin? Also maybe you could explain what the variables should be? : - DigitalMediaGuy 2012-04-04 03:51

LOAD DATA [LOWPRIORITY | CONCURRENT] [LOCAL] INFILE 'filename' [REPLACE | IGNORE] INTO TABLE tblname [CHARACTER SET charsetname] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(colnameoruservar,...)] [SET col_name = expr,... - DigitalMediaGuy 2012-04-04 03:51
The names of the columns in your cvs file should match with the name of the columns in your MySQL table. Also look at the updated example. ".$values." should be '.$values. - Daniil Ryzhkov 2012-04-04 04:02