Reading an Excel (xlsx) file

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

splayer
Charter Member
Charter Member
Posts: 502
Joined: Mon Apr 12, 2004 5:01 pm

Post by splayer »

It works when I have just one sheet in the whole spreadsheet. It is on Linux.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

As noted, involve support if you haven't already.
-craig

"You can never have too many knives" -- Logan Nine Fingers
splayer
Charter Member
Charter Member
Posts: 502
Joined: Mon Apr 12, 2004 5:01 pm

Post by splayer »

Ok, I gave up the idea of loading data from multiple sheets.

kashyap, the link you gave has the same steps as the ones in the "Guide to Extracting Unstructured Data". Anyway, I set up the job to load from a single sheet spreadsheet. I followed the steps given in the document. Job runs fine but does not load any data.

Just curious, has anyone been able to load data using the unstructured data stage from Excel?
rkashyap
Premium Member
Premium Member
Posts: 532
Joined: Fri Dec 02, 2011 12:02 pm
Location: Richmond VA

Post by rkashyap »

We have several jobs which read both single and multiple tab Excel spreadsheets using Unstructured data stage and load Oracle tables.

In our experience, design time error messages from Unstructured data stage are handled/meaningful, so I was surprised to see the raw messages from your system. Did you contact support for these issues?
splayer
Charter Member
Charter Member
Posts: 502
Joined: Mon Apr 12, 2004 5:01 pm

Post by splayer »

The job works fine now but does not move any data. I get a message like this, "The length of column Col1 cannot be validated because character set conversion is involved. Truncation may occur without warning". This is not an error in the log but just a message. Seems to be a NLS issue. Any ideas?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

More than once you've been asked if you've contacted support on this issue... have you?
-craig

"You can never have too many knives" -- Logan Nine Fingers
splayer
Charter Member
Charter Member
Posts: 502
Joined: Mon Apr 12, 2004 5:01 pm

Post by splayer »

It seems like rkashyap has been able to make it work so wanted to hear from him.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

And still no answer to a simple yes/no question... guessing that means no.
-craig

"You can never have too many knives" -- Logan Nine Fingers
splayer
Charter Member
Charter Member
Posts: 502
Joined: Mon Apr 12, 2004 5:01 pm

Post by splayer »

I did not contact support because the process is very complex in my organization.

rkashyap, can you tell me, on the unstructured data stage, during reading - that is on the Output tab, what values you have for your columns for SQL type, Extended, Length, Scale, Nullable etc.?
rkashyap
Premium Member
Premium Member
Posts: 532
Joined: Fri Dec 02, 2011 12:02 pm
Location: Richmond VA

Post by rkashyap »

So far we have not encountered any NLS/Unicode character issue while importing from excel spreadsheets. Output tab of Unstructured data stage has following system generated values:
  • SQL type: VarChar for all columns imported from "Excel Column" and most of the columns imported from "Property". Few columns imported from "Property" have value Integer (RowNumber, FileSize, RowIsHidden) and Date (LastModifiedDate, ContentCreationDate).

    Extended: (not populated)

    Length: (not populated)

    Scale: (not populated)

    Nullable: Yes for all columns imported from "Excel column" and most of the columns imported from "Property". No for some of the columns imported from "property" e.g. SheetName, LastModifiedDate, RowNumber, RowIsHidden, FileName etc).
splayer
Charter Member
Charter Member
Posts: 502
Joined: Mon Apr 12, 2004 5:01 pm

Post by splayer »

After Import, I get the same values. Job runs fine. No errors, no messages. Just the data doesn't get moved.
rkashyap
Premium Member
Premium Member
Posts: 532
Joined: Fri Dec 02, 2011 12:02 pm
Location: Richmond VA

Post by rkashyap »

Support.
Post Reply