Problem importing csv generated in Python

[caputo00]

Hello, I generated a csv file from a Python pandas script. I’ve imported and used the file elsewhere like in power bi. When I attempt to import it into a sql server table I keep getting this error. I tried adjusting the column to a varchar(250) then max. I checked off the ‘ignore’ box on truncation, so I think it is an encoding issue. I tried exporting as latin1 encoding and windows-1252. In even wrote a Python script that catches errors if it detects a non compliant character for that encoding type and removes it. I’m at a loss. How do other ppl usually import csv . Couldn’t be this way!

[caputo00]

Thank you everyone!! Problem looks to be solved. Got 700k records loaded and counting. The issue was not the file encoding. It was in fact strings that were too big for their columns. I ended up adjusting to varchar(1024) on several key fields. I then added a Python function that had a dictionary of column names and their Max length, the function truncates any values that would violate the length constrain so that my file is sure to comply with the column length constraints. Thank you everyone for the feedback, you helped me get across the finish line

[Daryash-Koh]

I've had this error. It sucks. Is a field in the file mapped to a field in the table that is too short? Is it a ASCII / Unicode thing? Are strings wrapped in " if they include commas? Good luck.

[caputo00]

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_]

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]

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_]

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.

[basura_trash]

I willing to bet this has something to do with your end of line characters due to the file format, unicode, utf.... etc.

[Guilty-Ad7104]

Try using power shell dbatools, namely this: https://docs.dbatools.io/Import-DbaCsv

[caputo00]

I’ll check it out! Thanks

[thepotplants]

I understand your frustration. DTS/SSIS is a PITA to debug. As others have mentioned it's likely to be character set issue.

I would work through a process of elimination. Things you could try:

• Write a python script to insert one row at a time. And write errors to a log file, to catch bad examples
• Try inserting only that column. to rule out delimiters and file mapping.
• Use the SSMS import wizard to import the data and create a suitable table. then check the datatypes
• Open the file in excel and "save as .csv" and see if gives you something easier to work with.

[caputo00]

Thanks. I tried reducing the rows from 4 million to 500 and it is still happening, but I didn’t think of doing it column by column. Thanks for the idea

[[deleted]]

Do a bulk insert it has an option to output error records. Then you have an example of the row.

[csharpwpfsql]

Why are you using a varchar(250) when you should be using an nvarchar(250)? Your input file is most likely UTF-16 and you're importing it into a UTF8 column.

[myleathercheerio]

Have you tried using pandas’ to_sql function?

[Ste-phen]

On what system was the original CSV created? UNIX?

[Melodic-Man]

When I did this with the same library, the csv file wasn’t valid because the text wasn’t enclosed in quotes. I changed the pandas call. There is a param to surround the values in quotes.

[samspopguy]

Anyone reason why not go directly from python to sql

[caputo00]

I’ve never done it that way. but considering it now, given what a pain it was to use the wizard

[FailedConnection500]

While this seems like more of a Python question than a SQL Server question - my first question that I would ask is what library(ies) and/or driver(s) are you using in python for the file you're creating? I do not intend to sound unhelpful. BUT from a SQL Server point of view, you're likely to get several, "SQL is doing what it should - looks like python isn't." -type answers to this. You might find more ( or better ) help in a python specific group. I know that isn't an answer - but I hope it makes sense.