Why does COPY FROM LOCAL skip last file when using wildcards?
joergschaber
Vertica Customer ✭
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
0
Best Answer
-
mosheg
Vertica Employee Administrator
Can you load that skipped file only, specifying its full name?
0
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 ..
It turns out that there are some character like ':' and ',' in a field that seem to make problems, can that be?
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!