How to handle missing values in a CSV file for a DECIMAL column

Question

I am reading data to database using pyodbc from .csv file.

One column is defined as decimal(18,4) in SQL Server, but there is missing value in this column. So when I try to insert it, it throws an error saying string type cannot transfer to numeric type.

The data looks like

[A, B, C, , 10, 10.0, D, 10.00]

as you see at position 4, there is a missing value '' which should be a float number like 4.3526

I want to read this row to database where the 4th column is defined as decimal(18,4) and it should looks like

A B C NULL 10 10.0 D 10.00

in database.

EDIT:

Here is my code

def load_data(c, infile, num_rows = None, db_schema = 'dbo',table_name = 'new_table'):

try:
    if num_rows:
        dat = pd.read_csv(infile, nrows = num_rows)
    else:
        dat = pd.read_csv(infile)

    l = dat.shape[1]
    c.executemany('INSERT INTO {}.{} VALUES {}'.format(db_schema,table_name,'(' + ', '.join(['?']*l) + ')'), dat.values.tolist())

except :
    with open(infile) as f:
        dat = csv.reader(f)
        i = 0
        for row in dat:
            if i == 0:
                l = len(row)
            else:
                c.execute('INSERT INTO {}.{} VALUES {}'.format(db_schema,table_name,'(' + ', '.join(['?']*l) + ')'), *row)

            if num_rows:
                if i == num_rows:
                    break
            i += 1

print(db_schema + '.' + table_name+' inserted successfully!')

Please ignore the indent error.

Thank you.

You want share your python code? I think you'd have to evaluate for blank and set equal to None. Maybe this would help https://stackoverflow.com/questions/41473612/convert-csv-blank-cell-to-sql-null-in-python — Tim Mylott, Oct 17 '18 at 20:26
@Eleanor - Does your CSV file really have a space after each comma (field separator)? If so, then that will affect how the default behavior of `read_csv` interprets missing values. — Gord Thompson, Oct 18 '18 at 11:42
@GordThompson I am not sure if there is a space. But the file can be only read when encoding = 'ISO-8859-1', if I just use `pd.read_csv('asdfad.csv')` it will returns an error. — Eleanor, Oct 18 '18 at 17:41

score 1 · Answer 1 · answered Oct 18 '18 at 18:30

If pandas' read_csv method is returning an empty string for the missing value then chances are good that your CSV file uses "punctuation style" comma separators (with a space after the comma) instead of "strict" comma separators (with no extra spaces).

Consider the "strict" CSV file

1,,price unknown
2,29.95,standard price

The pandas code

df = pd.read_csv(r"C:\Users\Gord\Desktop\no_spaces.csv", header=None, prefix='column')
print(df)

produces

   column0  column1         column2
0        1      NaN   price unknown
1        2    29.95  standard price

The missing value is interpreted as NaN (Not a Number).

However, if the CSV file contains

1, , price unknown
2, 29.95, standard price

then the same code produces

   column0 column1          column2
0        1            price unknown
1        2   29.95   standard price

Note that the missing value is actually a string containing a single blank (' '). You can verify that by using print(df.to_dict()).

If you want read_csv to parse that CSV file correctly you need to use sep=', ' so the field separator includes the space

df = pd.read_csv(r"C:\Users\Gord\Desktop\with_spaces.csv", header=None, prefix='column', sep=', ', engine='python')
print(df)

which again gives us

   column0  column1         column2
0        1      NaN   price unknown
1        2    29.95  standard price

score 0 · Answer 2 · answered Oct 17 '18 at 19:50

You could handle this with a case statement to make blank values NULL. Something like:

declare @table table (c decimal(18,4))

declare @insert varchar(16) = ''

--insert into @table
--select @insert
--this would cause an error

insert into @table
select case when @insert = '' then null else @insert end 
--here we use a case to handle blanks

select * from @table

score 0 · Answer 3 · answered Oct 17 '18 at 20:02

0

I would use NULLIF to insert null where the value = ''

declare @table table (c decimal(18,4))

declare @insert varchar(16) = ''

insert into @table
select NULLIF(@insert,'')

answered Oct 17 '18 at 20:02

Shredder

1
3

How to handle missing values in a CSV file for a DECIMAL column

3 Answers3