1

how can convert large txt file into mysql database, the file size 3 GB whith 11 million rows. Each row in the file is like this :

"1303179444","","","+20******5811","","","Ahmed","Al Emam","male","/ahmed.e.alemam","","ahmed.e.alemam","Ahmed Al Emam","description here","Ahmed Emam Dental Clinic","Dentist","","Dekernes  Ad Daqahliyah  iraq","MUST","ahmed.e.alemam@ahmed.e.alemam","0","0","0","1/1/0001 12:00:00 AM","1/1/0001 12:00:00 AM","","","","","","","","","",""

I tried to separate by comma and enter data to databases via php but it took a very long time and the whole file was not saved. i want another way to convert it to sql file

jarlh
  • 42,561
  • 8
  • 45
  • 63
egam321
  • 27
  • 6

1 Answers1

0

You can use mysql LOAD DATA INFILE command for this task.

First create the table:

CREATE TABLE items (
    field1 ...,
    field2 ...,
    field3 ...
    PRIMARY KEY (field1)
);

Then execute the command:

LOAD DATA INFILE 'data.csv' # full path to the csv file
INTO TABLE items         
FIELDS TERMINATED BY ','    # adjust to your csv format
ENCLOSED BY '"'             # adjust to your csv format
LINES TERMINATED BY '\n'    # adjust to your csv format
IGNORE 1 ROWS;              # if you have header row in the csv

You can also specify the mapping between columns in file and columns in table and even change the.

For example, suppose you want map:

[column1 in table] is [column3 in csv]
[column2 in table] is [column5 in csv]
[column3 in table] is [column1 in csv]
[column4 in table] is 2 * [column6 in csv]
[column5 in table] is the current timestamp
ignore [column2 in csv]
ignore [column4 in csv]

You can use the command:

LOAD DATA INFILE 'data.csv' # full path to the csv file
INTO TABLE items
FIELDS TERMINATED BY ','    # adjust to your csv format
ENCLOSED BY '"'             # adjust to your csv format
LINES TERMINATED BY '\n'    # adjust to your csv format
IGNORE 1 ROWS.              # if you have header row in the csv
(column3, @skip, column1, @skip, column2, @c4)
SET 
  column4= 2*@c4, 
  column5=CURRENT_TIMESTAMP;
kobi2c
  • 449
  • 5
  • There is more than one field that I do not want to add to the databases, how can I ignore it? – egam321 Nov 04 '22 at 08:59
  • Added example how to map columns in the command – kobi2c Nov 04 '22 at 09:29
  • Excuse me, I tried to understand what you mean, but I don't know, if I have 3 columns in the table they are ` id, name, phone` And I have the file like this `"idEcample4546","","","+1621963842","","","Ahmed","Gamal","male","https://www.facebook.com/10","","","James J. Cooper","","","","Mansoura","Mansoura","Mansoura Military Secondary School for Boys المنصورة الثانوية العسكرية بنين","100013005489949@facebook.com","0","0","0","1/1/0001 12:00:00 AM","1/1/0001 12:00:00 AM","","","","","","","","","",""` and i want only id , name and phone – egam321 Nov 04 '22 at 10:35