Solved

How do I import a spreadsheet into a db?

Posted on 2003-11-15
16
861 Views
Last Modified: 2012-05-04
hi,

How do I import an Excel spreadsheet into a phpMyAdmin type of database?  I see an export tab, but no import tab.  

My db has an autoincrement as the first field, and a autodate/timestamp field.  Do I need blank columns in my spreadsheet, one at the beginning and one in the middle of the spreadsheet, to correspond to those two fields too?  

The last 3 fields in my db are not used at this time; do they need to be removed for the import to happen properly?  

As you can tell, I'm not a programmer so please answer clearly, simply and in detail - it would be better to assume I don't know anything about this.  My spreadsheet I'm trying to import goes from column A to columnAB (28 columns) and is 43,890 rows long.  Do I need a special script, or do I just need to find the correct tabs and commands to click?

Should the spreadsheet file be saved as a xls or txt or some other format for the import?

Thank you for your help.

Steve
0
Comment
Question by:Steve234
  • 8
  • 5
  • 3
16 Comments
 
LVL 7

Expert Comment

by:jconde
ID: 9756499
Hi!

First, save your spread sheet in a CSV file.  This will generate a plain text file looking similar to the following example:

C:\my>type Book1.csv
test1,test2,2,32
testx,testxx,33,323
xxxx,dddd,23.23,33

Afterwards, create a table for your information ...

For the above example, a good table definition would be

CREATE TABLE book1 (
  Field1 varchar(10),
  Field2 varchar(10),
  Field3 float,
  Field4 int,
  primary key(Field1)   // make sure you define the correct primary keys !!
);

Then issue a query that looks similar to:

mysql> LOAD DATA INFILE "C:/my/Book1.csv" INTO TABLE book1 FIELDS TERMINATED BY ',' LINES TERMINATED BY "\n";

then test things out:

mysql> select * from book1;
+--------+--------+--------+--------+
| Field1 | Field2 | Field3 | Field4 |
+--------+--------+--------+--------+
| test1  | test2  |      2 |     32 |
| testx  | testxx |     33 |    323 |
| xxxx   | dddd   |  23.23 |     33 |
+--------+--------+--------+--------+
3 rows in set (0.01 sec)
0
 
LVL 7

Expert Comment

by:jconde
ID: 9756554
BTW, depending on the type of data your xls file contains, your LOAD DATA INFILE query might get more complex.

Make sure you read the documentation about LOAD LOCAL INFILE in
http://www.mysql.com/doc/en/LOAD_DATA.html
0
 

Author Comment

by:Steve234
ID: 9756718
hi,

I'm trying to get the thing to work now, except I get an access error, but I do have access because I can manually insert products.

Steve
0
 
LVL 7

Expert Comment

by:jconde
ID: 9756744
is LOAD LOCAL DATA enabled in your mysql installation ?

try starting mysql (the client app, not the server) with as follows:

mysql --local-infile=1 [database]
0
 

Author Comment

by:Steve234
ID: 9756758
I have created the table and fields with the correct var types.  The table is called products, the first 25 rows of the spreadsheet was saved as sample1.csv in the C:\ root of my hard drive; so is this the proper line and do I put it in the "Run SQL query/queries on database... "  textarea box?

LOAD DATA INFILE "C:/sample1.csv" INTO TABLE products FIELDS TERMINATED BY ',' LINES TERMINATED BY "\n";

Thanks.

Steve
0
 
LVL 7

Expert Comment

by:jconde
ID: 9756800
assuming none of your xls records have commas or line-feeds in them, yes, that's the correct line.

0
 

Author Comment

by:Steve234
ID: 9756827
yes, some of my cells definitely have commas inside of them.  Two of the cells in the row is a description a sentence long and could have commas and other punctuation in them.

I was just told by my web host (which I don't hold in high credability) that I probably don't have local access so I will have to upload my file to the cgi-bin directory with my ftp program, then run the script.

Thank you for your help.

Steve
0
 

Author Comment

by:Steve234
ID: 9756849
I doubt any of my cells have line feeds in them, but if they do, I will just have to delete them individually and re-run the script.  Is there any way to do a global search for linefeeds?  43,000 rows is a lot of scrolling to check.

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Steve234
ID: 9756925
Do you know what line syntax I should use as some of my cells do have commas inside of them?
Thanks.
Steve
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9757587
Try LOAD DATA INFILE "C:/sample1.csv" INTO TABLE products FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\n";

Excel usually encloses strings in double quotes. By adding OPTIONALLY ENCLOSED BY '"' will tell MySQL that string fields have double quotes wrapping them.
0
 
LVL 7

Assisted Solution

by:jconde
jconde earned 250 total points
ID: 9758051
Squeebee is correct ... add OPTIONALLY ENCLOSED BY '"' to your query ...

so, what will happen now is fields that have commas in them will automatically be enclosed by ".

For example:

"test1, test, test2",test2,2,32
testx,testxx,33,323
xxxx,dddd,23.23,33


As you can see, the first row / record is "test1,test test2" ... which means the complete value will be inserted into the database without problems.
0
 

Author Comment

by:Steve234
ID: 9758053
hi,

I still get access denied errors using that script.  And no one at the web hosting company knows how to help (Is it normal for a web hosting company to say "sorry, we don't give that kind of 'tutorial help', Go read the manual" or should such a problem be traditionally handled by web hosting tech support?)

Regardless, using the "Query window" link under the list of tables in the margin to import the data rather than a script, then seems to bypass the access issue, but I then get a syntax error.  So I still need some guidance.

Thanks.

Steve
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9760264
Well, as long as a hosting company has software properly configured I would consider it an unexpected favor for them to provide any tutorial type help. If I have a problem programming PHP I cannot expect any help from the hosting company beyond a working install of PHP, know what I mean? If they are a good sized company they could be quite busy.

Anyway, exactly what are you trying to do and exactly what is the error message?
0
 
LVL 17

Accepted Solution

by:
Squeebee earned 250 total points
ID: 9760385
Ok, now here is a question:

LOAD DATA INFILE "C:/sample1.csv" INTO TABLE products FIELDS TERMINATED BY ',' LINES TERMINATED BY "\n";

If your data file currently residing at:

c:/sample1.csv

On the SERVER? To use LOAD DATA you must point to a file on your server, not your local machine.

What does this query return?

SHOW VARIABLES like '%local%';
0
 

Author Comment

by:Steve234
ID: 9760683
ok, I had it residing on my local machine in the C: root directory.  I thought the browse was picking it up from there.  So the data file should be ftp'd to the server first?  I'll try that.

Steve
0
 

Author Comment

by:Steve234
ID: 9829920
hi,

Thank you for your help and assistance.  Sorry I was so slow in closing the question, but things came up that had to be taken care of first.  

My webmaster came up with a script that accomplished the task, but I've learned some, and I do sincerely appreciate your attempt to help me.

Thank you all again.

Steve
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MySQL DB vs MARIA DB compatibility for PHP and Wordpress 2 100
MySQL query to show different levels from a table. 5 56
html input clean up 3 47
Amazon Redshift 2 24
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now