2

I'm using Exasol database and want to insert data from a .csv file into a database table.

Here is the script

ALTER SESSION SET NLS_DATE_FORMAT= 'DD-MM-YYYY';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MM-YYYY HH24:MI:SS.FF3';
ALTER SESSION SET NLS_DATE_LANGUAGE='ENG';
TRUNCATE TABLE &1..&2;
DEFINE fileDir = './Input/';
IMPORT INTO &1..&2 FROM LOCAL CSV FILE '&fileDir&3'
ENCODING = 'ISO-8859-15'
--ROW SEPARATOR = 'CRLF'
COLUMN SEPARATOR = '|'
--COLUMN DELIMITER = ''
SKIP = 0
REJECT LIMIT 0;
commit;

Here is the data

Product | Base product | code | Product Name
44139   | 4413         | 4413 | Mobile

Error

Error: [42636] ETL-3050: [Column=0 Row=0] [Transformation of value='??Product failed - invalid character value for cast; Value: '??] (Session: 1595916477624115890) while executing 'IMPORT INTO TEST.PRODUCTS FROM CSV AT 'http://19.0.0.11:00000' FILE '07hgbjhed-jhkn-4178-92f3-jhasdkgasdsgdh6556' ENCODING = 'ISO-8859-15' COLUMN SEPARATOR = '|' SKIP = 0 REJECT LIMIT 0;'

May I know please what's the issue ? How can I fix this ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Squeez
  • 343
  • 2
  • 3
  • 15
  • Does it break with just one row of data? Could you try wit the row separator and `SKIP == 1` to avoid the first line? If you are on linux use LF as row separator – mucio Mar 25 '18 at 13:56
  • @mucio , It’s just a sample data but actual data is having more than one record...! But it fails as I’ve mentioned....! Secondly am running on Windows...! – Squeez Mar 25 '18 at 13:59
  • does it fail also with the `skip = 1`? – mucio Mar 25 '18 at 14:07
  • @mucio Yes ....It does....! – Squeez Mar 25 '18 at 14:08
  • your final table contains only 4 columns? This `&1..&2` maybe should be `&1.&2`, could you try it? – mucio Mar 25 '18 at 14:10
  • @mucio, &1..&2 is correct..That am sure..! Yes my final table contains 4 columns only..! – Squeez Mar 25 '18 at 15:05
  • I'm not familiar with the `..` syntax in SQL, but at this point I would try to reduce the number of rows in the imported file to identify the rows causing the problem – mucio Mar 25 '18 at 17:17
  • 1
    @mucio ...thanks for your time ....! I got the fix....Issue was with file’s encoding....! – Squeez Mar 25 '18 at 17:22
  • Happy you solved it and thanks for letting me know – mucio Mar 25 '18 at 17:23

0 Answers0