Command line script to bulk load .xlsx data into ES

stratawing · February 13, 2012, 9:43pm

I have a use case where users are permitted to upload xlsx files with
up to 10,000 records per xlsx file. The xlsx file has 9 fields, 4 of
which are numbers (double), and 5 of which are variable length
strings.

Accordingly, I was messing around with importing .xlsx files into ES
using the bulk operation and, after tossing out various xml parsers
due to speed issues, came up with the following:

gist.github.com

https://gist.github.com/stratawing/1820634

XLSX to ES


unzip -Uo 10k_records.xlsx xl/worksheets/sheet1.xml xl/sharedStrings.xml -d proc
	cat proc/xl/sharedStrings.xml \
	| sed -e 's/[^[:print:][:blank:][:space:]]/[?]/g' -e 's/|/-delim-/g' \
	| mawk 'BEGIN {RS = "</t></si><si><t>|<si><t>|</t></si></sst>|</t></si><si><t xml:space=\"preserve\">"
			FS = "999ABCDE"
                        OFS = "|"}
                        { print NR - 2, $1 }' \
	| LANG=C sort -f -t "|" -k1 \
	| grep -v "<sst xmlns=\|<?xml version" \

This file has been truncated. show original

Using this relatively simple command line script, I was able to parse
and bulk load 10,000 records into ES in under 2 seconds -- with about
1 second of the total time coming from parsing the xlsx file.

Caution: This isn't really general purpose (i.e. - no excel date
conversion, only text and numbers, depends on (m)awk), but hopefully
it can help someone along the way.

Note that there are a bunch of xlsx parsers out there that are
probably much "safer" (see, e.g. Apache POI) but I had some spare time
to kill today.....

Cheers!

kimchy · February 14, 2012, 2:33pm

Thanks for sharing!

On Monday, February 13, 2012 at 11:43 PM, stratawing wrote:

I have a use case where users are permitted to upload xlsx files with
up to 10,000 records per xlsx file. The xlsx file has 9 fields, 4 of
which are numbers (double), and 5 of which are variable length
strings.

Accordingly, I was messing around with importing .xlsx files into ES
using the bulk operation and, after tossing out various xml parsers
due to speed issues, came up with the following:

Command Line Script to bulk load XLSX into ES · GitHub

Using this relatively simple command line script, I was able to parse
and bulk load 10,000 records into ES in under 2 seconds -- with about
1 second of the total time coming from parsing the xlsx file.

Caution: This isn't really general purpose (i.e. - no excel date
conversion, only text and numbers, depends on (m)awk), but hopefully
it can help someone along the way.

Note that there are a bunch of xlsx parsers out there that are
probably much "safer" (see, e.g. Apache POI) but I had some spare time
to kill today.....

Cheers!