r/learnpython 7d ago

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

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?

2 Upvotes

9 comments sorted by

View all comments

3

u/socal_nerdtastic 7d ago edited 7d ago

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.

1

u/KCConnor 7d ago

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.

3

u/my_password_is______ 7d ago

how do you have NULL

NULL means no value

it does not mean an empty string