Convert an excel file to mysql table

Hi All,
I need a way or script or a FREE tool to convert excel sheets to mysql tables
I found tools on the nert but DEMO !!
it converts first 5 rows only !!

 

Why not use excel to save your file as a CSV (comma seperated values) and then use PHPmyadmin to import the data that way?

 

That would be the easiest way
If not i did a quick google and found this

Not sure if its any good but seems to do what you want, check it out

 

save it first as csv file. then import it to your mysql database

 

Readng of Excel is not very simple as it is with CSV.

Here is a library to read Excel files if you can utilize it
http://phpexcelreader.sourceforge.net

Download:
http://sourceforge.net/projects/phpexcelreader/

regards

 

Dear ,Grit.,joxtechnology,
I got the following error:
Invalid field count in CSV input on line 1.
======================================

 

hmm. your mysql table should have same number fields. example if your csv/excel file contains 10 columns your mysql table should also have 10 fields to import the data.

 

Well i suggest that please dont keep the column heading while saving as csv

the second thing is if there are any fields which are strings and contains characters like , ' & etc then please enclose such columns in ""


because csv file consist of comma separated values and any comma is considered to be a field separator so the there may be some comma in ur data which is reporting the php or mysql more number of columns than available

 

Well if you have decided to work with CSV well and good, it will go pretty well. If you already have table structure well otherwise following function will create new table and create all fields text (although it i not good idea, but as requirement is not clear, I am assuming it)

<?
	$csv = "filename.csv";
	$tab = "NewTableName";


	mysql_connect("<DBHOST>","<DBUSER>","<DBPASSW>");
	mysql_select_db("<DATABASE_NAME>");



	$inserted = [B]trasnformCSV2MySQL[/B]($csv, $tab);
	
	echo "Inserted total <b>$inserted</b> rows to table $tab";



	function trasnformCSV2MySQL($csv,$tab) {

		$f = fopen($csv,"r");

		$first_row = fgetcsv($f,2048); #assumed that first row or CSV is field names
		$total_fields = count($first_row);
		#create table

		$all_fields = implode(" text,\r\n",$first_row)." text";
		
		mysql_query("CREATE TABLE `$tab` (
		
			$all_fields
		
		)") or die(mysql_error());
		
		$insertion_queries = Array();
		
		while($csv_row = fgetcsv($f,2048)) {
			$fixed = array_slice($csv_row,0,$total_fields);
			if(count($fixed)==1 and trim($fixed[0]) == "") #empty row
				continue;
			
			foreach($fixed as $ke => $va) $fixed[$ke] = str_replace('"',"\\\"",trim($va));
			$fixed = "\"".implode("\",\"",$fixed)."\"";
			
			$insertion_queries[]= "(".$fixed.")";
			
		}
		$total_rows = count($insertion_queries);
		$insertion_queries = implode(",\r\n",$insertion_queries);
		mysql_query("INSERT INTO `$tab`
				VALUES
				$insertion_queries") or die(mysql_error());
				
		return $total_rows;
	
	}


?>

PHP:

function trasnformCSV2MySQL takes 2 arguments first is csv file path, second is table name. It is your responsibility to delete all enteries of table first if you want it to be empty. Also, it creates table and does not check if alreay exists, so drop the testing table first, if same name exists.

function requires little modifications to make it work with existing table btw.


I hope it helps.

regards

 

I invented a strange way !!
I imported excel into Access
and found a free tool to convert Access db to Mysql !!

Nice?

 

Well, basially purpose of such script is to perform everything at server-side.

Here is one more similar script that was also coed by me.

 

I purchased one software called exceltomysql which is good it convert my excel sheet into the table and column first row into the field name and it converts rest of the raw as number of entries..!!!

Cool one

 

The one I am pointing to is too flexible, and allows you to import to selective fields of a db table as well as skip any columns from CSV input. + it supports 3 SQL servers not just mysql.

regards

 

Hello,

Yup, You can definately convert excel file into mysql table..We already did this..We will help you if you need help for excel sheet into mysql table


Please send us PM for this


Regards
Stylesofts Developing Team

 

do with following step excel to mysql convert.

1. convert excel file to msaccess. you just need to open excel file in access.

2. then convert mdb to mysql by MySQL GUI Tools from migration option. MySQL GUI Tools is free, you can download from mysql site.

hope it will be help you