Page 1 of 1

Excel into MySQL

Posted: Wed Aug 28, 2002 11:36 am
by Warp
Hello every one,

I know there is a way I could keep my database in mysql updated by importing information I collect from excell sheets.

Firstly, I exported the data out of Excel into a comma-separated text file format (CSV).

Now I should be able to import this data into MySQL using the LOAD command.

But I'm blocked, I don't know how to start on this kind of uploading script (php). I hope you can help me...

Thanks in advance for your help.

Posted: Wed Aug 28, 2002 1:36 pm
by JPlush76

Code: Select all

LOAD DATA INFILE '/home/yoursite/www/blkprod.txt' INTO TABLE x_prod FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
thats the code I use to import from excel to mysql

basically what have to do is make sure your tables line up with your excel sheet

so if my excel sheet had columns like:

orders price qty

my mysql table I'm loading into would have to be set up like

orders price qty


and it will just do the magic from there :)

the text file has to be loaded onto your webserver and you have to have the FULL path to the file IE: /home/yoursite/www/blkprod.txt


anything else you need to know with this?

Little extra question - (thanks a lot)

Posted: Thu Aug 29, 2002 4:21 am
by Warp
Thanks

That's a great help ;)

Now I can easy start modifing my upload-function with this script.

Just some extra- questions: In my exel-spreadsheet there is a column with images (links of images stored on the web). Will these links be uploaded in mysql. So that i can use these links to show relevant product pictures?

Another problem is Excel puts quotes in strange places (well, I'm sure there's some reason) this makes my data inconsistant.

If I tell phpMyAdmin to use quotes to enclose the data, the fields that Excel didn't put quotes on are messed up, and if I tell it not to use quotes, I'll get quotes in my inserted data.

I think I will actually be forced to edit the CSV file by hand to get it the way I want?

Since I want to use Excel CSV to actually maintain the data, it would be a pain to edit the file each time I make a change.
__________________


Thanks in advance,

Greetz,

Posted: Thu Aug 29, 2002 10:45 am
by JPlush76
AH!!!!!!!!! I know exactly what you mean, I'm having that same QUOTES problem with excel and I've been trying to figure out how the heck to fix it.

One alternative is to load the file with the quotes, then run a php program to remove those quotes from the beginning and the end of the field.

Code:

Code: Select all

$str = substr($file, 1, strlen($str)-2);
or Code:

Code: Select all

$str=preg_replace("/"(.*)"/", "$1", $str); 
gets rid of it.

also, about the images path, that will work fine when you load it as long as you have your directories straight :)