I am working on a CSV collector, the business people are sending file with delimiter as | pipe, as some the data values have comma in them already.
I tried with _CSV_Separator=| but not getting successful test.
What else I am missing or it won't work ?
Temporarily I am using notepad++ to search and replace with regular expressions.
Complete string
jdbc:csv:////home/oracle/database/SampleData/Sears/?_CSV_Header=true;maxScanRows=20000;charSet=ISO8859_1;tmpdir=/home/oracle;_CSV_Separator=|
Sample data
Location|Unit_Number|Role|Username
Miami, FL|0009432|Administrator|AFARIA0
SITEL Bogota|0007931|Administrator|APRAKAS
West Hills, CA Cellphone|0008388|Administrator|CKOKINE
Bridgeview,IL|0009420|Administrator|DAGUIR3
To describe the CSV_Separator we use the unicode number instead of the actual symbol to ensure it interpretes it as intended and not causing any manipulations.
The unicode number for | is U+007C (https://en.wikipedia.org/wiki/List_of_Unicode_characters) you could try _CSV_Separator=\u007C and see if that works.
For your string:
jdbc:csv:////home/oracle/database/SampleData/Sears/?_CSV_Header=true;maxScanRows=20000;charSet=ISO8859_1;tmpdir=/home/oracle;_CSV_Separator=\u007C