mysql bulk insert a text file

Question

I am having plain text file and i would like to move the content of that text file in to mysql table line by line can any one help me as it is not a csv file how can i achieve this

I designed my db as follows

RowID, int(11), NO, PRI,auto_increment
Text, varchar(94) // Here i have to insert line by line
RecordType, varchar(50)

Content sample

123456789007
545654665654654
6344534534543534534
786775645654654646
8456546456456546
9078565656546546546456456456

Can you be more precise then 'line by line'. I assume you mean that the text file is actually single column (text). If so, you can consider your text file CSV (even if it contains no commas). — Unreason
– Unreason, Commented Apr 8, 2011 at 8:19
Well if it has multiple columns, then pls provide with the sample of for the text file. Then the answers will be of better quality. — Unreason
– Unreason, Commented Apr 8, 2011 at 8:57
In your content sample I can see only single column. Rows are multiple. Safe to consider it CSV (or tab delimited, or whatever delimited since there are no multiple columns and no delimiters). — Unreason
– Unreason, Commented Apr 8, 2011 at 9:18

Unreason · Accepted Answer · 2011-04-08 08:12:24Z

3

It is not clear do you need to do this once/rarely or as a part of the system.

On the server side, you can do it with

LOAD DATA LOCAL INFILE '<file name>' INTO TABLE <table>;

See the full syntax to

change the line terminating string
skip rows
assign values to RecordType in the same command

EDIT: Specifically in your case

LOAD DATA LOCAL INFILE '<file name>' INTO TABLE <table>
(@Text)
SET Text=@Text

answered Apr 8, 2011 at 8:12

Unreason

12.7k2 gold badges39 silver badges51 bronze badges

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

4 Comments

Vivekh Over a year ago

Unreason i write this LOAD DATA INFILE 'c:\ACH1.txt' INTO TABLE tblACHDATA i am getting an error as c:ACH1.txt not found

Unreason Over a year ago

LOCAL is significant, try that.

Unreason Over a year ago

Semms windoze expect backslashes to be forward slashes, C:\ to be C:/ (I don't run mysql on win). Google is your friend.

Unreason Over a year ago

Read the docs from dev.mysql.com/doc/refman/5.1/en/load-data.html, the section on LOCAL. If using LOCAL location of your file should be available to client, if no LOCAL the to the server. But read the details on relative paths, then there's also security and permissions of the mysql process, etc... Bottom line is that this works and mysql docs might not be perfect, but are not wrong. Use them.

Developer · Accepted Answer · 2011-04-22 12:06:07Z

2

Try this

LOAD DATA local INFILE 'C:/Documents and Settings/Administrator/Desktop/Merge.txt' 
INTO TABLE tblachmaster
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

edited Apr 22, 2011 at 12:06

answered Apr 8, 2011 at 8:13

Developer

8,69743 gold badges137 silver badges254 bronze badges

Comments

Mitch Wheat · Accepted Answer · 2011-04-08 08:09:31Z

2

See LOAD DATA INFILE

answered Apr 8, 2011 at 8:09

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Comments

Devart · Accepted Answer · 2011-04-08 08:20:33Z

If your file is a CSV-like, you can use a LOAD DATA INFILE statement.

Text file like this (example below), you could load with a Data Import tool (text format) in dbForge Studio for MySQL. It supports headers and skips unnecessary lines.

================================================================
|      actor_id      |     first_name     |     last_name      |
================================================================
|         1          |      PENELOPE      |      GUINESS       |
----------------------------------------------------------------
|         2          |        NICK        |      WAHLBERG      |
----------------------------------------------------------------
|         3          |         ED         |       CHASE        |
----------------------------------------------------------------
|         4          |      JENNIFER      |       DAVIS        |
----------------------------------------------------------------
|         5          |       JOHNNY       |    LOLLOBRIGIDA    |
----------------------------------------------------------------
|         6          |       BETTE        |     NICHOLSON      |
----------------------------------------------------------------
|         7          |       GRACE        |       MOSTEL       |
----------------------------------------------------------------
|         8          |      MATTHEW       |     JOHANSSON      |
----------------------------------------------------------------
|         9          |        JOE         |       SWANK        |
----------------------------------------------------------------
|         10         |     CHRISTIAN      |       GABLE        |
----------------------------------------------------------------

Vincent Mimoun-Prat · Accepted Answer · 2011-04-08 08:06:25Z

0

Some PHP script would make an easy way to do this in a flexible and simple way. You'll need to give more details with respect to your text file format and the database schema (some examples from the text file).

Another solution could simply be to build a VBA macro to translate your text file into a valid CSV file. Then use something like phpMyAdmin to upload the CSV file to the DB

answered Apr 8, 2011 at 8:06

Vincent Mimoun-Prat

28.6k16 gold badges85 silver badges126 bronze badges

Collectives™ on Stack Overflow

mysql bulk insert a text file

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related