Solved

How do I import a spreadsheet into a db?

Posted on 2003-11-15
16
860 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

758 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

18 Experts available now in Live!

Get 1:1 Help Now