[SOLVED] excel

[miracle_potential]

I'm having trouble getting the data from my excel table to my MYSQL table I've tried exporting it as text files and CSV files and I cant seem to get this massive table into MYSQL and I dont want to have to type it out

[litebearer]

A bit more info would be in order.

 

What error message(s) are you getting?

Can you show us your code as well as a snippet of the CSV file?

 

Lite...

 

[miracle_potential]

Well basically I REALLY dont want to have to type out 350 products into my database and my client only supplied an excel sheet, which I exported as a CSV and text file neither worked for the upload.

 

I've attached the excel sheet to this post and it has the excel file, a CSV and a text file. I really just need it so that I can export it or convert it or anything to save me from typing everything out or if anyone has a PHP file that I can run and load my excel sheet into to convert it I could make one but again sort of defies the object of the game here haha running tight deadlines now.

 

I need this excel sheet and all of its contents in a MYSQL insert list so I can just run it and have done with it lol

 

[attachment deleted by admin]

[miracle_potential]

Anyone?

[947740]

You should be able to export as a text file with fields delimited by a commas. You will have to specify "fields terminated by ','" in your "load data infile" query. Have you tried that?

[miracle_potential]

I cant say I have. But I have to admit to not knowing how or what you jsut said is 

[947740]

Try going to save as and change the type of file to text delimited by commas.  It is either that or export, but I am not sure.

 

I presume you understand the "load data infile" part?

[litebearer]

Ok - being old, lazy and a 'sledge hammer approach' guy, here is my suggestion and comments.

 

I converted the xls file into a comma delimited CSV file - file name noir000.dat (note when I did this some of the elements had many extra empty 'fields' so I went back and deleted all those columns after the 'STOCK' column to clean things up a bit).

 

Here is a rough idea how to then move the data to your mysql database - obviously I left out the database connection and actual query portions.

<?PHP

##########################################
# read the data file into an array
# each element of this array contains all of the information for each item

$data_file = "noir000.dat";

$line_array = file($data_file);

########################################
# make your database connection here


####################################################
# loop through the first array

$number_of_items = count($line_array);

$i =0;
for($i=0;$i<$number_of_items;$i++) {

########################################
# explode each element into a new array using the comma as the delimiter

$temp_array = explode(",",$line_array[$i]);

###########################################
# make your mysql query here and
# insert each field from the temp array into the proper mysql fields

}

?>

 

Hope this helps

 

[miracle_potential]

Can you attach your .dat file either mines wrong or my query isnt working as it should

[litebearer]

here it is

 

 

 

[attachment deleted by admin]

[miracle_potential]

Lol its my query xD can someone explain this? I've never done this before 

[litebearer]

we need to see your code to be able to assist

[miracle_potential]

mysql_query("INSERT INTO prod_inf itemname, DESCRIPTION, CATAGORY, sub-catagory, PRICE VALUES " . $temp_array . "");

 

[947740]

The only problem with my suggestion is the fact that you have commas in your data.  If the commas were removed, a CVS file delimited by commas would work just fine.

[miracle_potential]

Did you use the one that Lite attached to his post? or mine? I havnt really a clue what I'm doing I've never imported excel stuff into MYSQL

[947740]

I opened your excel document.

[litebearer]

here is how the query shold look. NOTE - your excel file lists the columns as follows:

 

Department - Sub Cat - Item - Desc - Price - Stock

(there are six cols in the excel file and only five in your insert statement)

mysql_query("INSERT INTO prod_inf (itemname, DESCRIPTION, CATAGORY, sub-catagory, PRICE)  VALUES($temp_array[2]','$temp_array[3]',$temp_array[0]',$temp_array[1]',$temp_array[4]',  ) ") or die(mysql_error()); 

 

I presumed

itemname = item

description = description

sub cat = sub cat

cat = dept

price = price

 

Hope this helps

 

Lite...

 

 

[litebearer]

as a side note - when saving an excel file as a CSV comma delimited, it will automatically add opening and closing quotation marks to any field that contains commas; thereby 'fixing the apparent' problem with 'extra' commas.

[947740]

I did not know that part.  Eh, you learn something new every day.  So I guess my recommendation would work, but the problem seems to be solved.

[miracle_potential]

Column count doesn't match value count at row 1

 

I got that, and I'm extremely tired so this could be blind idiocy but this is the code after I altered it a little bit from other errors in the query.

 

The code is now

 

<?
##########################################
# read the data file into an array
# each element of this array contains all of the information for each item

$data_file = "csv.dat";

$line_array = file($data_file);

########################################
# make your database connection here
mysql_connect("localhost", "noirorchidemporium_co_uk", "animal")or die("didnt connect1");
mysql_select_db("noirorchidemporium_co_uk_1")or die("didnt connect2");

####################################################
# loop through the first array

$number_of_items = count($line_array);

$i =0;
for($i=0;$i<$number_of_items;$i++) {

########################################
# explode each element into a new array using the comma as the delimiter

$temp_array = explode(",",$line_array[$i]);

###########################################
# make your mysql query here and
# insert each field from the temp array into the proper mysql fields

mysql_query("INSERT INTO prod_inf (ID, itemname, DESCRIPTION, CATAGORY, subcatagory, PRICE, PICTURE_1, PICTURE_2, POSTAGE, STOCK)  VALUES('$temp_array[2],$temp_array[3],$temp_array[0],$temp_array[1],$temp_array[4],,,,,,'  ) ") or die(mysql_error()); 
}
echo $temp_array;
echo $number_of_items;
echo "inserted/inserting";

?>

 

And the DB is

 

CREATE TABLE prod_inf (
  ID int(11) NOT NULL auto_increment,
  itemname text NOT NULL,
  DESCRIPTION text NOT NULL,
  CATAGORY text NOT NULL,
  subcatagory text,
  PICTURE_1 text NOT NULL,
  PICTURE_2 text NOT NULL,
  PRICE text,
  POSTAGE text,
  STOCK text NOT NULL,
  PRIMARY KEY  (ID)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

 

Cheers guys

[litebearer]

Ok -

 

When you are inserting a new record into a database

1. You do NOT need to list any fields for which you do not have values.

2. If a field (ID in this case) is AUTO-INCREMEN, you do not have to list it

3. Each value needs to have the single quotes around it, not just at the begining and end of the list.

 

so change this

mysql_query("INSERT INTO prod_inf (ID, itemname, DESCRIPTION, CATAGORY, subcatagory, PRICE, PICTURE_1, PICTURE_2, POSTAGE, STOCK)  VALUES('$temp_array[2],$temp_array[3],$temp_array[0],$temp_array[1],$temp_array[4],,,,,,'  ) ") or die(mysql_error()); 

 

to this

mysql_query("INSERT INTO prod_inf (itemname, DESCRIPTION, CATAGORY, subcatagory, PRICE)  VALUES('$temp_array[2]','$temp_array[3]','$temp_array[0]','$temp_array[1]','$temp_array[4]')") or die(mysql_error()); 

 

that should do it.

 

Lite...

 

 

[miracle_potential]

Ok so the finishing code was

And it worked a charm thankyou so much guys

 

<?
##########################################
# read the data file into an array
# each element of this array contains all of the information for each item

$data_file = "csv.dat";

$line_array = file($data_file);

########################################
# make your database connection here
mysql_connect("localhost", "noirorchidemporium_co_uk", "animal")or die("didnt connect1");
mysql_select_db("noirorchidemporium_co_uk_1")or die("didnt connect2");

####################################################
# loop through the first array

$number_of_items = count($line_array);

$i =0;
for($i=0;$i<$number_of_items;$i++) {

########################################
# explode each element into a new array using the comma as the delimiter

$temp_array = explode(",",$line_array[$i]);
$temp_array2 = preg_replace("",",'&+-",$line_array[$i]);

###########################################
# make your mysql query here and
# insert each field from the temp array into the proper mysql fields

$sql = mysql_query("INSERT INTO prod_inf (itemname, DESCRIPTION, CATAGORY, subcatagory, PRICE)  VALUES('$temp_array[2]','$temp_array[3]','$temp_array[0]','$temp_array[1]','$temp_array[4]' ) ") or die(mysql_error()); 
}
echo $temp_array;
echo $number_of_items;
echo "inserted/inserting";

?>

[johnc71]

I tested the code in the previous post and it works fine. However, I would like to know if there is a way to first check if the record already exist in DB before loading data from .DAT file?

 

Basically, I would like to only import new data.

 

 

[miracle_potential]

All I can suggest to save you a crap load of time is change your query to only select distinct records on the page querying from the database. Because right now I'm being way to lazy to edit that script 

 

for instance.

 

mysql_query("SELECT DISTINCT column FROM table")or die(mysql_error());