Error uploading .xls or .xlsx files to datastore

Question

In CKAN 2.4.1, install datapusher and .csv file uploading to datastore is correct, but when test .xls or .xlsx files upload to datastore fail when datapusher search the file fields headers, in datapusher.error.log:

Fetching from: http://10.115.100.69:5000/dataset/b0c86ea3-d764-493b-b0e8-d4bb0c287474/resource/287e7041-2616-4223-a94b-ed23f2937f2b/download/testxls.xls
[Tue Nov 17 18:38:38 2015] [error] Deleting "ead7be15-511f-4884-9f40-e12086d331b1" from datastore.
[Tue Nov 17 18:38:38 2015] [error] Determined headers and types: []
[Tue Nov 17 18:38:38 2015] [error] Successfully pushed 0 entries to "ead7be15-511f-4884-9f40-e12086d331b1".

In configuration file: ckan.datapusher.formats = csv xls xlsx tsv application/csv application/vnd.ms-excel application/vnd.openxmlformats-officedocument.spr ckan.datapusher.url = http://0.0.0.0:8800/

[error] is just what apache puts for all logs, so it's not really an error. So it's telling you it didn't find any headers in the XLS. So maybe your XLS is not a nice table with the headers in the first row. — D Read
Performing a set of simple tests: xls without format, with format, freezing header, saving csv in xls format, etc., all tests failed, but in the last test datapusher inserted the content of the file in the datastore. Reviewing differences in both files I saw that the problem was in the default new document option in M. Excel 2010, because ME2010 adds 3 tabs when creating new documents, and deleting the tabs without content solved the problem. Thanks. — jroig
Good spot. I've added an issue for DataPusher with a suggested improvement: github.com/ckan/datapusher/issues/90 Anyone can contribute this improvement, hint hint... — D Read

Aaron M Aaron M · Accepted Answer · 2015-11-18T20:56:41

Note, CKAN will push (only) the last worksheet in an excel file with multiple worksheets. When we have multiple worksheets we create a "Contents" sheet as our last and have a table with heading "Contents" and then some descriptive details e.g. below. We then upload each worksheet as a csv or tsv so the data on each worksheet gets pushed to the datastore as well.

Contents
Title
Simple description of the data
Comprises this contents sheet, plus 3 additional worksheets:
- Sheet1: Blah blah
- Metadata: Overall description of the dataset and high level information (Author, contact, keywords etc)
- ColMetadata: Descriptions/definition of the fields (columns) on each worksheet

Error uploading .xls or .xlsx files to datastore

1 Answers