Loading data to MySQL from CSV，but the attributes in SQL table need to do same calculation from the data

Question

I need to do an assignment that load date from csv, but the data attribute in the csv ins not fit with the SQL table. The SQL table schema like

BUS(id, Duration, totalcustomer)

But the csv file title like

id=1, Start time =00:00:00, end time=00:01:11, male customer=20, female customer=20

The output the Table will like id=1, drration=00:01:11, total customer =40

So, in the SQL table,Duration= end time -start time, and totalcustomer=male customer + female customer.

I try SQL expressions like below but not work

LOAD DATA LOCAL INFILE 'BUS.csv'  
INTO TABLE BUS  
FIELDS ENCLOSED BY '\"'TERMINATED BY ','  
LINES TERMINATED BY '\n'  
(@id, @Duration, @totalcustomer)  
SET  'end time' - 'start time' = @Duration,  
     'male customer'+ 'female customer' = @totalcustomer

So, do I need to make a new table to load the csv data and do the calculation in MySQL?

what kind of data do we have in male customer and female customer ? is it a number field or a name ? — JagaSrik, Jul 12 '20 at 06:31
your question need more clarity, very hard to assume what you intent to say, why are you doing + with both of the fields, please put the sample output which is expected — JagaSrik, Jul 12 '20 at 06:31
The example for the csv, like id=1, Start time=00:00:00, end time00:01:11, male customer=20, female customer=20, the start time and end time is Date, and the customer is INT. So the output the Table will like id=1, drration=00:01:11, total customer =40, sorry for my unclear description — Lee Alex, Jul 12 '20 at 06:36
Can you please edit your question and provide some sample data and expected output — JagaSrik, Jul 12 '20 at 06:39
Ok, that's work , but only in MYSQL Command Line Client, not working on the cell and workbench. — Lee Alex, Jul 15 '20 at 06:41

score 0 · Answer 1 · answered Jul 12 '20 at 08:39

The variables in the load statement are for the columns in the table and/or temporary variables which can be manipulated and loaded with the set statement. So your load should look more like

(id, @starttime, @endtime, @malecustomer,@femalecustomer)  
SET  duration = @endtime - @starttime,  
     totalcustomer = @malecustomer + @femalecustomer

score 0 · Accepted Answer · edited Aug 11 '20 at 21:44

We can do it in Load data itself but it took time for me to figure it out, Let me know if you face any issues it works for me, make sure you replace schema_name and path in the query accordingly and also avoid spaces in column names. Use duration table in database as time datatype.

Create table used :

create table BUS
(
id int, 
 Duration time, 
 totalcustomer int
 );

The Load statement :

LOAD DATA LOCAL INFILE 'C:\\Users\\path\\Downloads\\BUS.csv'  
INTO TABLE schema_name.bus FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS
(id, @END_TIME,@START_TIME,@MALE_CUST,@FEMALE_CUST)  
SET 
     Duration = timediff(str_to_date(@START_TIME,'%H:%i:%S'),str_to_date(@END_TIME,'%H:%i:%S')),
     totalcustomer = @MALE_CUST+@FEMALE_CUST;

This is the csv data i have used

ID,START_TIME,END_TIME,MALE_CUST,FEMALE_CUST
1,00:00:00,00:01:11,20,30

The output in the table BUS after executing the Load data statement

# id    Duration    totalcustomer
   1    00:01:11      50

Here is what i understood, you have the first table column id is not using @ because it is getting mapped automatically to the first ID column name in csv, rest of the columns are not directly mapped so we use @ and col_name in

(id, @END_TIME,@START_TIME,@MALE_CUST,@FEMALE_CUST)

You can try change this above line by using @ID you might have to map id=@ID after SET

you should use table columns after SET

Duration =  
totalcustomer =

and equate it to the data columns from csv source on RHS.

When　I run the code, I will have a message: Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access. Do I need to change any parameter? — Lee Alex, Jul 14 '20 at 05:29
you should login to mysql using cmd in windows 'mysql --local-infile=1 -u root -p1' — JagaSrik, Jul 14 '20 at 05:35
https://stackoverflow.com/questions/59993844/error-loading-local-data-is-disabled-this-must-be-enabled-on-both-the-client — JagaSrik, Jul 14 '20 at 05:35
It is work, but only work ion MySQL Command Line Client, and in the path all \'s need to change to /, like'C:/Users/path/Downloads/BUS.csv'.It is not working in Cell and workbench, — Lee Alex, Jul 15 '20 at 06:45
its better to use command line interface for this than workbench, i did the same. — JagaSrik, Jul 15 '20 at 06:56

Loading data to MySQL from CSV，but the attributes in SQL table need to do same calculation from the data

2 Answers2