How do I import a spreadsheet into a db?

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
Steve234Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jcondeCommented:
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
jcondeCommented:
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
Steve234Author Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

jcondeCommented:
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
Steve234Author Commented:
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
jcondeCommented:
assuming none of your xls records have commas or line-feeds in them, yes, that's the correct line.

0
Steve234Author Commented:
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
Steve234Author Commented:
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
Steve234Author Commented:
Do you know what line syntax I should use as some of my cells do have commas inside of them?
Thanks.
Steve
0
SqueebeeCommented:
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
jcondeCommented:
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
Steve234Author Commented:
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
SqueebeeCommented:
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
SqueebeeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve234Author Commented:
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
Steve234Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.