Can we load Excel (.xlsx or .xls) file directly to Vertica Table?

haroon

Currently to load Excel (.xlsx or .xls) file to my Vertica Table I have to convert Excel file to CSV and then load it to Vertica Table using Copy query.

Is there any way that i can be able to load Excel (.xlsx or .xls) directly to Vertica without converting it to CSV?

Any kind of help will be appreciated.

Jim_Knicely

Yes, you have to convert them.

I use xlsx2csv to convert Excel files to CSV in Linux.

See:
https://pypi.org/project/xlsx2csv/

Example:

[root@s18384357 dbadmin]# easy_install xlsx2csv
Searching for xlsx2csv
Reading https://pypi.python.org/simple/xlsx2csv/
Downloading https://files.pythonhosted.org/packages/fa/25/a10bf9f58e0d8c906342093ba730d26755f19f4a0b8a1d4f64e193f3ca43/xlsx2csv-0.7.4.tar.gz#sha256=6ce45a87b61af6d3c24fed4221642de9115dc9cb9ea65887b0926fd0fab0a597
Best match: xlsx2csv 0.7.4
Processing xlsx2csv-0.7.4.tar.gz
Writing /tmp/easy_install-CnIxQW/xlsx2csv-0.7.4/setup.cfg
Running xlsx2csv-0.7.4/setup.py -q bdist_egg --dist-dir /tmp/easy_install-CnIxQW/xlsx2csv-0.7.4/egg-dist-tmp-pbf5cn
zip_safe flag not set; analyzing archive contents...
Moving xlsx2csv-0.7.4-py2.7.egg to /usr/local/lib/python2.7/site-packages
Adding xlsx2csv 0.7.4 to easy-install.pth file
Installing xlsx2csv script to /usr/local/bin

Installed /usr/local/lib/python2.7/site-packages/xlsx2csv-0.7.4-py2.7.egg
Processing dependencies for xlsx2csv
Finished processing dependencies for xlsx2csv

[root@s18384357 dbadmin]# exit
exit

[dbadmin@s18384357 ~]$ file ExcelFile.xlsx
ExcelFile.xlsx: Microsoft Excel 2007+

[dbadmin@s18384357 ~]$ xlsx2csv ExcelFile.xlsx ExcelFile.csv

[dbadmin@s18384357 ~]$ cat ExcelFile.csv
COL1,COL2,COL3
1,ABC,09-19-12
2,XYZ,05-04-12

[dbadmin@s18384357 ~]$ xlsx2csv ExcelFile.xlsx ExcelFile.csv; cat ExcelFile.csv | vsql -c "COPY test FROM stdin SKIP 1 DELIMITER ',';"

[dbadmin@s18384357 ~]$ vsql -c "SELECT * FROM test;"
col1 | col2 |        col3
------+------+---------------------
    1 | ABC  | 2012-09-19 00:00:00
    2 | XYZ  | 2012-05-04 00:00:00
(2 rows)

haroon

I am using openpyxl to convert .xlsx to csv and pyexcel to convert .xls to pyexcel

What are your reviews about both these packages?