CODEWITHSUNDEEP
mysqldatabasebulkinsert
MEM
MEM

Reputation: 31307

How to prepare and insert data from a file into Mysql DB?

I have this data on an XLS file. The XLS file is a mess with a lot of data that I don't need. I will clean the file and I can re-arrange it.

I have something like this:

Level  - Code  - Description
 1        A       'foo foo'
 2       12331    'bar bar'
 3       13123    'bla bla'
 4       21321    'plim bar'
 5       12111    'foo plim'
 5       12111    'plim bla'
 5       12111    'bla plim'
 1        B       'bla bar'
 n        ...      ...

The Level defines the hierarchy position, like 1 is a top hierarchy. 5 is the lowest.

I will make use of the Adjacency List Model to store this information. So I believe I will have to store it like this:

id - description - parent_id
 1    'foo foo'      NULL
 2    'bar bar'       1
 3    'bla bla'       2
 4    'plim bar'      3
 5    'foo plim'      4
 6    'plim bla'      4
 7    'bla plim'      4
 8    'bla bar'      NULL
 n     ...            ...

What is the best (fastest, easiest) method to insert this information like this? Should I convert to a CSV? How should I format the XLS file so that I can insert this information by maintaining the hierarchy ?

There are 9000 lines in the XLS file, and I would love to avoid doing this one by one!

What methods should we consider in order to successfully import this data ?

Update: I have this mysqlworkbench software... I'm on Ubuntu and all opensource here.

Thanks a lot in advance.

Upvotes: 2

Views: 379

Answers (2)

Nikola Sivkov
Nikola Sivkov

Reputation: 2852

You can use Navicat to do the importing, the free version will do just fine.

Upvotes: 1

davek
davek

Reputation: 22895

Use the LOAD command, with csv-like data, if that's at all possible.

Here's an example:

LOAD DATA INFILE 'path-to-your-data-file'
INTO TABLE <your-table>
CHARACTER SET latin1
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(col1, col2,..., coln)
;

where IGNORE 1 LINES means your column headings in the data file will be ignored, and (col1, col2,..., coln) are the database columns to use.

You have lots of other options available to you (see http://dev.mysql.com/doc/refman/5.1/en/load-data.html) and LOAD is supposedly up to 20 times faster then INSERT for large data loads (according to http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html).

Upvotes: 2

Related Questions