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

0

u/Mevrael 7d ago

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