I have three kinds of data, that is comma or tab or pipe separated data, trying to ingest file with comma or tab or pipe separated data with single control file?

Is it possible to load different kind of delimited data using single control file?

Egg:

Test1.csv

content:

firstname,lastname
rachel,green
chandler,bing

Test2.tsv

content:

firstname   lastname
rachel  green
chandler    bing

Test3.psv

content:

firstname|lastname
rachel|green
chandler|bing

My current control file:

test.ctl

load data into table USERNAMES APPEND fields terminated by '\t' (firstname,lastname)

Expecting something like:

load data into table USERNAMES APPEND fields optionally terminated by '\t' or "," or "|" (firstname,lastname)

CodePudding user response：

Nope, unfortunately you can't make it using same control file

docs

CodePudding user response：

I believe sqlldr only allows for one delimiter, so you could pre-reprocess the file with a script to make all delimiters the same first (you'll need to know if those characters could be in the data though-could get ugly) and technically this changes the incoming data.
Alternatively you could load the file after pre-processing as above into a staging table where some sanity checks could be performed, then load into the main data table if sanity checks and validation passes.