Importing Spreadsheet

[harlequin2k5]

I have an Excel spreadsheet with almost 2000 rows of data and I was hoping that there was a way that I could you phpMyAdmin to import that data iwhtout having to type in each and every item?

I have 2 other spreadsheets which have a total of about 7-8000 items in them as well

I've been trying to follow the manual online and I have another book that I'm using but maybe I'm just being a bimbo because I can't figure it out

I have already set up the table and fields in phpMyAdmin and I actually started to type the items in (about 200 so far) and I decided that was a little retarded considering that the spreadsheet was already there and I shouldn't need to...

...unless you guys say different *chuckles*

as always, any help is greatly appreciated!

[harlequin2k5]

This maybe should be in MySql forum?

sorry about the mis-post :rolleyes:

[raf]

the way to go is save the spreadsheet as a csv file, and then import this into MySQL.

only thing you need to take care off is that you don't have ';' symbols in your strings (or you should pick a different seperator) + that your dates are in yyyy-mm-dd notation and your decimal point is '.'

Or you can import the xls into MsAccess and then create a dumpfile from there.

If you really don't succeed at this, just attach a zipfile with your spreadsheets to a reply + a MySQL dumpfile of your tablestruct and i'll load it for you and create a dumpfile.

[StupidRalph]

Well since this is in the PHP forum.

<?php
$connection = mysql_connect("localhost", "db_user", "password") or die ("Unable to connect to server");
$db = mysql_select_db("database", $connection) or die ("Unable to select database");


$fcontents = file ('./test.txt');
# expects the txt file to be in the same dir as this script

for($i=0; $i<sizeof($fcontents); $i++) {
$line = trim($fcontents[$i]);
$arr = explode("\t", $line);
#if your data is comma separated
# instead of tab separated,
# change the '\t' above to ','

$sql = "insert into TABLENAME values ('".
implode("','", $arr) ."')";
mysql_query($sql);
echo $sql ."<br>\n";
if(mysql_error()) {
echo mysql_error() ."<br>\n";
}
}
?>

I save my excel sheets to be tab delimited and run this script. Of course you have to have the table set up in your DB already with the correct # of fields in the same order as the spreadsheet. And modify the mysql_connect, and mysql_select_db and SQL to fit your app.

[raf]

Well since this is in the PHP forum.
indeed :)

but even then, you should still use the LOAD DATA INFILE statement (that db-fronts use when you import a csv), because that will be so much faster and would reduce your code to

$connection = mysql_connect("localhost", "db_user", "password") or die ("Unable to connect to server");
$db = mysql_select_db("database", $connection) or die ("Unable to select database");
mysql_query("LOAD DATA INFILE 'test.txt' INTO TABLE yourtablename FIELDS TERMINATED BY '\t'");

[StupidRalph]

Well, little do you know raf....I knew about the LOAD DATA INFILE statement (or shall I say I've seeeeeeeeeen it) but I only used it from the command line...I didn't know how to run it in my scripting....so thanks...you've taught me something else....guess I have some reading to do.

[harlequin2k5]

thanks for your help guys

good golly I can be so silly sometimes - when I made the post yesterday I was expecting the usual quick response and then when I went to implement it I realized that I didn't have the spreadsheet on this computer

so then this morning I made sure to grab the file that I needed so I could work on it and it's the right file but I didn't setup the stupid table yet! I'm going to try your suggestions later this afternoon and we'll see how it goes

thanks again

[harlequin2k5]

ok - within phpmyadmin I came across an option to insert values from a textfile so of course I was all over that - I tried using an xls, a csv and a txt file and all three returned this error:

Error

SQL-query :

LOAD DATA LOCAL INFILE 'C:\\WINNT\\TEMP\\php127.tmp' INTO TABLE `tblProducts` FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

MySQL said:


The used command is not allowed with this MySQL version
Back
these are the versions:
phpMyAdmin 2.5.0
MySQL 4.0.12-nt

I haven't started designing the pages yet to display this info so for the moment I'm unable to use StupidRalph's suggestion

do you guys have any other ideas? I'm so completely new to this - I'm trying to use my book and the online manual and look at other examples and I'm just not getting it...


Edit: I attached a copy of the text file

[harlequin2k5]

Well since this is in the PHP forum.

I had thought it didn't belong here because I'm not ready to start designing my pages yet - I'm only just starting to get my tables worked out

02-16-2006 09:14 AM - This maybe should be in MySql forum?
do I pm the moderator to move it?

[raf]

Edit: I attached a copy of the text file
How did you create that txt? it doesn't look corrrect to me.
Can you attach the original spreadsheet so we can make a csv ourselves?

[StupidRalph]

I haven't started designing the pages yet to display this info so for the moment I'm unable to use StupidRalph's suggestion


You do not need to have your pages designed for my suggestion...what you do need is your database structure set up. If you have that, just copy the above php code and name it something like import_csv.php (Since you will be using comma delimited) Place that file and your .csv file in the same directory and run the script. That should populate the database.

And if that is a .csv file shouldn't it have had a csv extension? Did you use Save As in excel to make that?

[StupidRalph]

I got it to work for me.
I attached a sql dump file.

BTW......I used my script to import it :) So if a mod can move this back to the PHP forum....I kidd, I kidd. :thumbsup:

WAIT!!! I have to import it again.

NEVERMIND. I think my eyes were playing tricks on me. Use the .sql dump and import it and tell me if you see anything inauspicous.

You can modify the .sql file to change the name of the database, table, fields, fieldnames, and field properties. Just do a FIND and replace on "your_database" there are 3 total instances of it.

[StupidRalph]

As I stated I used SQLyog to export my SQL dump. And I know you will be using PHPMyAdmin to import it. There is one little difference between how the 2 handle SQLdumps. SQLyog exports and imports the complete dump including the creation of the database if necessary. PHPMyAdmin starts its dumps at the Table level. You must have a database already created. You have to select the database and then import the Dump. If you have a database declared in your Dump, then an arror will be generated. To remedy it simply comes down to 2 lines of code.create database if not exists `your_database`;

USE `your_database`;

If you are importing to SQLyog you need these two lines. If you are using PHPMyAdmin you do not. With that said here is the PHPMyAdmin exported Dump.