ODO command in python and how to upload missing files from CSV as NULL with simple command

Question

i try to load data from a csv file into a mysql table using odo in python.

the csv file contains blank cells. The odo command files when it encounters blank cells.

how can I use the odo command to load the data and insert a null value by default for missing data.

I'm trying to import a simple CSV file that I downloaded from Quandl into a MySQL table with the odo python package

t = odo(csvpathName)

The rsow look like this in the CSV. The second line has a value missing.

A   7/25/2016   46.49   46.52   45.92   46.14   1719772 0   1   46.49   46.52   45.92   46.14   1719772
B   7/25/2016   46.49   46.52   45.92           1719772 0   1   46.49   46.52   45.92   46.14   1719772

The MySQL table is defined as follows:

Ticker varchar(255) NOT NULL,
Date varchar(255) NOT NULL,
Open numeric(15,2) NULL,
High numeric(15,2) NULL,
Low numeric(15,2) NULL,
Close numeric(15,2) NULL,
Volume bigint NULL,
ExDividend numeric(15,2), 
SplitRatio int NULL,
OpenAdj numeric(15,2) NULL,
HighAdj numeric(15,2) NULL,
LowAdj numeric(15,2) NULL,
CloseAdj numeric(15,2) NULL,
VolumeAdj bigint NULL,
PRIMARY KEY(Ticker,Date)

It throws an exception 1366 with the following info:

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1366, "Incorrect decimal value: '' for column 'High' at row 185") [SQL: 'LOAD DATA INFILE %(path)s\n INTO TABLE QUANDL_DATA_WIKI\n CHARACTER SET %(encoding)s\n FIELDS\n TERMINATED BY %(delimiter)s\n ENCLOSED BY %(quotechar)s\n ESCAPED BY %(escapechar)s\n LINES TERMINATED BY %(lineterminator)s\n IGNORE %(skiprows)s LINES\n '] [parameters: {'quotechar': '"', 'encoding': 'utf8', 'path': 'C:\ProgramData\MySQL\MySQL Server 5.6\Uploads\WIKI_20160725.partial.csv', 'lineterminator': '\n', 'escapechar': '\', 'skiprows': 0, 'delimiter': ','}]

Does anyone know how to configure ODO so I can upload missing values as NULL values with the simple command?

Answer 1

If I make all fields varchar(255) then it reads missing fiels as ''. sqlalchemy cannot force a '' from the csv file into another datatype.

Best is to use varchar to purely reads the csv file and then afterwards convert it to the proper formats