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


Problem :

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 !!!



Solution :

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


Mysql Tutorials

Mysql Howto..

How can I tell what version of mysqli is installed?

How to connect Android with MySQL using Mysql JDBC driver

How do I use shortcodes in a standard PHP page?

MySQL Operand should contain 1 column - how to overcome

how to add rows to mysql database table and display it in a listbox in vb.net?

How to retrive and update radio button's data from mysql database?

How to select date and time without the seconds in mysql?

UTC Problems, not sure how to fix them (PHP/MySQL)

MySQL - how to sum counts by group

how to write case statement in mysql?

How to properly use join query mysql InnoDB

MySQL: How to search from two fields?

How to avoid “dll is not allowed to be included or could not be found” error while building web player app?

How to disable MySQL's errors?

How to display MySQL results side by side

How to generate SQL table structure as a text table?

How to connect MySQL Cartridge to Django in OpenShift

How to use SELECT inside INSERT to define a specific column in mysql

MySQL How to efficiently compare multiple fields between tables?

How to start from a certain row and end in a certain row in mysql PHP?

How to dont let users save the same item twice in mysql

How to update and replace string with random number using a regular expression in MySQL?

how to hide duplicate record in mysql?

how to create view and get data mysql php javascript

Copying a database in MySQL, how to copy views?

Using MySQL in Powershell, how do I pipe the results of my script into a csv file?

How to enable users from another domain to search records from my domain?

How to scroll through a count query in MySQL/PHP

How to increase numbers in mysql column

How to store multiple SELECT values at once into MySQL?