say I have a table named XY_values with columns X_values and Y_values.
now, I have a text file xy.txt which contains those x values and y values, with x values in the 1st column and y values in the second column.
is there any SQL command to load those x and y values from text file into the XY_values table in the database?
thanks.
3 answers
hello, yes there is a very fast way to do this which is bulk insert in a query something like this
in the prev query the text file has a comma seperator which is assigned with the FIELDTERMINATOR to seperate columns from each others/
answered one year ago by:
1556
I have no luck with BULK INSERT, but when I try LOAD DATA LOCAL INFILE, it works for a comma delimiter, as below:
DROP TABLE IF EXISTS test;
CREATE TABLE test(x_values float, y_values float NOT NULL)
LOAD DATA LOCAL INFILE 'C:/xy5.txt' INTO TABLE test Fields TERMINATED BY ',' LINES TERMINATED BY '\r\n';
// xy5.txt file
1.00000000e+000, -1.00000000e+000
2.00000000e+000, -1.00000000e+000
3.00000000e+000, -2.00000000e+000
4.00000000e+000, 0.00000000e+000
5.00000000e+000, -1.00000000e+000
How about for space delimiter?
// xy_5.txt file
1.00000000e+000 -1.00000000e+000
2.00000000e+000 -1.00000000e+000
3.00000000e+000 -2.00000000e+000
4.00000000e+000 0.00000000e+000
5.00000000e+000 -1.00000000e+000
DROP TABLE IF EXISTS test;
CREATE TABLE test(x_values float, y_values float NOT NULL)
LOAD DATA LOCAL INFILE 'C:/xy_5.txt' INTO TABLE test Fields TERMINATED BY ' ' LINES TERMINATED BY '\r\n';
// above code does not work. Why?
answered one year ago by:
494
hello, you must keep in mind that reading the text file is actually reading text, so your problem lies in conversion of text rather than reading the text. so when you insert the text insert it into a temp table with varchar values, then convert the values read from varchar to float and insert them to your table this is how it is usually done. Beside this the preferable thing is to place a parenthesis beside the comma so the text file would be delimited in a better way and hope this helps.
answered one year ago by:
1556