Importing data into MySQL database

Question

I've had a look at a number of programs but haven't found a solution-

I have a database in mySQL which I want to fill with data from an Excel spreadsheet.

The database has a number of fields which have set datatypes and some that are default NULL. Anything I've managed to convert is leaving the NULL fields blank, but not NULL.

The cPanel I'm using only allows CSV or SQL. Any ideas where to go from here?

----ANSWER:----

All good answers so far, but whilst researching some more I found this YouTube video: http://www.youtube.com/watch?v=JDBYU9f1p-I

I hadn't heard of it before but I downloaded Talend Open Studio and while the number of functions available seem a bit over the top for what I need it's been the most straight forward and accurate way to move Excel files to MySql I've come across.

dani herrera · Accepted Answer · 2012-01-29 18:47:45Z

2

My strategy in some cases where only few rows (< 10K) and few tables (<10) are involved, is to make sql statements from excel values thoough excel formula:

=concat( "Inser into your table (f1, f2) values ("; B1; ", ... )

This is only a way for one time import and for a developer. You should consider other suggestion if you need to do this import in schedule time or is a user who imports data.

Of course your formula can include nulls values:

... iif( lengh( B1 ) = 0; NULL; B1 ...

answered Jan 29, 2012 at 18:47

dani herrera

52k11 gold badges133 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joachim Isaksson · Accepted Answer · 2012-01-29 18:47:45Z

1

If you want empty fields in column1 of table1 to map to NULL in the database, the obvious option is to simply import the file as you did and then do

UPDATE table1 SET column1=NULL where column1='';

May be tedious if we're talking many columns of many tables, but simple and works without creating your own solution.

answered Jan 29, 2012 at 18:47

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Comments

kba · Accepted Answer · 2012-01-29 18:58:08Z

1

As you mention, cPanel allows you to import .csv files. Luckily, you can export a spreadsheet as .csv using Microsoft Excel.

Here's an excerpt from the article Import or export text (.txt or .csv) files from Microsoft Office's website.

You can convert an Excel worksheet to a text file by using the Save As command.

1. Click the Microsoft Office Button, and then click Save As.

The Save As dialog box appears.

2. In the Save as type box, choose the text file format for the worksheet.

For example, click Text (Tab delimited) or CSV (Comma delimited).

answered Jan 29, 2012 at 18:58

kba

19.5k6 gold badges65 silver badges90 bronze badges

Comments

penpen · Accepted Answer · 2012-01-30 01:30:09Z

0

All good answers so far, but whilst researching some more I found this YouTube video: http://www.youtube.com/watch?v=JDBYU9f1p-I

I hadn't heard of it before but I downloaded Talend Open Studio and while the number of functions available seem a bit over the top for what I need it's been the most straight forward and accurate way to move Excel files to MySql I've come across.

answered Jan 30, 2012 at 1:30

penpen

9353 gold badges12 silver badges22 bronze badges

Comments

Gabe · Accepted Answer · 2012-01-30 01:39:04Z

0

I've used this website before to create insert statements. Makes it easy to copy and paste from Excel. Read the instructions carefully.

"The format that you MUST follow is this: Line one has the table name only. Line 2 is a tab separated list of field names. Lines 3+ are tab separated data rows."

http://tools.perceptus.ca/text-wiz.php?ops=7

answered Jan 30, 2012 at 1:39

Gabe

6,31213 gold badges65 silver badges103 bronze badges

Collectives™ on Stack Overflow

Importing data into MySQL database

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related