This discussion is archived
2 Replies Latest reply: Jul 29, 2012 11:03 PM by Johnreardon RSS

help needed in cleaning raw data with odd delimiters in it

608249 Newbie
Currently Being Moderated
I am trying to write a sp, which adjust the number of columns based on the IN Parameter for the raw file.

Raw (Id Int,RawData varchar2(5000))

RawData has actual data with delimiter, this column has all the raw file imported as is.

e.g. data in RawData Column

A|B|C|D|E|F
X|B|C|D|E
Z
P|ABC|ZPD|BITX|E|ETC


Here above you can see the max number of delimiter are 5 for a 6 column table, however for some records there are less delimiter, here comes the problem

For such records I want to add the extra delimter and take values for missing column as NULL or best would be''


plz help me in writing Generic SP to clean this.

We can pass the source & target table name and column names as a parameter, and create a clean table dynamically by name CleanedRaw etc.

here the file is | delimited, actually It can have any single char delimiter or space or tab also. I am ok if we need to pass the delimiter also as a parameter to this sp

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points