0

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.

Aspirant
  • 2,238
  • 9
  • 31
  • 43
  • 1
    Use an escape character, e.g., `\`. Configure your loader for using this escape character. – Rachcha Mar 25 '13 at 10:27
  • @Rachcha U mean specify the escape character in `control file` , i can do that if i know the position of the column which is getting data in such format !! but the columns position are dynamic – Aspirant Mar 25 '13 at 10:31

3 Answers3

5

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, '^.*?,.*?,')"
)
Egor Skriptunoff
  • 23,359
  • 2
  • 34
  • 64
  • 1
    And there I was, writing a long-winded explanation of how to do this using UTL_FILES and a pile of ugly, messy code when I saw this. Great answer - and it saved me finishing up my too-long-too-messy answer! – Bob Jarvis - Слава Україні Mar 25 '13 at 10:58
  • dont you mean "fields terminiated by ','", will it still work with regex then ?? – Raghav Mar 25 '13 at 12:51
  • @Raghav - `~` could be replaced with any character which is not encountered inside data file – Egor Skriptunoff Mar 25 '13 at 12:55
  • are nongreedy those "*?". Did not know they worked in informatica. – momobo Mar 25 '13 at 19:44
  • @EgorSkriptunoff - thats my exact point, we know that the field delimiter required is "," but that appears in the data itself... thats why I was wondering, the moment you say, fields delimited by "," the whole perspective on the regex changes.. right ?? – Raghav Mar 26 '13 at 07:08
  • @EgorSkriptunoff SQL * Loader solution is ok but i want to perform the same thing in Informatica !! Any idea regarding how to handle the same situation i.e table structure changing dynamically in flat files in `Informatica` – Aspirant Mar 27 '13 at 11:53
0

you need to change your source file to enclose the fields in an escape character eg:

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

then in sql-loader you'd put:

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '^'

just pick a delimiter that doesn't normally appear in your data.

DazzaL
  • 21,638
  • 3
  • 49
  • 57
  • if i do have access to the source flat file i can do as suggested by you !! but since i donnot have access to it is there any other way to handle the same. – Aspirant Mar 25 '13 at 10:36
0

If you could get the source data enclosed within double quotes ( or any quotes for that matter) you can make use of 'Optional Quotes' option in Informatica while reading from Flat file

Sadagopan
  • 141
  • 4