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

haroonharoon Registered User

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.

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    Yes, you have to convert them.

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

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

    Example:

    [[email protected] 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
    
    [[email protected] dbadmin]# exit
    exit
    
    [[email protected] ~]$ file ExcelFile.xlsx
    ExcelFile.xlsx: Microsoft Excel 2007+
    
    [[email protected] ~]$ xlsx2csv ExcelFile.xlsx ExcelFile.csv
    
    [[email protected] ~]$ cat ExcelFile.csv
    COL1,COL2,COL3
    1,ABC,09-19-12
    2,XYZ,05-04-12
    
    [[email protected] ~]$ xlsx2csv ExcelFile.xlsx ExcelFile.csv; cat ExcelFile.csv | vsql -c "COPY test FROM stdin SKIP 1 DELIMITER ',';"
    
    [[email protected] ~]$ 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)
    
  • haroonharoon Registered User
    edited March 6

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

    What are your reviews about both these packages?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file