Reading an Excel (xlsx) file
Moderators: chulett, rschirm, roy
Reading an Excel (xlsx) file
I set up the "Configuration Window for Unstructured Data stage" window. I am trying to read sheet 2 of 4 sheets. Under Range Option, I selected "Specify the entire data range". Under "Range expression", I specified "A2:G8". Under "Sheet names to skip", I specified "Qwe Rte;Fre Grd;Ogh Gre" - the three sheets I want to skip. Under "Column header", I selected None.
Issue1: When I click on Load, I get the error "com.ascential.asb.cas.shared.exceptions.CASDMDIException". Any ideas?
Issue1: When I click on Load, I get the error "com.ascential.asb.cas.shared.exceptions.CASDMDIException". Any ideas?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Try replacing the spaces with underscores in the tab names in the spreadsheet. Sometimes Unix is the culprit.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
I understand your situation. My suggestion is to have the creator of the file make the change, not have you make the change after receiving the file.splayer wrote:FranklinE, thank you for your response. I am trying to automate this process and having to rename each sheet with an underscore for a space would defeat the purpose.
I have data sources who use Windows exclusively. I impose upon them compliance with Unix requirements, or I reject their data. Seems harsh, but it's much simpler to correct a problem at the source.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
Exactly. Whatever it takes to make it work, fully automated, force that back on the source as a requirement once you figure it out. Been there, done that, got the t-shirt.
Only fall back to handling the quirks yourself if you absolutely have no other choice.
Only fall back to handling the quirks yourself if you absolutely have no other choice.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Try naming the sheet to extract in 'Range expression' and not populate 'Sheet names to skip'.
e.g. Assuming 'sheet 2 of 4 sheets' is named Tab2, code would be
If sheet 2's name has spaces (say Tab 2), then it can be enclosed in single quotes
e.g. Assuming 'sheet 2 of 4 sheets' is named Tab2, code would be
Code: Select all
Range expression: Tab2!A2:G8
Code: Select all
Range expression: 'Tab 2'!A2:G8
To skip multiple sheets (with space in name) use single quotes e.g.
I feel that 'skipping sheets' is suited for extracting data from multiple sheets (of a spreadsheet) with same layout; for extracting data from a single sheet, naming the sheet in 'range expression' is a better option.
Code: Select all
Sheet names to skip: 'Qwe Rte;Fre Grd;Ogh Gre'
rkashyap, thank you for your response. I have a spreadsheet with two sheets. I would like to skip the first and load the data from the second.
In "Range option", I tried "Specify the entire data range" and "Specify the start row". Under "Range expression", I have Sheet2!A1:G10. Under "Sheet names to skip", I have Sheet1. Under "Column header", I have "None". When I click on Load, I get the error:
org.apache.poi.xssf.usermodel.XSSFChartSheet (initiallization failure)
It doesn't matter if I put single quotes or not.
In "Range option", I tried "Specify the entire data range" and "Specify the start row". Under "Range expression", I have Sheet2!A1:G10. Under "Sheet names to skip", I have Sheet1. Under "Column header", I have "None". When I click on Load, I get the error:
org.apache.poi.xssf.usermodel.XSSFChartSheet (initiallization failure)
It doesn't matter if I put single quotes or not.
Sample code that I have seen has sheet-name in either "Sheet names to skip" or in "Range option" but not both.
i.e Try
Error messages that we have seen from Unstructured Data Stage are handled/meaningful, but the ones you are getting are raw.This may imply a deeper underlying issue.
On which flavor of Unix is DataStage server installed? Is Unstructured Data Stage working OK in other cases?
You may also validate by building and executing sample job as described in this link.
i.e Try
Code: Select all
Data ranges: Specify the entire data range
Sheet names to skip:
Range expression: Sheet2!A2:G8
On which flavor of Unix is DataStage server installed? Is Unstructured Data Stage working OK in other cases?
You may also validate by building and executing sample job as described in this link.
This sounds like a support issue.
Again ... On which flavor of Unix is DataStage server installed? Is Unstructured Data Stage working OK for a single tab spreadsheet?
You may build and execute sample job as described in this link to confirm that Unstructured Data stage works OK in your shop.
Again ... On which flavor of Unix is DataStage server installed? Is Unstructured Data Stage working OK for a single tab spreadsheet?
You may build and execute sample job as described in this link to confirm that Unstructured Data stage works OK in your shop.