Why does COPY FROM LOCAL skip last file when using wildcards?

joergschaberjoergschaber Vertica Customer
edited March 2022 in General Discussion

I am trying to bulk load a bunch of flies into a table using wildcards and the ADONET driver version 10:

COPY <table> from LOCAL '<path>\\SampleData*'

There are 10 files, named SampleData0.txt, SampleData1.txt, ..., SampleData9.txt, but only 9 are loaded into the table, the last one, SampleData9.txt, is skipped.

Any idea? Thanks, Jörg

Best Answer

  • Options
    moshegmosheg Vertica Employee Administrator
    Answer ✓

    Can you load that skipped file only, specifying its full name?


  • Options
    joergschaberjoergschaber Vertica Customer

    No, indeed, I cannot. Thanks for the hint. It didn't occur to me that there could be a problem with the file, because all files have the same structure. I will check ..

  • Options
    joergschaberjoergschaber Vertica Customer
    edited March 2022

    It turns out that there are some character like ':' and ',' in a field that seem to make problems, can that be?

  • Options
    moshegmosheg Vertica Employee Administrator

    Use the following to handle special characters in your input data:
    1. Add “NO ESCAPE” to the COPY command which eliminates escape character handling.
    Use this option if you do not need any escape character and you want to prevent characters in your data from being interpreted as
    escape sequences. For example: copy table_name from '/your_path/file_name.csv' no escape;
    See: https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/COPY/Parameters.htm
    2. Use ENCLOSED BY and set a quote character within which to enclose data, ENCLOSED BY parameter lets you set an ASCII character
    to delimit characters to embed in input string values. The enclosing character is not considered to be part of the data if and only if it
    is the first and last character of the input. For example: copy table_name from '/your_path/file_name.csv' enclosed by '"' delimiter '|';
    See: https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/DataLoad/LoadingUTF-8FormatData.htm

  • Options
    joergschaberjoergschaber Vertica Customer

    When I convert the data to json and use the respective parser, have no problems. All files are read. So I just go with that solution I suppose. Thanks for your responses!

Leave a Comment

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