Date not being imported from csv in proper format in MySQL



  • 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!



  • First your query contains https://dev.mysql.com/doc/refman/8.0/en/keywords.html 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')
    ;
    

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1850517047b1e27ca4c4f5ed73e81414

    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
    

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=481b7be3a0cc6056668870921d30bd7c

    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)




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2