How to Fix the Incorrect Value Error When Importing NULL Values on an Excel CSV into a Table in MySQL

In this post, I'll be explaining a workaround when importing Excel CSVs with NULL values into MySQL.  I ran into this problem while delving into a personal financial project where I was missing some data from certain rows. Once I had prepared the CSV and created my table, I attempted to run a LOAD DATA INFILE command, however I received the following errors in fields that contained NULL values:

• Error Code: 1292. Incorrect date value: 'NULL' for column 'effective_date' at row 1
• Error Code: 1366. Incorrect decimal value: 'NULL' for column 'dollar_value' at row 1

It turns out that MySQL does not accept inserting NULL values from a CSV. Some forums listed using /N or "/N" and other options, but none would work for me. So instead I came up with this solution:

1. Make sure that the excel file is saved as a .csv
2. Save a backup of the .csv in case the original gets corrupted.
3. Install Notepad++ - it's easier to work with and includes some extra find and replace options that are not available through Notepad or MS Excel.
4. Open the .csv file in Notepad++.
5. Determine a unique identifier that will not otherwise be included in your data set.
• String Example - Use a random, uniqeu set of characters unlikely to show up in your data.
• ZZ#$@%$
• Number example - use a large number or small number unlikely in your data set
• 9999999999999 or 0.000000001
• In my table, I knew that all of my actual values were rounded to the nearest hundredth, so I changed all Nulls in DECIMAL fields to 0.001, and altered the field to accept 3 places past the decimal
• Date example - use a date unlikely to be in your data
• 1901-01-01





Find and Replace in Notepad++
Keyboard Shortcut CTRL + F 



6. Save the edited CSV.
7. Load the CSV into MySQL.
• Example: 
• LOAD DATA INFILE 'purchases_data.csv' INTO TABLE monthlydata
• FIELDS TERMINATED BY ','
• lines terminated by '\r\n'
• IGNORE 1 LINES
• (primary_key, description, price, purchase_date);
• NOTE: Often, Excel will add 3 commas at the end of each line, if you want to use the script in Step 7, you should also remove these by using find and replace. 
• Example: FIND ",,," REPLACE ""
8. Once the data has loaded, you will need to change your unique identifiers back to NULL. This can be done with the following statement for each of your data columns:
• UPDATE {table} SET {field} = NULL WHERE {field} = {unique identifier}
• Example: UPDATE monthlyData SET price = NULL WHERE price = '0.001'
9. You will need to repeat Step 8 for each field in your table. 

Yes, it is a tedious process! I'm sure there is a more elegant way, but with 3 hours of searching, I couldn't seem to find a better option. This one works and it ensures that you keep those NULL values within your data.