Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

edited March 23 in General Discussion

Hi,
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

  • moshegmosheg Administrator
    Answer ✓

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

Answers

  • 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 ..

  • edited March 23

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

  • moshegmosheg 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

  • 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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.