I'd like to import data into my database. I have a .csv file full of 223 million entries! phpMyAdmin cannot handle that, so I am hoping MySQL workbench can help me!
The file, however, is a mere text (.txt) file which simply uses the .csv extension, as no line is separated by anything but \n
Meaning the file looks like this
data1
data2
data3
data4
There is only one column which each has one row. I could write a script that separates all of them by a comma or so, but I think there's a better way to do it?
I am using the Table Data Import Wizard in MySQL Workbench and it detects the file format as CSV, however, when I get to the third step it gives me the following error message:
Unhandled Exception: list index out of range
Check the log for more details.
I don't even know where to find the log. However, I am sure it has something to do with the configuration that is:
Field Separator ;
Line Separator LF
Enclose Strings in *
null and NULL word as SQL keyword YES
I've also tried creating a table using a SQL tab and querying it by hand:
USE theintal_domains;
CREATE TABLE domain2 (
id INT NOT NULL AUTO_INCREMENT,
domain VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
LOAD DATA INFILE '/var/lib/mysql/domains.csv'
INTO TABLE domain2
LINES TERMINATED BY '\n'
IGNORE 0 ROWS;
However, I get the error message: Error Code: 1366. Incorrect integer value: 'thingonfirstline ' for column 'id' at row 1
As I understand it, since my csv file / list has no column ID which is supposed to be an integer, it cannot write the first row (which is a string) to that since the data types don't match. However, my list has no line numbers, is there a way to automatically add them when going down the list? Again, my csv file is just a bunch of one-word text separated by \n. What can I do?
How can I change these settings to import my data as a table in MySQL or is there a better different way to do it?
Any help is deeply appreciated!
