Solved

How do I import a spreadsheet into a db?

Posted on 2003-11-15
16
867 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 

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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

622 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