Convert CSV Blank cell to SQL NULL in Python

Question

I'm trying to convert blank cells in a csv file to NULL and upload them in SQL Server table so it shows as NULL rather blank. below code works but they load NULL as a string. Can you please help me to modify the code so it loads NULL in SQL ?

reader = csv.reader(f_in)    # setup code
writer = csv.writer(f_out)

row = next(reader)           # handle first line (with no replacements)
writer.writerow(row)
last_row = row               # always save the last row of data that we've written
variable = None

for row in reader:           # loop over the rest of the lines
    row = [x if x else "NULL" for x, y in zip(row, last_row)]  # replace empty strings
    writer.writerow(row)
    last_row = row




with open(outputFileName,'r') as fin: # `with` statement available in 2.5+    
dr = csv.DictReader(fin) # comma is default delimiter        
to_db = [(i['SubFund'], 
        i['Trader'], 
        i['Prime Broker/Clearing Broker']) 

cur.executemany("INSERT INTO Citco_SPOS (" + 
            "subfund, " +
           "trader, " +
            "prime_broker_clearing_broker, " + +
            "VALUES (?, ?,  ?);", to_db)
con.commit()

From Python I'd guess None but certainly not 'NULL'. But it's a guess. — roganjosh
– roganjosh, Commented Jan 4, 2017 at 21:48
What is your writer? How is it outputting the data - SQL commands to be executed, data elements to be imported, some form of direct-to-SQL-Server writer, or ??. — Laughing Vergil
– Laughing Vergil, Commented Jan 4, 2017 at 21:56
I tried using None, it's shows blank in SQL Server table after dataload. — Partha
– Partha, Commented Jan 4, 2017 at 21:56
Laughing Vergil - I've added more code to understand how it's inserting data in SQL Server. - Thanks. — Partha
– Partha, Commented Jan 4, 2017 at 22:02

Pramod Maharjan · Accepted Answer · 2017-01-05 05:32:01Z

4

This should work

import pyodbc
import csv
cnxn = pyodbc.connect(connection string)
cur = cnxn.cursor()
query = "insert into yourtable values(?, ?)"
with open('yourfile.csv', 'rb') as csvfile:
    reader = csv.reader(csvfile, delimiter=',')
    for row in reader:
        for i in range(len(row)):
            if row[i] == '':
                row[i] = None
        cur.execute(query, row)   
    cur.commit()

answered Jan 5, 2017 at 5:32

Pramod Maharjan

1711 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Partha Over a year ago

I tried above approach but it doesn't work. Still shows up blank in SQL database.

Partha Over a year ago

Promod - Your solution works. I had to do bit modification in code but technically that's the correct solution. Thanks. I've edited working version in answer. Thanks again.

UpWork · Accepted Answer · 2022-11-24 11:01:41Z

0

I found a great way of doing this. Step 1: Create a function in DB as Below:

CREATE FUNCTION dbo.ConvertBlankToNull(@tag nvarchar(100))
RETURNS varchar(100)
WITH EXECUTE AS CALLER
AS
BEGIN

Select  @tag=IIF(@tag='',NULL,@tag)
Return(@tag);
END;
GO

Step 2: Refer below for Cursor.execute statement.

cursor.execute("INSERT INTO dbo.NTable (CustomerID)
Select dboconvertBlanktonull(?) ",
row['Company.internalid'])

answered Nov 24, 2022 at 11:01

UpWork

1

Collectives™ on Stack Overflow

Convert CSV Blank cell to SQL NULL in Python

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related