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

Importing .csv that was output by Stata

User, date Message
Written by nmwhite0131
1 year ago
Category: Import/Export
4 posts since Wed, 12 Jun 13
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

Written by nmwhite0131
1 year ago
4 posts since Wed, 12 Jun 13
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
Written by ansgar
1 year ago
4936 posts since Fri, 07 Apr 06
Settings look right to me. What did you select as "Method" - server or client? Try server if you selected client.
Written by ansgar
1 year ago
4936 posts since Fri, 07 Apr 06
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.
Written by nmwhite0131
1 year ago
4 posts since Wed, 12 Jun 13
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.
Written by ansgar
1 year ago
4936 posts since Fri, 07 Apr 06
Now, what method did you use - server or client parsing?
Written by nmwhite0131
1 year ago
4 posts since Wed, 12 Jun 13
Server! Also, replacing all missing with \N worked.
Written by ansgar
1 year ago
4936 posts since Fri, 07 Apr 06
Super! Thanks for the feedback.
 

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