Search code examples
mysqlnullload-data-infile

MySQL load data in file - replace specific value with null for all columns

I am trying to use the MySQL load data infile statement to import the large csv file. I have a problem - when client provided us with this data they use a custom indicator of missing values - they are indicated as @NA. Now for all fields with text I have it as @NA and for all integer or float variables I get it as 0 instead of NULL. I wonder if there is a way to indicate a custom NULL placeholder when I upload the file for each and every field I try to import?

Solution

  • Seems like there is no way to do this with built-in MySQL load data infile function.

    I had to come up with the solution via Python Pandas - it has a nice support of custom NULL values.

    df = pd.read_csv(r"file.csv", sep=",", na_values = "@NA")
df.to_sql(name='table', con=engine,if_exists = 'append', index = False, chunksize=100000)