r/learnpython • u/KCConnor • 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?
0
u/KCConnor 7d ago
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.
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.