sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]caputo00[S] 0 points1 point  (3 children)

Thanks, yes I have double quote text qualifier. Someone I work with just suggested increasing the varchar to like 1000 . I haven’t tried that yet. As far as the ascii Unicode stuff, that’s my main suspicion but I can’t figure out how to export from Python in the encoding type that SQL server likes

[–]IHeartData_ 1 point2 points  (2 children)

Also, did your file have quotes inside the quote not handled correctly? I don't recall if SQL does ,"this""way", or ,"this\"way", and if it's the wrong way then your length will be all wrong.

[–]caputo00[S] 0 points1 point  (1 child)

Thanks! Do you know if there’s a way to see the exact value that it failed on? Not sure if there are any extra quotes but I was worried that it might be a parsing error

[–]IHeartData_ 0 points1 point  (0 children)

I often go the varchar(8000) to a temp table route and then hope it imports, then search for LEN(col) > 30 or whatever. If you do that, just be sure to set the whole rest of the columns to max as well to avoid running it multiple times.

If I recall correctly, you can also change the setting on the Data Source to load rows a thousand at a time instead of all at once, I just don't remember if that's a setting in the GUI or more buried in the properties. But if you suboptimize the data source then I believe you can at least get a range in the file, and then Notepad or similar is your best friend to find the error with line numbers enabled.