How to fix pandas to_sql append not appending one item out of 2000?

I have a CSV file with 2000 rows and 3 columns of type Int, String, and String respectively. I’m trying to write a program that creates a table and appends my CSV file rowsxcolumns into the table. It all seems to work, except for a single item. The item’s first and third column are appended, but the second one is null.

No errors are shown on the console, and I have tried printing the data to the console before calling the to_sql function and it shows the one missing item just fine. My CSV file also uses a delimiter. That item was at first not correctly formatted and so after delimiting all of my rows, I had to specifically delimit that single row by itself – I’m thinking that this could have caused the problem? All of the other rows and columns are perfectly fine.

Here’s my code:

import sqlite3
import pandas as pd
from pandas import DataFrame

connection = sqlite3.connect('data.sqlite')
c = connection.cursor()

c.execute('''CREATE TABLE test(val1 int, val2 varchar(255), val3 varchar(255))''')

connection.commit()

col_names = ["val1", "val2", "val3"]
read_clients = pd.read_csv(r'thefile.csv', encoding='utf-16', names=col_names, sep='t')

read_clients.to_sql('test', connection, if_exists='append', index = false, schema='test')

connection.commit()

No messages are printed to the console.

Answer

So the value of the string that was missing was “NA”. Python apparently translates this to a null value! Here’s how I fixed it:

read_clients = pd.read_csv(r'thefile.csv', encoding='utf-16', names=col_names, sep='t', na_filter=False)