[SOLVED] Importing Data From Microsoft Excel into MySQL database

[PC Nerd]

Hi,

 

Im looking for a method if importing Excel Data into my MySQL database.  I have a feeling that the only way will to have it exported as a CSV file - and then have PHP transfer it ( INSERT INTO) - my database - but Im looking for an easier option.

 

If anyone can suggestion an easy way to do this I would be most grateful. I would cop the data across manually - excelt for that fact that Its over 800 records - so its a little un managable.

 

The data comes from another database, however I dont have access to it ( to export as SQL in MySQL compatability mode - however Im looking at options) - so I only have a CSV file that comes out of that database.  I can ensure that field names are the same as my MySQL database - all that isnt an issue - its just transfering the raw data across that is the issue.

 

Once again - any suggestions would be great.

 

Thanks in advance

[ratcateme]

someone else had a similar question i gave them this code

<pre>
<?php
$con=mysql_connect();
mysql_select_db('test');
//number of lines to skip
$skip=1;
$current_line=1;


$sheet='test.txt';
$file=fopen($sheet,'r');
while(!feof($file)){
$line=fgets($file);
//add this
if($current_line>$skip){


	$line=explode("\t",$line);
	$query='INSERT INTO `table` VALUES (';
	foreach($line as $key => &$value){
		if($key==0){
			$query.="'".$value."'";
		}else{
			$query.=", '".$value."'";
		}
	}
	$query.=');';
	echo $query."\n";
	mysql_query($query,$con);
//add this
}
$current_line++;


}
fclose($file);


?>
</pre> 

 

this is designed for xls files saved as txt but if you want change the "\t" to "," on line 16 to use csv

and set the number skip according to how many lines are taken up by titles and headers

 

Scott.

[PC Nerd]

Ok thanks.

 

are there any other methods apart from the manual PHP import.  is there a way that i can convert the excel sheet ( and by extension the CSV file) - convert that into SQL inserts or similar methods?

 

thanks

[ratcateme]

i don't know about converting a xls sheet into query's but it might be possible. But i think i found a way to use straight xls files using this pear package http://www.codeplex.com/PHPExcel/Wiki/View.aspx?title=Home

 

Scott.

[ratcateme]

sorry i think that only writes not reads xls files

 

Scott.

[PC Nerd]

*** I can read and write from CSV - and there are heaps of scripts on th net - so thats fine - but im looking for a simpler solution tha thavign that script do it.

 

Any suggstions about conversions or programs etc???

 

[PC Nerd]

** bump **

[Stooney]

So far I've found:

 

http://mysql-migration-toolkit.intelligent-converters.qarchive.org/  - $99

 

If you have a way to convert xls to dbf, you could check out http://www.downloadatoz.com/compare/dbf-converter.html

One of those will convert dbf into sql queries.

 

I just found one:

 

http://xls-excel-to-dbf.whitetown-software.alienpicks.com/

 

So you could use the 3rd link to go from xls to dbf, then the second link to go dbf to sql queries. 

 

Not much there, but it might help.

[ratcateme]

you could try this i don't no how often you want top do this but this has a 30 day free trial you could use in the short term

 

Scott.

[Barand]

Some options

 

1 ) save as csv then bulk load using MySql LOAD DATA INFILE

 

2 ) save as csv and use a script to read and load

 

3 ) use COM to read the excel file directly

 

4 ) set up an excel odbc connection and use a script to read and load

 

5 ) StarTrek method - "Computer - transfer file from Excel to MySQL"

[MCrosbie]

There is a great MySQL Program called Navicat (www.navicat.com) which allows you to import xml files into MySQL databases. I use it and it's fantastic, highly recommended.

[PC Nerd]

Thanks.

 

Ill look into it.