topic Re: DI Studio 3.4 CSV Source in SAS Programming

DI Studio 3.4 CSV Source

milts — Wed, 23 Sep 2009 05:44:11 GMT

Hi All,

I created a fixed-width source table from a csv file. I also imported the column definitions from a csv file.

Then I created a job that uses that source table to create an exact copy of it in a sas data set. But i get errors regarding on invalid data. How come do i get those errors when i didn't specify any constraints?

Also is there a maximum length for fields which come from csv files? I have a column character with length 80 but when I run the job complete value wasn't captured.

Hope you could help me out. Many thanks!
Milton

Re: DI Studio 3.4 CSV Source

milts — Wed, 23 Sep 2009 06:33:02 GMT

Correction

Its a TXT source not a CSV source sorry.

The column definitions is the CSV file.

Many thanks
Milton

Re: DI Studio 3.4 CSV Source

LinusH — Wed, 23 Sep 2009 07:02:27 GMT

Please attach a log so that you error messages can be available to be commented on.
Your error could be that you are specifying a numeric column, but there exists for some reason alphanumeric data.
For the field length, try using a informat with the lenght of 80.

/Linus

Re: DI Studio 3.4 CSV Source

milts — Thu, 24 Sep 2009 00:32:02 GMT

Hi Linus,

In the log there are error messages saying invalid data for value SOME_DATE and yes it's specified as numeric but there are no alphanumeric characters. But the problem is that there is no value for that field in the text source file. I checked the treat unassigned values to missing in the option also.

Regarding the field length, thanks it's now working.

Regards,
Milton

Re: DI Studio 3.4 CSV Source

deleted_user — Fri, 25 Sep 2009 14:59:18 GMT

It sounds like you ERROR is because there is some value in an expected date field that is not a valid date. For example you migh have something like '9999/99/99' in your field, or a year may be outside of the range specified by YEARCUTOFF (From memory, I think the highest year by default is 5499?)

As for your field length problem, I suspect that the issue will lie in the Begin & End position columns in your external table definition. You'll most likely need to check the overlap tick box before you're allowed to change them though. I guess you specified it incorrectly in the first place, and then just changed the Length column in the definition??

Re: DI Studio 3.4 CSV Source

milts — Fri, 25 Sep 2009 23:56:51 GMT

Hi simkinp,

I have checked the raw text file and the error appears on rows which has no value/blank in that field. that's why it's a little weird.

Regards,
Milton

Re: DI Studio 3.4 CSV Source

Patrick — Sat, 26 Sep 2009 02:14:18 GMT

Hi Milton

Try this:
In your "fixed-width source table" object in the tab "File Parameters"
- check the box "Pad column values with blanks"
- check the box "Treat unassigned values as missings"
- Choose a "logical record length" big enough for the longest line of data in your text file.

If this doesn't help:
Please send the log and/or the part of the generated code where the "infile" statement is.

HTH
Patrick

P.S:
The important checkbox is "Treat unassigned values as missings" as this adds a "truncover" to the infile statement.

Checkbox "Pad column values with blanks" adds a "pad" to the infile statement. Actually no more necessary as there is already a "truncover" - but it doesn't hurt to have it as well.

Re: DI Studio 3.4 CSV Source

milts — Sat, 26 Sep 2009 05:36:49 GMT

Hi Patrick,

Those are checked already. truncover is in the option and the lrecl i set is 32000.

Will there be a difference if I add the DSD option?

I'll post the logs by Monday.

Regards,
Milton

Re: DI Studio 3.4 CSV Source

Patrick — Sat, 26 Sep 2009 06:15:40 GMT

"Will there be a difference if I add the DSD option?"
Not with fixed length as you are reading by column position and don't use a delimiter to decide where a new field starts in the record.

If truncover is already there:
Could it be that there are some invisible control characters in your text file. That can happen when textfiles are copied from Windows to Unix with Unix only having LF at the end of a line and Windows having CR/LF.
Is this field you're having problems with at the end of your record?

Re: DI Studio 3.4 CSV Source

deleted_user — Mon, 28 Sep 2009 11:12:55 GMT

Apologies, I mis-read data as date so my first comment is kind of irrelevant! (The regular Friday evening rush to leave work!)

As for your truncation problem, I'm presuming that you can actually see your data is being truncated? DI Studio will not truncate your data unless it is told to do so, and it won't even map you column if the target is shorter than the source, Meaning the problem is certainly in the external file.

I'd suggest you've got one of the following issues

Your LRECL is too short meaning just your last column has been truncated.

or

There is a difference of opinion between your Length, Informat and Begin/End positions (The length of these should all be the same). Don't trust the import wizard, it does get things wrong occaisionally.

Re: DI Studio 3.4 CSV Source

Peter_C — Thu, 01 Oct 2009 09:31:47 GMT

when handling delimited data allways use DSD.
Otherwise it isn't needed.
peterC