Add new rows to MySQL from txt file

Question

I have a bunch of data in a text file that looks like this:

file.txt

Name.n12d74.text.text
Name.n16d99.text.text
Name.n87d16.text.text

and my mysql table looks like this:

id int(11) NOT NULL AUTO_INCREMENT,
random varchar(50) DEFAULT NULL, 
data int(11) DEFAULT NULL,
`number` varchar(250) DEFAULT NULL,
fullname varchar(500) DEFAULT NULL,
PRIMARY KEY (testable) ENGINE=MyISAM DEFAULT CHARSET=latin1

Example data:

id   | data | random    | number | fullname
-----|------|-----------|--------|----------
1    | 5    | xjiogj    | n12d74 | Name.n12d74.text.text
2    | 5    | jfoisjf9s | n16d99 | Name.n16d99.text.text
3    | 5    | zjiwej    | n87d16 | Name.n87d16.text.text

id: is always the previous one +1
random: is just random lower case letters and numbers
number: is taken from the .txt file lines
fullname: is just a copy paste of the full line from the .txt file

I am trying to insert it into MySQL, i can do it through the terminal (ssh) or phpmyadmin.

I am very new mysql so appreciate any help.

check https://stackoverflow.com/questions/13579810/how-to-import-data-from-text-file-to-mysql-database — Madhur Bhaiya, Oct 05 '18 at 14:30

score 1 · Answer 1 · answered Oct 05 '18 at 14:30

What you are looking for is the LOAD INFILE query.

Syntax:

LOAD DATA LOCAL INFILE '/home/sellerp04/kk.csv' INTO TABLE tablename FIELDS 
TERMINATED BY ',';

Login using mysql -uusername -ppassword --local-infile databasename
The command "--local-infile" enables the uploading from a file option to the given database. This has to be done for each session.

Add new rows to MySQL from txt file

1 Answers1