Connect with EXCEL

chitravallivenkat · Post by **chitravallivenkat** » Tue Dec 11, 2007 3:37 am

Hi,

How can excel be connected with datastage?

Thanks,
Venkat

ArndW · Post by **ArndW** » Tue Dec 11, 2007 3:46 am

On your windows platform, quite easily using ODBC and defining a system DSN on your server.

chitravallivenkat · Post by **chitravallivenkat** » Tue Dec 11, 2007 3:55 am

does it need UserId and Password also? or only DSN is enough?

ArndW · Post by **ArndW** » Tue Dec 11, 2007 4:23 am

DSN is enough for Excel access.

chitravallivenkat · Post by **chitravallivenkat** » Tue Dec 11, 2007 4:46 am

Thanks ArndW.

chitravallivenkat · Post by **chitravallivenkat** » Tue Dec 11, 2007 6:16 am

should Fields need to create in the excel like DB?

ArndW · Post by **ArndW** » Tue Dec 11, 2007 6:45 am

Could you perhaps rephrase that? You do need to define rows and columns so that the ODBC drivers can detect them and access them; if that is what you are asking.

chitravallivenkat · Post by **chitravallivenkat** » Tue Dec 11, 2007 7:25 am

I mean i want to store the data there. For that, do we need to define the columns?

ArndW · Post by **ArndW** » Tue Dec 11, 2007 7:31 am

I can't recall - why don't you try it without predefined columns to see? If it doesn't work you'll have your answer.

chitravallivenkat · Post by **chitravallivenkat** » Tue Dec 11, 2007 7:39 am

ArndW,

I did not create any job using excel. I want to tell the requirements to client to connect excel. That is why i asked these questions. So now I understood from your answer that to connect with excel, DSN should be created and columns also should be defined.

Thanks Arndw to spend your valuable time to me.

Thanks.

scottopizza · Post by **scottopizza** » Thu Jan 03, 2008 2:37 pm

One solution we use is to create .csv (comma delimited) text files. Then when the user double clicks on one of these files, .csv is associated with Excel on their PC - so it opens in Excel. The nice thing about this is that we don't have to concern ourselves with what version of Excel the user has and we don't have to mess with different versions of Excel on the server. So, just an idea if you did not already know about it.

- Scott

ray.wurlod · Post by **ray.wurlod** » Thu Jan 03, 2008 2:51 pm

The ODBC driver for Excel requires - mandates - that the worksheet be in "pure" tablular format with column headings.