Topic
3 replies Latest Post - ‏2009-03-17T05:49:36Z by SystemAdmin
SystemAdmin
SystemAdmin
332 Posts
ACCEPTED ANSWER

Pinned topic Excel as map source

‏2006-01-12T17:19:37Z |
Hello

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.

Thanks mcuh.
Updated on 2009-03-17T05:49:36Z at 2009-03-17T05:49:36Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    332 Posts
    ACCEPTED ANSWER

    Re: Excel as map source

    ‏2006-01-13T10:03:17Z  in response to SystemAdmin
    Set up ODBC connection and use the spreadsheet as a DB. Create the tree through the Database Interface Designer.
  • SystemAdmin
    SystemAdmin
    332 Posts
    ACCEPTED ANSWER

    Re: Excel as map source

    ‏2006-01-19T19:19:41Z  in response to SystemAdmin
    Thanks 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.

    _____________________________________

    Question:
    What are the necessary steps to use an Excel spreadsheet in a map?

    Answer:
    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.
    • SystemAdmin
      SystemAdmin
      332 Posts
      ACCEPTED ANSWER

      Re: Excel as map source

      ‏2009-03-17T05:49:36Z  in response to SystemAdmin
      Hi,

      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?