How to get out the Null row in a file

[shrek7577]

Hi,

I've a problem. Our client has sometimes somes lines with nothing a in his file. For example :

Field1 ; Field2 ; Field3
Field1 ; Field2 ; Field3
(nothing)
Field1 ; Field2 ; Field3

And the output must be :
Field1 ; Field2 ; Field3
Field1 ; Field2 ; Field3
Field1 ; Field2 ; Field3

I dont't know if the following solution is the simplest solution :
1. In the columns description of the file, on Incompete columns, click on 'REPLACE' for all the fields
2. In a transformer, filter all row with Null key field

Thanks for all your ideas !

[ray.wurlod]

Easier to process with a filter command in the Sequential File stage that reads the file. Use sed or awk or some other command that allows you to remove empty lines from a file.

[kumar_s]

You can optionally read all the fields in a single field using Varchar datatype and eliminate the empty lines using transformer.
Optionally you can use FIND witn /V option to eliminate the empty lines. Iam just looking for the command to find the newline. If I get it, I ll update you.

[shrek7577]

Thanks kumar_s and ray.wurlod but the DS Server is on Windows Server. So, no sed nor awk...

[ArndW]

Shrek - you can get free UNIX utilities on Windows that include such tools as sed and awk; the most common one I know of is MKS toolkit, but MS also offers a package that will do this.

[shrek7577]

OK, thanks for this precision.
I didn't know

[DSguru2B]

When you say its nothing, you mean not even a delimiter? If thats the case then you can read the entire line as one record, do an INDEX() on delimiter and if its equal to 0 then reject it. You cannot exactly check for NULLS in a flat file.

[shrek7577]

Hi DSGuru2B,

Yes nothing, just the system character next line.

Often, this file CSV is generated from .XLS in which there is some empty rows. Sometimes times, there's only separators :
Field1 ; Field2 ; Field3
;;;;
;;
Field1 ; Field2 ; Field3

But, I can't ask the person generating this file to correct his CSV file (He doesn't know how to do...). And there'is some bug in generating CSV from XLS with empty last columns...

[DSguru2B]

How about you save the xls sheet as tab delimited file. See if that works.

[shrek7577]

DSguru2B,
nothing to do in the CSV generating ... Only in Datastage.

[chulett]

But, I can't ask the person generating this file to correct his CSV file (He doesn't know how to do...).

Of course you can. Explain how. Anyone responsible for generating a spreadsheet as input to an automated process needs to be disciplined and not send you crap. They can delete the empty rows and/or columns before converting to csv format.

There's no 'bug', it's all about the people working the spreadsheet.

[DSguru2B]

Open your file as a text document. Copy and paste a few records here enclosed in "code" tags. Lets see how we can tackle this using just DataStage. Also , how many columns are supposed to be in your file?

[DSguru2B]

I failed to see Craig's reply as we both posted at the same time. He is so right. All you need to do is go talk to that person.

[shrek7577]

I understand your reaction and reply.
When I wrote "I can't ask", I meaned I don't have permission to explain how to do ?..


I don't understand that too !..


Let's return to the technical problem.
No manual intervention allowed, only automatical job in Datastage to correct to file.

[DSguru2B]

Ok, then post the sample contents here and also tell me the number of columns present. Make sure you copy paste the contents here.