CODEWITHSUNDEEP
mysql
Sebastian
Sebastian

Reputation: 1

Import CSV with 5 columns into MySQL 8.3 DB with three tables

I have a CSV file with the following header row: col1, col2, col3, date, source.

I have the following tables and columns in my database:

Table1
ID
src

Table2
ID
coll
DateCollected

Table3
ID
column1
column3
column2

I want to import the CSV into the database and I've been trying to use the LOAD DATA INFILE command. However, I cannot figure out how to properly map the CSV columns to Table columns. I know I'll have to use three LOAD DATA INFILE commands, one for each table, because not all of the columns in the CSV file go into ever table. Here is the mapping I'm hoping to achieve:

Table1.src = csv.source
Table2.DateCollected = csv.date
Table3.column1 = csv.col1
Table3.column3 = csv.col2
Table3.column2 = csv.col3

(NOTE: The column numbers in Table3 are NOT in the same order as they appear in the CSV)

I've tried variations on this theme:

LOAD DATA INFILE '/var/lib/mysql-files/testTripsForDB.csv'
INTO TABLE Table1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@dummy,@dummy,@dummy,@dummy,source)
;

LOAD DATA INFILE '/var/lib/mysql-files/testTripsForDB.csv'
INTO TABLE Table3
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(col1,@col2,@col3,@dummy,source)
SET @col2 = col3, @col3 = col2
;

I'm not sure which column names to put in the parentheses in the row after "IGNORE 1 ROWS" and I'm not sure which column names to put in the SET command or if the SET command is even needed.

Upvotes: 0

Views: 62

Answers (1)

Schwern
Schwern

Reputation: 164769

load data infile ignores CSV headers.

What's between the parens is where to put each value in the order they appear in the CSV.

@col1 indicates a variable to put the value into. col1 indicates a column to put the value into. So for table3...

load data infile '...'
into table table3
fields terminated by ','
ignore 1 rows
(column1, column3, column2, @dummy, @dummy)

That puts the first CSV field into table3.column1, the second into table3.column3, and the third CSV field into table3.column2. The rest are put into the @dummy variable and effectively ignored.

For table1...

load data infile '...'
into table table1 
fields terminated by ',' 
ignore 1 rows 
(@dummy,@dummy,@dummy,@dummy,src)

That puts the 5th column of the CSV into table1.src.

set is useful for when you need to change the data. For example, let's say we needed to multiply the value in the first field by 100 before inserting it. Put the field data into a variable (@column1) then use set column1 = @column1.

load data infile '...'
into table table3
fields terminated by ','
ignore 1 rows
(@column1, column3, column2, @dummy, @dummy)
set column1 = @column1 * 100

This puts first field into the variable @column1, multiplies it by 100, and puts the result into table3.column1. The second CSV field goes into table3.column3, and the third CSV field into table3.column2.

(column1)

and

(@value)
set column1 = @value

Do the same thing.

Upvotes: 0

Related Questions