Export MS SQL table with `null` values to CSV

Question

I am trying to figure out how to create a csv file that contains the null values I have in my MS SQL database table. Right now the script I am using fills up the null values with '' (empty strings). How I am supposed to instruct the csv Writer to keep the null values?

example of source table

ID,Date,Entitled Key
10000002,NULL,805
10000003,2020-11-22 00:00:00,805

export_sql_to_csv.py

import csv
import os
import pyodbc

filePath = os.getcwd() + '/'
fileName = 'rigs_latest.csv'

server = 'ip-address'
database = 'db-name'
username = 'admin'
password = 'password'

# Database connection variable.
connect = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' +
                                 server+';DATABASE='+database+';UID='+username+';PWD=' + password)
cursor = connect.cursor()

sqlSelect = "SELECT * FROM my_table"


cursor.execute(sqlSelect)
results = cursor.fetchall()

# Extract the table headers.
headers = [i[0] for i in cursor.description]

# Open CSV file for writing.
csvFile = csv.writer(open(filePath + fileName, 'w', newline=''),
                     delimiter=',', lineterminator='\r\n',
                     quoting=csv.QUOTE_NONE, escapechar='\\')

# Add the headers and data to the CSV file.
csvFile.writerow(headers)
csvFile.writerows(results)

Example of the result after running the above script:

ID,Date,Entitled Key
10000002,,805
10000003,2020-11-22 00:00:00,805

The main reason why I would like to keep the null values is that I would like to convert that csv file into series of insert SQL statements and execute those against Aurora Serverless PostgreSQL database. The database doesn't accept empty strings for the type date and results in that error: ERROR: invalid input syntax for type date: ""

What would you like the CSV file to contain? Anyway, as it is a text file, it can only contain string values, delimited with a separator (here the comma). But if you know which columns have to be tweaked and which values you would like instead of empty strings, it is easy to replace the relevant empty values in the Python script... — Serge Ballesta
– Serge Ballesta, Commented Nov 23, 2020 at 16:34
Ideally, I would like to keep the NULL values that I have in my db so that when I use that csv file to generate SQL statements the NULL values will go into them as well instead of empty strings. I think I only need the NULL DATE type columns from my MS SQL db to be saved as NULL values instead of '' — Georgi Koemdzhiev
– Georgi Koemdzhiev, Commented Nov 23, 2020 at 16:44

James Pringle · Accepted Answer · 2020-11-23 16:52:10Z

1

As described in the docs for the csv module, the None value is written to CSV as '' (empty string) by design. All other non-string values call str first.

So if you want your CSV to have the string null instead of '' then you have to modify the values before they reach the CSV writer. Perhaps:

results = [
['null' if val is None else val for val in row] for row in results
]

answered Nov 23, 2020 at 16:52

James Pringle

1,0796 silver badges15 bronze badges

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

1 Comment

Georgi Koemdzhiev Over a year ago

Thank you for your suggestion, James. That resulted in adding 'null' values into the generated CSV file as expected. I had to modify my csv_to_sql python script a bit to accommodate for that change and now it generates sql statements with NULL values properly :)

Collectives™ on Stack Overflow

Export MS SQL table with `null` values to CSV

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related