Excel Data connection

Permanently deleted user

October 21, 2013 21:48

Hello,

I am running Windows Server 2008 and noticed I cannot get the data connections to work for excel.

When using the connection wizard in Geocortex site manager - I am able to create a connection but when tested - it fails:

DataConnection '': Unable to establish a connection for DataConnection ''. [ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]

Has anyone else run into this issue?

 

Cathy

0

• 

  Permanently deleted user

  October 21, 2013 22:13

  Just a guess but I am betting you need to have the 32bit excel odbc drivers installed on your server. I would check your odbc manger in windows server to see if you have the execel driver installed. There are 2 ODBC managers one for 32bit and one for 64bit I would check each one to make sure your server has the excel drivers.

  0
• 

  Permanently deleted user

  October 21, 2013 22:30

  Thanks for your reply.

  I was able to see the drivers for excel when I ran the 32 bit odbc c:\windows\sysWOW64\odbcad32.exe

  But unfortunately from the site manager, it is still showing the 64 bit options of only sql server drivers.

  0
• 

  Permanently deleted user

  October 23, 2013 00:19

  My solution:

  1st - Installed AccessDatabaseEngine_x64.exe drivers on my Windows 2008 server

  2nd - altered the connection string to be 

  Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};   DBQ=C:\Program Files (x86)\Latitude Geographics\Geocortex Essentials\County\REST Elements\Sites\Resources\Data\BUILDING_MAS.xls;

  This is for excel sheets save in  Excel 97-2003 format.

  3rd - In excel, created a Name Table (using Name Manager) and selected the rows and columns to include - saved as Permits.

  4th - In site manager, I was able to create the link using the Named Table (using the Sheet1$ did not work).

  Thanks

   

  Cathy

  0
• 

  Permanently deleted user

  February 14, 2015 20:03

  Hi, Cathy,

  I did the first 3 steps. It is all fine.

  However, I still have problem to retrieve table. What is the format of the command line?

  By default, it is "SELECT * FROM <the named excel table> WHERE <mylinkedfield> = ?", is this the correct format?

  Thanks in advance

  Helen

   

  0
• 

  Permanently deleted user

  February 15, 2015 14:48

  Hi,

  you will need to be sure you create your DSN in the proper 32 bit ODBC Admin window ("C:\Windows\SysWOW64\odbcad32.exe")  click run and type"C:\Windows\SysWOW64\odbcad32.exe"

   

  hope it helps

  Ramla

  0
• 

  Permanently deleted user

  February 15, 2015 14:52

  Hi,

  The command should be as the below if you are adding it from the data linking tab

   

  SELECT Year, ESTABLISHMENTS as [Total Businesses], EMPLOYEES as [Total Employees], PAYROLL_1STQ as [First Quarter Payroll], PAYROLL_ANN as [Annual Payroll] FROM ZIPCODEJOBDATA WHERE ZIPCODE = ?

   

  Thanks

  Ramla

   

  0

Please sign in to leave a comment.