sequential file stage - comma delimiter failed
Moderators: chulett, rschirm, roy
Is the input sequential file defined with format field defaults of double-quotes as the quote character?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 160
- Joined: Tue May 02, 2006 7:49 am
I cut-and-pasted your 3 lines to a text file and then defined a sequential file stage with the default Quote=double setting and was successfully able to view the data. I suspect that you have another issue - try doing what I did to see if you can successfully read a quoted string with embedded delimiter characters in a small test program.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 160
- Joined: Tue May 02, 2006 7:49 am
As per the suggestion, I have taken sample records and tested the job. Now the below records are populated successfully.
"122","Chaina Asia","1234"
"124"," london, united kingdom ","2222"
But I have identified one record. As this is the address column, I can see many charters in the column.
Second column having double quotes with in the value. That is the reason the job is failed.
Below highlighted is one column
"125","WALT STEET "FIRST", LONDON UNITED KINGDOM ","3333"
"122","Chaina Asia","1234"
"124"," london, united kingdom ","2222"
But I have identified one record. As this is the address column, I can see many charters in the column.
Second column having double quotes with in the value. That is the reason the job is failed.
Below highlighted is one column
"125","WALT STEET "FIRST", LONDON UNITED KINGDOM ","3333"
is, unfortunately, an invalid format which DataStage cannot read. It should read:"125"," WALT STEET "FIRST", LONDON UNITED KINGDOM ","3333"
Code: Select all
"125"," WALT STEET ""FIRST"", LONDON UNITED KINGDOM ","3333"
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Basically you will need to have the source create it using different rules, either
1) Select a quote character that isn't contained in the data
2) Double the quotes (Arnd's example) for interior quotes
3) Strip out interior quotes prior to writing the file
This isn't a DataStage problem - any product would have problems reading it since the quote characters are contained in the data.
1) Select a quote character that isn't contained in the data
2) Double the quotes (Arnd's example) for interior quotes
3) Strip out interior quotes prior to writing the file
This isn't a DataStage problem - any product would have problems reading it since the quote characters are contained in the data.
-
- Participant
- Posts: 46
- Joined: Tue Jul 20, 2010 1:26 pm
- Location: USA
- Contact:
Re: sequential file stage - comma delimiter failed
I would recoment you to convert the delimiter in the source file "," (comma) into another delimiter "|" before processing it into sequential stage. If the character "|" is also used in the source file you may rather use some other characters such as ~(tilde) or $(dollar) which are generally not used in files.
To accomplish this in the shell script you should replace the character "," with "|" also includes the double quotes " as they would help you replace only the delimiters and not any other , (comma) used in any fields.
You can write the following shell script and use it as before job subroutine:
#!/bin/sh
tr '","' '"|"' <input_file_name>
#In the input file name you can you can parameterize it if required else #put the file name along with the path where it exists
exit 0
This script changes the delimiter from comma to pipe and thus you can run yours datastage job using delimiter as pipe "|" and probably this would reesolve yours issue.
Thanks
Abhijit.
To accomplish this in the shell script you should replace the character "," with "|" also includes the double quotes " as they would help you replace only the delimiters and not any other , (comma) used in any fields.
You can write the following shell script and use it as before job subroutine:
#!/bin/sh
tr '","' '"|"' <input_file_name>
#In the input file name you can you can parameterize it if required else #put the file name along with the path where it exists
exit 0
This script changes the delimiter from comma to pipe and thus you can run yours datastage job using delimiter as pipe "|" and probably this would reesolve yours issue.
Thanks
Abhijit.
Abhijit
IBM Certified Solution Developer Infosphere DataStage
IBM Certified Solution Developer Infosphere DataStage
-
- Participant
- Posts: 80
- Joined: Fri Aug 21, 2009 7:49 am
- Location: India
sequential file stage - comma delimiter failed
try this function TRIM(input.col,""","B").