Search code examples
oracle-databasesql-loaderinformatica-powercenterinformaticautl-file

Delimeter files issues

I do have a flat file with not a fixed structure like

 name,phone_num,Address
 bob,8888,2nd main,5th floor,avenue road

Here the last column Address has the value 2nd main,5th floor,avenue road but since the same delimeter , is used for seperating columns also i am not getting any clue how to handle the same.

the structure of flat file may change from file to file. How to handle such kind of flat files while importing using Informatica or SQL * Loader or UTL Files

I will not have any access to flat file just i should read the data from it but i can't edit the data in flat file.

Solution

  • Using SQLLoader

    load data
append
into table schema.table
fields terminated by '~'
trailing nullcols
(
   line       BOUNDFILLER,
   name       "regexp_substr(:line, '^[^,]+')",
   phone_num  "regexp_substr(:line, '[^,]+', 1, 2)",
   Address    "regexp_replace(:line, '^.*?,.*?,')"
)