Excel into MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Warp
Forum Newbie
Posts: 2
Joined: Wed Aug 28, 2002 11:36 am

Excel into MySQL

Post 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.
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post 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?
Warp
Forum Newbie
Posts: 2
Joined: Wed Aug 28, 2002 11:36 am

Little extra question - (thanks a lot)

Post 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,
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post 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 :)
Post Reply