SQLcl Oracle to export data to CSV without blank first line and without carriage return character at the end
0
votes
1
answer
827
views
For reasons, I am using the following here-doc approach to export data to CSV.
$ /oracle/sqlcl/bin/sql -s username/password@//host.mybiz.com.my:1521/dwh > arca.csv <psql tool when I want to load to Postgres
Below error happens because the CSV file has blank row as first line. The column names are read as data instead, causing type error.
$ psql -c "\COPY raw_data.arca FROM arca.csv CSV HEADER"
ERROR: invalid input syntax for type date: "FILE_DATE"
CONTEXT: COPY arca, line 2, column file_date: "FILE_DATE"
If I remove the blank row manually, the following error then occurs because of carriage return character at the end of data
$ psql -c "\COPY raw_data.arca FROM arca.csv CSV HEADER"
ERROR: unquoted carriage return found in data
HINT: Use quoted CSV field to represent carriage return.
CONTEXT: COPY arca, line 101
How do I correctly use sqlcl with here-doc approach to produce CSV file without first blank line, and carriage return ending?
Asked by idazuwaika
(121 rep)
Mar 27, 2021, 10:39 AM
Last activity: Apr 11, 2025, 12:03 PM
Last activity: Apr 11, 2025, 12:03 PM