Solved

How do I import a spreadsheet into a db?

Posted on 2003-11-15
16
864 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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