How to set null values while importing to phpmyadmin?

4
votes

I'm trying to import a .csv file into phpmyadmin where several fields are purposefully left blank. I need these field to register as null values and not just left as a blank string.

I know in the field properties you can select to allow "null" vs. "not null" for each field, but it still doesn't change cell to a null value while importing. After the import I can manually go check the null box for each field on each record, but that it unrealistic considering the amount of data I'm working with.

Is there a way to get phpmyadmin to set these blank cell to null values on import?

3
importnullphpmyadmin
Seems it is not clear. Having a NULL string in the csv in my case made the field "empty" but not NULL. Using \N as in the command line import gives the string "\N". I will keep exploring and hopefully came back. – mariotti
I found this: stackoverflow.com/questions/9282154/… but doesn't work OOTB for me. It might have to do with " within fields .. – mariotti

3 Answers

6
votes

I've been experience similar issues.

If you download a PhpMyAdmin CSV file with NULL values, you'll notice that NULL doesn't get encapsulated with quotes. So you'll have a line like this:

"1";"2";NULL;NULL

"2";"2";NULL;NULL

etc.

However, if you edit a CSV file in something like Open Office Calc, it might change this to put quotes around NULL, like so:

"1";"2";"NULL";"NULL"

"2";"2";"NULL";"NULL"

etc.

What should work is doing a search and replace for ["NULL" = NULL].

In your case, because you have empty (blank) fields, you'll be looking at doing a search and replace like this:

[,, = ,NULL,]

And probably a second pass for NULL values at the end of a line like so:

[,\n = ,NULL\n]

1
votes

Ancient question, but in case another MySQL noob like myself comes across it.

The find/replace rigamarole jmbertucci describes is avoidable if you're in charge of the creation of the CSV file, for example when you're backing up your own databases. In phpMyAdmin, if you select "custom" export method, you will see replace NULL with: and the default is NULL. Simply change that to "NULL" and you save yourself a step.

0
votes

I ran into this same problem and jmbertucci's answer worked great. I did run into one additional problem. In the case with a row of data like such

"hello","world",,,,,,

which has multiple sets of null values in a row doing a search replace with [,, = ,NULL,] as jmbertucci suggested won't work as you intend it to on the first pass. Instead you'll end up with

"hello","world",NULL,,NULL,,NULL

You should continue to do the search replace to until you end up with 0 occurrences replaced