Accessing a DataStage file with Excel

[gpbarsky]

Hola mis amigos......

I need to generate an *.xls file from within DataStage. The generated file should be accessible from Excel program.

Does anybody know what is the format the file shoud has, in order to be "understandable" for Excel ?

Thanks in advance.

[Sainath.Srinivasan]

Use an Excel ODBC for this. Otherwise, create a csv file and use excel macros to convert to .xls.

[gpbarsky]

Thanks, but where is the Excel ODBC stage ? How can I use it ?

[Sainath.Srinivasan]

You can use ODBC stage in DS after configuring Excel ODBC in Windows.

[pnchowdary]

You can try what sainath suggested above or alternatively, you can use a regular sequential file stage and put the file name as .csv. Then you can import it in excel directly and if you want it in .xls format, then you can save it in .xls format using excel.

[gpbarsky]

Thanks to everybody.

Actually, I am generating a .txt file wth ";" as separators. This has to be this way because within the file I have amount fields with a "," separating decimals (in Argentina we don't use the "." as decimal separator, we use the ",").

But for my users the procedure to open the file, telling to Excel that this is a ";" separated file, may be a little complicated.

This is why I wanted to generate an *.xls file directly, in order to my user has to double click the file to open it and it is.

I really don't understand how to configure the ODBC to work with Excel files. Does anybody has any document to do this ?

Again, I really appreciate all your comments and suggestions.

Have a nice day, "mis amigos".....

[ds_developer]

On your (Windows) DataStage server, use the 'ODBC Data Source Administrator'. I think it is found on the Start menu under Administrative Tools. Once in there you should see a listing for Excel Files. Use the Help buttons for more details.

John

[Sainath.Srinivasan]

You can use semi-colon (;) or any other char as your delimiter and then convert them into tabular fashion within Excel.

[Sainath.Srinivasan]

The above is not a winking symbol. It was supposed to mean a semi-colon within brakets.

[ray.wurlod]

Edit the post and check "Disable Smilies in this post". ;)

[StefL]

I'm in a similar situation with a client, where a side application from the data warehouse we're building is to export some data directly to files that are then read with Excel.
My solution is the simpler one of the two you have to choose from - I make a .csv file using semicolon (;) as separator (in Sweden we also use comma as decimal separator). There is no problem opening the files with Excel directly, and all the users then need to do is to apply any formatting they like, and save the files as .xls if desired.

[gpbarsky]

Thank you StefL for sharing your experience. I did the same, and it is working.

Besides this, I wanted to know how can I generate "an Excel file". I sent a note to the administrator in order to generate the "Excel conection".

I will tell you if the solution worked, and if I could generate the Excel file, with all the features I wanted.

"Que tengas buen dia", Stef.

[StefL]

Well, I've basically had to explain to the client that Data Stage is not really a reporting tool (which it isn't) and that it's not made to write to any desired file format but rather to databases or sequential files (or hash files).
To get reports you usually use some reporting tool, and Excel in itself can always link to a sequential file and use it as data source - that is the alternative to actually opening the .csv file directly.

My Spanish is not good enough for me to know the meaning of 'tengas' but 3 out of 4 isn't too bad is it?
A very good day to you too gpbarsky!

[PhilHibbs]

There is no problem opening the files with Excel directly,

Do you mean by just opening the file in Excel, rather than going through the Import? The problem I have with Excel is that it strips leading zeros from string fields (such as telephone number).

[Sainath.Srinivasan]

You can import by selecting all to be char field.