All geek questions in one place

Import CSV to temporary table in MySQL

I have a pretty significant CSV file that can change from month to month and has approximately 450 data points per line. I also need to manipulate the data before inserting it into a persistent table. Therefore, I plan to import it into a temporary table, manipulate it, and then insert the data.

However, I cannot find information on how and how to import CSV into a temporary table (or, alternatively, somehow import dynamic CSV - the first row has column headers in CSV).

I tried to create a temporary table with one column, and then import the CSV, but did not import it. This is what I have tried so far:

DROP TEMPORARY TABLE IF EXISTS tmp_import; CREATE TEMPORARY TABLE tmp_import (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY); LOAD DATA LOCAL INFILE '/import.csv' INTO TABLE tmp_import FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

As an example of CSV, it is in the format (all data points are numbers):

 500,400,101,93,005,22 4,954,23434,123423432,44
+5
source share
1 answer

you can try the import option on the command line

 mysqlimport --ignore-lines=1 --fields-terminated-by=, --verbose --local -u [user] -p [database] /path/to/address.csv

/path/to/address.csv is the full path to the CSV file, in this case, be sure to specify the name of the address file with the name of the table, otherwise it will not work, the extension can be anything or nothing.

or

 LOAD DATA LOCAL INFILE 'File.csv' INTO TABLE tableName FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

reference: MySQL Documentation on LOAD DATA INFILE

0
source

Source: https://habr.com/ru/post/1235868/

More articles:


All Articles