Importing .csv that was output by Stata

Hello,

Just switched to Heidi from Workbench. So much better.

Anyway, I'm trying to import a .csv file that I made in Stata. It is ; delimited without quotes. Here is an example line:

1;1984;WHEELWRIGHT NATURAL GAS SYSTEM, CITY OF;CITY HALL, MAIN STREET;WHEELWRIGHT;FLOYD;21071;KY;21;41669;;;;;

Where the ;;;; are null values. However, the import dialogue gives me an error as soon as it reaches one of these 'nothing' values. How do I get it to read the 'nothing' as a null?

Thanks,

Nicole

Sorry, forgot this bit:

My settings are:

Fields terminated by: ;
Fields enclosed by:
Fields escaped by:
Lines terminated by: \r\n

And the error is:

SQL Error (1366): Incorrect integer value: " for column ... at row 1

Settings look right to me. What did you select as "Method" - server or client? Try server if you selected client.

SQL Mode STRICT_TRANS_TABLES or STRICT_ALL_TABLES seems to trigger that error, when you try to insert an empty string into an integer column. See here.

I found that but I do not want to change the data integrity setting. I did find that .csv files treat empty values as '', so it's trying to put an empty string into a field that's supposed to be an integer. Reading the load file syntax page from SQL's website I'm thinking I need to replace all missing values with \N and use an escape character of '\'. I'm still confused about what an escape character really does, but I'll try it out anyway.

Now, what method did you use - server or client parsing?

Server! Also, replacing all missing with \N worked.

Super! Thanks for the feedback.