2

Is there an option to change the default way the csv and xlrd packages handle empty cells? By default empty cells are assigned an empty string value = ''. This is problematic when one is working with databases because an empty string is not a None value, which many python packages that interface with databases (SQLAlchemy for example) can handle as a Null for database consumption.

For example if an empty cell occurred in a field that is suppose to be a decimal/integer/float/double then the database will throw up an exception because an insert of a string was made to a field of type decimal/integer/float/double.

I haven't found any examples or documentation that shows how I can do this. My current approach is to inspect the data and do the following:

if item[i] == '': 
   item[i] = None

The problem with this is that I don't own the data and have no control over its quality. I can imagine that this would be a common occurrence since a lot of apps are using files/data that are produced by sources other then them.

If there is a way to change the default treatment then that would be a sensible approach in my opinion.

codingknob
  • 11,108
  • 25
  • 89
  • 126

3 Answers3

1

I have the same setup as yourself (sqlalchemy for the ORM, and data that I have little control over, being fed through excel files). I found that I need to curate the data from the xlrd before dumping it in the database. I am not aware of any tweaks that you can apply on the xlrd module.

On a more general note: It is probably best to try and get as large a sample of example excel files as you can and see if your application can cope with it. I found that occasionally weird characters make it through the excel (people copy paste from different languages) which cause crushes further down. Also found that in some cases the file format was not UTF-8 but iso-8859 or something else. I ended up using iconv for converting the files.

you may also want to have a look at this stackoverflow article

Overall xlrd has worked for us, but I am less than impressed with the activity around the project. Seems like I am using a library that has little maintenance.

Community
  • 1
  • 1
Dimitris
  • 2,501
  • 3
  • 21
  • 28
  • You say "Also found that in some cases the file format was not UTF-8 ...". Text in valid Excel .XLS files is NEVER encoded in UTF-8.Text in valid .XLSX files is expected to be encoded in UTF-8 (the XML default). In any case, xlrd returns Unocode. You must be talking about csv files. – John Machin Mar 29 '13 at 11:13
0

You could use the following code to change the values of all empty cells in the sheet you are reading in to NULL (or None, or whatever you like) before you actually read in the data. It loops through all rows and columns and checks if the cell_type is EMPTY and then changes the value of the respective cell to 'NULL'.

import xlrd

book = xlrd.open_workbook("data.xlsx")
sheet_name = book.sheet_names()[0] #getting the sheetname of the  first sheet
sheet = book.sheet_by_name(sheet_name)

for r in range(0,sheet.nrows): #create a list with all row numbers that contain data and loop through it
    for s in range(0, sheet.ncols):       #create a list with all column numbers that contain data and loop through i
        if sheet.cell_type(r, c) == xlrd.XL_CELL_EMPTY:
            sheet._cell_values[r][c] = 'NULL'

Then you can read in the data (e.g. from the first column) and you will get NULL as a value if the cell was previously empty:

for r in range(0,sheet.nrows):
    data_column_1 = sheet.cell(r,0).value
rkiko
  • 56
  • 3
-1

xlrd will tell you what type of cell you have (empty or blank, text, number, date, error).

This is covered in the xlrd documentation. Look at the Cell class, and these methods of the Sheet class: cell_type, col_types, and row_types.

The csv format has no way of expressing the difference between "no data at all" and "the value is a zero-length string". You will still need to check for '' and act accordingly.

John Machin
  • 81,303
  • 11
  • 141
  • 189