Has anyone worked with excel as source of data. Going through the documentation, I could see that ODBC connectivity can be used for excel files. I would like to know the procedure of creating type tree and assigning the excel sheet to a card in the map.
Any help is appreciated. Examples listed somewhere would also be good enough.
This topic has been locked.
3 replies Latest Post - 2009-03-17T05:49:36Z by SystemAdmin
Pinned topic Excel as map source
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2009-03-17T05:49:36Z at 2009-03-17T05:49:36Z by SystemAdmin
Re: Excel as map source2006-01-19T19:19:41Z in response to SystemAdminThanks Smoley for the reply.
From knowledge base I got information on how to create type tree and use it on a map. Please find it attached here. Its article # T855.
What are the necessary steps to use an Excel spreadsheet in a map?
In order to utilize Excel as a Source or Target Cards for a Mercator application map, please follow the steps detailed below:
1. Create the .XLS spreadsheet.
Note: The data sheet should be in a columns and rows format.
2. Label each column.
Note: Avoid using general and automatic data types assigned to the data in each column, instead use text, number, etc.
3. Select the range of cells to be used in the map, and include the column headings in the range. b:15783ab6f0Give the range of cells a name.[/b:15783ab6f0] Note: To name the range, select the menu option: Insert, Name, Define. Create the name and select ADD
4. Create an ODBC DSN in the Control Panel ODBC settings, and select ADD.
5. Select the Microsoft Excel Driver.
6. Select the correction version of Excel.
7. When asked for ?Workbook?, enter the name of the .XLS file created in step 1.
8. If the Excel spreadsheet will be used for Target in the ODBC settings, un-check the ?read only? option.
9. In the Database Interfase Designer, create a Database using the ODBC DSN created in step 8.
10. To use Excel as a source, create a query; Select * from , and then generate tree from Query.
11. To use Excel as a target, generate a Mercator type tree from Table, and select the RangeName from the list of displayed tables.
12. In the Map Designer, if you are using Excel as a Source, select Database in the input card, the tree created for the query and associate the corresponding MDQ file and query. If using Excel as a Target, select Database in the output card, the tree generated for the Table, the MDQ file and the range name surrounded by ? in the Table name.
This works perfectly fine.
Only issue is that the range has to be defined in excel sheet using the same name. Its totally understanable, bcos the sheet is now treated as table with the specified name.
But in our system, files come in auto mode, there is no way for us to define names and then process. Is there any other way to generate type tree from excel sheet.
Thanks in advance.
Re: Excel as map source2009-03-17T05:49:36Z in response to SystemAdminHi,
We are using the excel file as input.
created DSN and followed all the steps mentioned above.
But we are facing problem that we get the new file every day with differnt name
eg. STKddMMyy.xls where ddMMyy is current date.
we are using event server to run the mmc.
File is not present in directory. It comes at perticualr time.
Event server triggered on 00:00 hrs and creates blank excel file. When the new input file is generated and we are trying to trnasfer it gives error.
is there any solution to use excel file as inuput?
or how to prevent the odbc to created blank file?