1

I am trying to import a .sql file to MySQL using this command line

mysql -u username -p databasename < path/myfile.sql

Here is a sample of myfile.sql

INSERT INTO mytable (‘id’,’isDeleted’,’amount’,’date’,’description’,’description_clean’,’isDebit’,’account_id’,’user_uuid’) VALUES (3798,'0',35,'0000-00-00','test','test','1',108,'021752f8-7194-4a4b-8919-fb8216ecba07');
INSERT INTO mytable (‘id’,’isDeleted’,’amount’,’date’,’description’,’description_clean’,’isDebit’,’account_id’,’user_uuid’) VALUES (3799,'0',9.4,'2018-05-02','CB Frichti','CB Frichti','1',108,'316a3da6-821b-42c8-80ea-3c1ac437484e');
INSERT INTO mytable (‘id’,’isDeleted’,’amount’,’date’,’description’,’description_clean’,’isDebit’,’account_id’,’user_uuid’) VALUES (3800,'0',50,'0000-00-00','test','test','1',108,'316a3da6-821b-42c8-80ea-3c1ac437484e');

My file contains some date but some of those are wrong date 0000-00-00. Therefore the command above returns an error.

What is the best way to import ? Should I remove the line where date is wrong ? Is there a way to import only import lines that are correct and ignore those with error ?

Thanks for your help

Solal
  • 611
  • 2
  • 9
  • 26
  • 2
    Replace '0000-00-00' in a text editor with NULL (if the column is nullable) or a default date '1900-01-01' then you can either use the data as is or delete it one you have imported. (`INSERT INTO ‘‘` doesn't look correct either) – Alex K. Apr 05 '19 at 16:05
  • @AlexK sounds like you should put that as an answer :) – ChrisM Apr 05 '19 at 16:15
  • Insert into...nothing? – tadman Apr 05 '19 at 16:51
  • If you want to clean up your data first, keep in mind it's often a lot easier to edit as CSV and then [import that with `LOAD DATA INFILE`](https://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table). Most any spreadsheet tool can read and write CSV, making edits pretty effortless and bulk search/replace isn't even that hard. – tadman Apr 05 '19 at 16:52
  • Or load it into a temporary table where the columns are just character, and then write some SQL with case statements etc. to clean it up and populate the production table - that way you (effectively) have a record of what you changed, and you can potentially re-use it next time you get a batch of nasty data to load. – MandyShaw Apr 05 '19 at 17:03
  • @tadman the thing is I don't have the data in CSV format it is only in this format so far – Solal Apr 05 '19 at 17:59
  • You can always import it, dump it as CSV, fix it up, and reload it. – tadman Apr 05 '19 at 18:22

1 Answers1

3

As stated here

You can use the --force (-f) flag on your mysql import. Rather than stopping, MySQL will continue and just log the errors to the console.

For example:

mysql -u username -p -f -D path/myfile.sql < path/myfile.sql

Replacing invalid values with a default value (e.g., 1900-01-01) is not a good practice, you should prefer NULL instead

w4bo
  • 855
  • 7
  • 14