CODEWITHSUNDEEP
mysqlsqldatabaseexcelimport-from-excel
penpen
penpen

Reputation: 935

Importing data into MySQL database

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.

Upvotes: 0

Views: 975

Answers (5)

Gabe
Gabe

Reputation: 6097

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

Upvotes: 0

penpen
penpen

Reputation: 935

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.

Upvotes: 0

kba
kba

Reputation: 19476

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).

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

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.

Upvotes: 1

dani herrera
dani herrera

Reputation: 51715

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 ...

Upvotes: 2

Related Questions