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.
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.
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.
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.
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!
StefL wrote: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).