2

I am new to MySQL, so kindly explain your answers in a simple way!

I have a csv file which has 2 date columns. I want to import the csv into MySQL and I am using the following method:

  1. Create an empty table in the database with proper column types. So both date columns were assigned a DATE datatype and so on.

  2. Load the csv using following query:

     LOAD DATA LOCAL INFILE 'C:\Users\Shrey\OneDrive\Desktop\assignments\Samyak\Comorbidity Covid-19.csv'  
     INTO TABLE `comorbidity` 
     FIELDS TERMINATED BY ',' 
     LINES TERMINATED BY '\r\n'
     IGNORE 1 ROWS;
    

The issue is that both date columns are being imported but with all the entries like 0000-00-00.

I tried the following query as suggested by @Ergest Basha:

LOAD DATA LOCAL INFILE 'C:/Users/Shrey/OneDrive/Desktop/assignments/Samyak/Comorbidity Covid-19.csv'  
INTO TABLE `comorbidity`  
FIELDS TERMINATED BY ','  
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

( 
@Current_date_var, @Start_Date_var, State, 
Condition_group, `Condition`, 
Age_group, Covid19_deaths, Number_of_mentions
)
SET `Date_as_of` = STR_TO_DATE(@Current_date_var,  '%m-%d-%Y') ,
  Start_date = STR_TO_DATE(@Start_Date_var,  '%m-%d-%Y')
;

But still get the same output!

This is a sample of the data I am working with:-

Date as of Start date State Condition group Condition Age group Covid-19 deaths Number of mentions
02-06-2022 01-01-2020 United States Respiratory diseases Influenza 0-24 1167 1127
02-06-2022 01-01-2020 United States Respiratory disease pneumonia 25-51 2254 5458
02-06-2022 02-02-2020 United States Respiratory disease pneumonia 52-76 5545 2222

Any help is much appreciated!

2
  • Yes Condition is a reserved word but it is also the name of one of my columns. That is why I have included it in the column list along with other column names!
    – spectre
    Feb 16, 2022 at 16:47
  • 1
    Using reserved word (and even keyword) as object name is wrong practice. I strongly recommend you to rename this column.
    – Akina
    Feb 16, 2022 at 17:26

1 Answer 1

4

First your query contains MySQL Reserved Words Condition and Current_date please avoid using reserved words. and secondly you are using SET twice.

Try:

LOAD DATA LOCAL INFILE 'C:\Users\Shrey\OneDrive\Desktop\assignments\Samyak\Comorbidity Covid-19.csv'  
INTO TABLE `comorbidity` 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(@Current_date_var, @Start_Date_var, State, Condition_Group, `Condition`, Age_group, Covid19_deaths, Number_of_mentions)
SET `Current_date` = STR_TO_DATE(@Current_date_var,  '%m-%d-%Y') ,
    Start_Date = STR_TO_DATE(@Start_Date_var,  '%m-%d-%Y')
;

Demo

Be aware that STR_TO_DATE(@Current_date_var, '%m-%d-%Y') returns the date in the yyyy-mm-dd format, so you should have a date type column.

For example:

SELECT STR_TO_DATE('02-21-2022',  '%m-%d-%Y');

Returns:

STR_TO_DATE('02-21-2022',  '%m-%d-%Y')
2022-02-21

Demo

Example with the updated data:

mysql> CREATE TABLE `comorbidity` (
  `State` varchar(20) NOT NULL,
  `Condition_Group` varchar(50) NOT NULL,
  `Condition` varchar(45) NOT NULL,
    ->   `Current_date` date NOT NULL,
    ->   `Start_Date` date NOT NULL,
    ->   `State` varchar(20) NOT NULL,
    ->   `Condition_Group` varchar(50) NOT NULL,
    ->   `Condition` varchar(45) NOT NULL,
    ->   `Age_group` varchar(15) NOT NULL,
    ->   `Covid19_deaths` int NOT NULL,
    ->   `Number_of_mentions` int NOT NULL
    -> ) ;
Query OK, 0 rows affected (0.57 sec)

mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
2 rows in set (0.00 sec)

mysql>
mysql> LOAD DATA INFILE '/var/lib/mysql-files/comorbidity.csv'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@Current_date_var, @Start_Date_var, State, Condition_Group, `Condition`, Age_group, Covid19_deaths, Number_of_mentions)
SET `Current_date` = STR_TO_DATE(@Current_date_var,  '%m-%d-%Y') ,
    Start_Date = STR_TO_DATE(@Start_Date_var,  '%m-%d-%Y')
;

    -> IGNORE INTO TABLE `comorbidity`
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 ROWS
    -> (@Current_date_var, @Start_Date_var, State, Condition_Group, `Condition`, Age_group, Covid19_deaths, Number_of_mentions)
    -> SET `Current_date` = STR_TO_DATE(@Current_date_var,  '%m-%d-%Y') ,
    ->     Start_Date = STR_TO_DATE(@Start_Date_var,  '%m-%d-%Y')
    -> ;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql>
mysql> select * from comorbidity;
+--------------+------------+---------------+----------------------+-----------+-----------+----------------+--------------------+
| Current_date | Start_Date | State         | Condition_Group      | Condition | Age_group | Covid19_deaths | Number_of_mentions |
+--------------+------------+---------------+----------------------+-----------+-----------+----------------+--------------------+
| 2022-02-06   | 2020-01-01 | United States | Respiratory diseases | Influenza | 0-24      |           1167 |               1127 |
| 2022-02-06   | 2020-01-01 | United States | Respiratory disease  | pneumonia | 25-51     |           2254 |               5458 |
| 2022-02-06   | 2020-02-02 | United States | Respiratory disease  | pneumonia | 52-76     |           5545 |               2222 |
+--------------+------------+---------------+----------------------+-----------+-----------+----------------+--------------------+
3 rows in set (0.00 sec)
5
  • This gives me following error : Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Current_date = STR_TO_DATE(@Current_date_var, '%m-%d-%Y') , Start_Date = ST' at line 8. I am getting this error. I made sure both the date column are of DATE type too!
    – spectre
    Feb 17, 2022 at 6:10
  • Ok that error is solved and the records are being imported. But still the date columns are in the 0000-00-00 format
    – spectre
    Feb 17, 2022 at 8:41
  • Added some rows
    – spectre
    Feb 17, 2022 at 9:04
  • Still no luck! I have literally copy pasted the same code and still no luck. I have updated my question with your code (the second code in the question is what I tried)
    – spectre
    Feb 17, 2022 at 9:56
  • Another problem with using only \n is that some of my column values are long. So If I use only \n then half the text goes into another columns. Basically it messes up my table!
    – spectre
    Feb 17, 2022 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.