Error viewing sequential file.

bala_135 · Post by **bala_135** » Mon Dec 26, 2005 7:22 am

Hi,

I am having a problem viewing the data if I filter the record in the sequential file.

I have tried this.
Passed the UNIX command(grep -i "M" "D:\New_sources\TwoMillion") in the filter field of the sequential stage.

field de-limiter Delimiter is comma
final de-limiter -end
Style -Unix Style.

I am able to view the record without this filter option.

I have tried the same command for the same file in command prompt it's working fine.

Kindly let me know what can be done to solve this.

Thank you in Advance,
Bala

kumar_s · Post by **kumar_s** » Mon Dec 26, 2005 7:29 am

Hi,
May i know how you use unix command in windows environment or vice versa.

-Kumar

bala_135 · Post by **bala_135** » Mon Dec 26, 2005 7:35 am

Hi,

If MKS Toolkit is installed you can work with UNIX commands.I have not tried the other way.

Thank you,
Bala

kumar_s · Post by **kumar_s** » Mon Dec 26, 2005 7:38 am

Hi,
Is the issue with only viewing the data?
Is the output produced as required??

-Kumar

bala_135 · Post by **bala_135** » Mon Dec 26, 2005 7:44 am

Hi,

My requirement I want to filter the data from a text file(say having 2 million records) and I want to view the data.Before using the filter command it was working but when I use it's giving me a problem.Any more info.

--Bala

kcbland · Post by **kcbland** » Mon Dec 26, 2005 8:53 am

What's the "problem"? Since we don't have the error message we really can't tell you the issue.

Have you grep'd the file into another and tried viewing in the grep'd copy to verify it's not a metadata issue? Do you have embedded quotes that wrap <CR>/<LF> or delimiters that needs honoring, thus grep is actually corrupting the file?

ray.wurlod · Post by **ray.wurlod** » Mon Dec 26, 2005 2:44 pm

View Data is simply not available when a filter command is specified. You will need to verify the functionality of your filter command outside of DataStage, possibly by redirecting its output into a file as suggested by Ken.

bala_135 · Post by **bala_135** » Mon Dec 26, 2005 10:50 pm

Hi Ray/Kcbland,

Thank you very much.
I have used the following grep command
grep -i "M" TwoMillion.
This command works well when I use it in my command prompt.
I have tried piping the output to another file(grep -i "M" TwoMillion|cat>backup1) as suggested by you its working fine.

As I have mentioned earlier,I am able to view the data without the filter option but when I use a filter option data is not viewable.

Error messgae which I am getting when I am trying this command is.
##I TOSH 000002 10:06:29(001) <main_program> orchgeneral: loaded
##I TOSH 000002 10:06:29(002) <main_program> orchsort: loaded
##I TOSH 000002 10:06:29(003) <main_program> orchstats: loaded
##W TCOS 000049 10:06:29(004) <main_program> Parameter specified but not used in flow: Filename
##W TCOS 000049 10:06:29(005) <main_program> Parameter specified but not used in flow: DSProjectMapName
##I TFSC 000001 10:06:29(010) <main_program> APT configuration file: C:/Ascential/DataStage/Configurations/default.apt
##W TOIX 000000 10:06:31(000) <Sequential_File_0,0> Bad trailing quote character at field "first_name"; looking for '34' but found 'M', at offset: 17
##W TOIX 000154 10:06:31(001) <Sequential_File_0,0> Import warning at record 0:
##W TOIX 000018 10:06:31(002) <Sequential_File_0,0> Import unsuccessful at record 0:
##W TOIX 000000 10:06:31(003) <Sequential_File_0,0> Bad trailing quote character at field "last_name"; looking for '34' but found '9', at offset: 40
##W TOIX 000154 10:06:31(004) <Sequential_File_0,0> Import warning at record 1:
##W TOIX 000018 10:06:31(005) <Sequential_File_0,0> Import unsuccessful at record 1:
##W TOIX 000000 10:06:31(006) <Sequential_File_0,0> Bad trailing quote character at field "first_name"; looking for '34' but found 'M', at offset: 17
##W TOIX 000154 10:06:31(007) <Sequential_File_0,0> Import warning at record 2:
##W TOIX 000018 10:06:31(008) <Sequential_File_0,0> Import unsuccessful at record 2:
##W TOIX 000000 10:06:31(009) <Sequential_File_0,0> Bad trailing quote character at field "first_name"; looking for '34' but found ',', at offset: 17
##W TOIX 000154 10:06:31(010) <Sequential_File_0,0> Import warning at record 3:
##W TOIX 000018 10:06:31(011) <Sequential_File_0,0> Import unsuccessful at record 3:
##W TOIX 000000 10:06:31(012) <Sequential_File_0,0> Bad trailing quote character at field "first_name"; looking for '34' but found ',', at offset: 17
##W TOIX 000154 10:06:31(013) <Sequential_File_0,0> Import warning at record 4:
##W TOIX 000018 10:06:31(014) <Sequential_File_0,0> Import unsuccessful at record 4:
##I TOIX 000157 10:06:31(015) <Sequential_File_0,0> (no further reports will be generated from this partition until successful import)
##I TOIX 000163 10:06:38(000) <Sequential_File_0,0> Import complete. 0 records imported successfully, 250000 rejected.
**VIEWDATA ROW LIMITER HIT**

Thank you,
Bala

ArndW · Post by **ArndW** » Tue Dec 27, 2005 3:55 am

Bala,

your column "first_name" is misdefined. What is your defined quote character? What is your definition of the "first_name" column and what is the data (you just need to copy one line, since the error is occuring on each line)? The error message is telling you that you have a metadata mismatch but without additional information we can't tell you exactly wihch problem you have.

bala_135 · Post by **bala_135** » Tue Dec 27, 2005 6:33 am

Hello ArndW,

Thank you for the reply,I have pasted the sample data from the notepad as it is.

"John ","Parker ","M","1979-04-24"," 0087228.46","MA"
"Susan ","Calvin ","F","1967-12-24"," 0091312.42","IL"
"William ","Mandella ","M","1962-04-07"," 0040676.94","CA"
"Ann ","Claybourne ","F","1960-10-29"," 0061774.32","FL"
"Frank ","Chalmers ","M","1969-12-10"," 0004881.94","NY"
"Jane ","Studdock ","F","1962-02-24"," 0075990.80","TX"
"Seymour ","Glass ","M","1960-08-18"," 0051531.56","NJ"
"Laura ","Engels ","F","1981-12-07"," 0015280.31","KY"
"John ","Boone ","M","1964-04-16"," 0042729.03","CO"
"Susan ","Sarandon ","F","1966-06-08"," 0081319.09","ND"
"William ","Tell ","M","1974-07-13"," 0021008.45","SD"
"Ann ","Dillard ","F","1969-02-21"," 0004552.65","MI"

Metadata of the first_name is unicodeChar length(16).Defined quote character is double quotes.

Thank you,
Bala

kumar_s · Post by **kumar_s** » Tue Dec 27, 2005 7:06 am

Hi,
What is your delimeter.
Try changing char to varchar. Char field expects 16 digit to present in the file.
Also try to write to a file with out the filter. You may allowed to view the data with in the limitation of the given number of rows, but the issue may come beyond the range. Filter might have excluded those rows which are viewable.

-kumar

ArndW · Post by **ArndW** » Tue Dec 27, 2005 9:29 am

Bala,

you've identified your problem. You've declared the first_name column to be of fixed length of 16, yet your data has this column as a varchar column. You can get away with this in DataStage Server, but PX is very picky about using correct datatypes. You will also need to change the last name to varchar.

bala_135 · Post by **bala_135** » Tue Dec 27, 2005 11:53 pm

Hello All,

Thank you very much.I did the following.

Changed the datatype to varchar and also the final delimter to none.I able to view the data with filter condtion.

Regards,
Bala

rwierdsm · Post by **rwierdsm** » Fri Oct 06, 2006 1:15 pm

Wanted to add to this thread since I was just wrestling with a similar error and wanted to make it perfectly obvious to future seekers that sequential files in EE DO NOT LIKE FIELDS DEFINED AS CHAR!!!

I received the following error:

Code: Select all

Bad trailing quote character at field "COLUMN_ONE"; looking for '34' but found '3', at offset: 103

The data was not the full length of the CHAR(20) definition. Changing the data type from CHAR to VARCHAR fixed this up.

I usually copy my meta data backwards through the data stream, in this case from the target table to a loadset persistent dataset. I needed to create some test data, so I created a second job to populate the persistent dataset from a sequential file, again, copying the metadata backwards through the data stream. Banged my head against the error above. Finally did a search and determined that I needed to change the field definition from CHAR to VARCHAR.

End of Story

Rob W

ray.wurlod · Post by **ray.wurlod** » Fri Oct 06, 2006 2:44 pm

DataStage parallel jobs are perfectly happy with CHAR as a data type.

Unlike server jobs, however, parallel jobs enforce the rules: if it's CHAR(n) then it MUST be n characters long, including the null field value.

End of story.