read_delimited()

vardhan354 · Post by **vardhan354** » Mon Jun 26, 2006 10:30 am

Hi Fellas,

I'm loading data from a oracle table to a sequential file(Job1).The job is running fine with no indication of errors or warnings in the log.I use this sequential file to load to another table(Job2).
Now the scenario is like this.
When I tried to view the data from the job 1, I get the following messages.

1. read_delimited() - invalid quotes
2. read_delimited() -required column missing

I'm not able to get rid of this.

When I run the Job 2, Sequential file (from Job1) into a table,the job is getting aborted with the following message.

read_delimited() - invalid quotes, row 204

So, Unless I fix the issue in job1,my job 2 cannot be go for a successful run.Please throw your opnions on this issue.

Regards,

VG.

DSguru2B · Post by **DSguru2B** » Mon Jun 26, 2006 10:33 am

In the output of the sequential file stage, specify the quote characters as 000. Also while building the file. Specify the quote character as 000.

kcbland · Post by **kcbland** » Mon Jun 26, 2006 10:36 am

Make sure your Sequential stage in the OCI-->SEQ job is setup to honor quotes if your delimiter is in the data. If not, don't use quotes in the setup. If so, you then need to make sure your data does contain quotes that will foul up the detection of column separation. In addition, data containing low/hi ASCII values and embedded <CR>/<LF> values will also need to be honored in the configuration of the SEQ stage.

kris007 · Post by **kris007** » Mon Jun 26, 2006 10:49 am

Looks like some records contains terminators(as Ken mentioned ASCII characters) to me. Identify which column's data contains these termintators and then, in the Sequential file stage, go to the Columns tab and set the attribute " Contains Terminators" to Yes for that column. Hopefully, that should take care of your error.

The columns which might contain the terminators in most cases would be of datatype Varchar(in my experience). I hope that gives a good start.

vardhan354 · Post by **vardhan354** » Mon Jun 26, 2006 10:55 am

I just happened to see one of the discussions posted on this issue.It was suggested,"The best way to handle it , is to go to the column tab in the sequential file stage, and set the 'Contains Terminators' of the last row to Yes. "

Now I just ran the job with the said modifications and is running.This job is multiple instance job as the source has 0.45 Billion records, So I decided to chunk the data.

Thanks a lot for your opinions.Your thoughts valued a lot to me.......

Rgds,
VG.

kris007 · Post by **kris007** » Mon Jun 26, 2006 10:59 am

You cannot or not supposed to set the 'Contains Termintors' attribute to Yes for the last row(column).

Krazykoolrohit · Post by **Krazykoolrohit** » Tue Jun 27, 2006 9:21 am

Instead try this for the last row:

Convert(Char(10):Char(13),"",Field)