Thursday, January 20, 2011

OBIEE: Import Excel File as Source

You would have thought this would have been as easy as the text file or xml file...but it's not. One very important step was missing. Thanks to Mr. Minkjan for providing me with that missing step.

The file I used can be found here.

Everything else you need can be found on your local BI Server.

It's been awhile, so I'll run through the steps quickly.

1. Open up your ODBC Data Sources.
2. Select the driver you want to use.



3. Name your ODBC Data Source



4. Select your workbook.



5. In the Admin tool, go to File -> Import (10g) and select your data source. Just ignore those neoview connections, thankfully I don't have to use that one much anymore (but when I do...argh, it kills me).



6. Import that file/folder



7. What you'll have in your physical layer is this:



That doesn't do me much good does it? I tried to manually create a table, called Sheet1 like this:



And this is where Mr. Minkjan's article came in handy...you have to name the ranges. Ah, yeah...makes sense? I had assumed (I know, I know what they say about that) it would be named sheets.

So how do you name ranges?

8. Highlight your selected rows/columns, right click, "Name a Range"



9. That brings up a box like this, now just call it LOB.



10. Now repeat steps 1-5 and when you get to the Import step, you should see something like this:



Easy!

Thanks to Mr. Minkjan for continuously writing up these obscure little details. They make my life, and others, so much easier.

5 comments:

Fahd Mirza said...

Thanks for sharing this tip.

jrod said...

I got the tables in but no columns ? What is wrong ? Is there a naming convention I need to follow for the columns ?

oraclenerd said...

did you include the column names in the "Named Range" portion?

Me said...

By any chance do you know the process for "importing data from excel" in OBIEE 11g. I have created the system DSN and then I went into the RPD and selected File -> Import Metadata but I am not seeing the DSN that I created for excel.
I see that DSN in OBI 10g with File -> import from database option.
Please let me know if you have any idea. Thanks in advance.

oraclenerd said...

@Me

I don't think I've tried it in 11g yet. Might be something for me to try out though...i'll post back if I do.

chet