distal-attribute
distal-attribute
distal-attribute
distal-attribute

Importing .csv that was output by Stata

nmwhite0131 posted 2 years ago in Import/Export
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

nmwhite0131 posted 2 years ago
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
ansgar posted 2 years ago
Settings look right to me. What did you select as "Method" - server or client? Try server if you selected client.
ansgar posted 2 years ago
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.
nmwhite0131 posted 2 years ago
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.

ansgar posted 2 years ago
Now, what method did you use - server or client parsing?
nmwhite0131 posted 2 years ago
Server! Also, replacing all missing with \N worked.
ansgar posted 2 years ago
Super! Thanks for the feedback.

Please login to leave a reply, or register at first.