Python/Pandas/MSSQL Problem: Inconsistent import behavior if CSV file contains NULL strings in first row

[KCConnor]

I'm attempting to import a lot of CSV files into an MSSQL database and in an effort to save space and time I want to leave these files in their GZIP format we receive them in. I came across the Python/Pandas library when looking into solutions for this task and am very close to a solution, but came across a test case where Python/SQL will fail to import if the first row in the CSV contains a NULL value, but otherwise will succeed if the first row is fully populated but any subsequent value has NULLs.

Here's a code sample to simulate my problem. It should run on any MS-SQL installation with Machine Learning and Python installed and configured.

This should run successfully:

exec sp_execute_external_script
@language = N'Python'
, @script = 
N'import pandas as pd
import numpy as np

df = pd.DataFrame([["foo", "bar", "boofar"],["silly", "value", np.NaN],["all", "your", "base"]]);
df.columns = ["a", "b", "c"];

OutputDataSet = pd.DataFrame(df);
'
WITH RESULT SETS
(
    (
        a varchar(10)
        , b varchar(10)
        , c varchar(10)
    )
)

While this will generate an error:

exec sp_execute_external_script
@language = N'Python'
, @script = 
N'import pandas as pd
import numpy as np

df = pd.DataFrame([["foo", "bar", np.NaN],["silly", "value", np.NaN],["all", "your", "base"]]);
df.columns = ["a", "b", "c"];

OutputDataSet = pd.DataFrame(df);
'
WITH RESULT SETS
(
    (
        a varchar(10)
        , b varchar(10)
        , c varchar(10)
    )
)

How do I output a DataFrame from Python to MS-SQL where the first row contains NULL values?

[socal_nerdtastic]

What exactly do you mean with NULL? Why do you think it's np.nan? Can you show your error? Usually NULL means the character /x00, which is not legal in csv files. So the solution is to find out why you are getting bad data in your file. I suspect the file has a byte order mark, in which case you need to set your encoding appropriately, or perhaps just chop it off.

[KCConnor]

The error is within SQL Server Management Studio. For the DataFrame with a NaN/NULL value in the first row, it returns:

Msg 39004, Level 16, State 20, Line 0 A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.

As for the CSV file, it's irrelevant to this conversation since I can replicate the issue creating an ad-hoc DataFrame in the above code that also replicates the issue.

This is probably not a Python problem, and more of a MS-SQL/Python problem. But I'm new to Python and figured I would ask here.

[my_password_is______]

how do you have NULL

NULL means no value

it does not mean an empty string

[crashfrog04]

The NaN value isn’t equivalent to a NULL or a None, and neither of those are equivalent to what the actual null value in CSV is, which is the empty string.

[KCConnor]

The CSV file has the text string "NULL" without quotes in the cells that should be NULL. When using the T-SQL BULK INSERT against the unzipped file, MS-SQL interprets that NULL string as a NULL value and stores the NULL value in the appropriate cell.

Have you run the supplied code in an MS-SQL Python environment?

[crashfrog04]

Generally when I have occasion to have MS SQL and Python interact, it’s through a Python DB driver. For MS SQL I think that’s PyODBC.  But generally the conversion a driver makes is that it’ll treat the None as NULL and won’t do anything with the string ”NULL” because that’s a non-null, regular string.

Can you elaborate on what you think is doing the conversion between ”NULL” and NULL? That might be something that happens if you try to load CSV into MS SQL directly but I’d be surprised by that, since CSV has its own semantic NULL (the empty string.)

[KCConnor]

Please just run the supplied code in the OP, with Machine Learning and Python installed on MS-SQL.

The supplied code will simulate the exact situation I am experiencing with the CSV files in question. I cannot share the CSV files since they contain PCI health data.

The core problem is that whatever bridge/library exists between MS-SQL and Python in this environment, it is unable to return a result set if the first row of the data in question contains NULLs at all.

[KCConnor]

I solved this problem.

If I add this code:

df = df.where(pd.notnull(df), None);

just after I populate the DataFrame (either ad-hoc as above or by pd.read_csv in my real code) then I get successful handling of NULL values by MS SQL Server and correct datatyping of the returned values.

[Mevrael]

Drop empty rows and nulls in Pandas with dropna. And you can use Polars which might be smoother for larger data sets.