TM1 Forum

Hi All,

I need to import data frequently from about 18-20 excel files generated by other application. Best way i found was through ODBC drivers as csv upload will be time consuming for user since user need to convert excel to csv about 20 time per one upload cycle.

However, it seems that 64bit tm1 server does not support excel ODBC drivers.

Wanted to know whether is that true? if no then requesting gurus to let me know how to use excel odbc drivers in 64 bit server.

If yes than whats the best alternative available.(Considering Source file will always be excel and want to avoid CSV).

PS: I am open for installation of any version of excel right from 2003 to 2010.

Regards,
Dhims

dhims wrote:However, it seems that 64bit tm1 server does not support excel ODBC drivers.

That is correct, there is no 64-bit ODBC driver for Excel.

dhims wrote:If yes than whats the best alternative available.(Considering Source file will always be excel and want to avoid CSV).

There is no alternative using Excel.

Hi

I think there is 64-bit ODBC driver for Excel, you can download it from here:
http://www.microsoft.com/download/en/de ... n&id=13255

What we did to make it work (Windows Server 2008 R2 64-bit, TM1 9.5.1 64-bit, Excel 2007 32-bit on the server):
1. Uninstall Excel 2007 (the 64-bit ODBC driver can not be installed over an existing 32-bit Excel 2007 instance)
2. Install the 64-bit ODBC driver
3. Reinstall 32-bit Excel 2007

After these steps taken, we were able to add 64-bit odbc connection to Excel files and so Turbo Integrator could use them as data source.
It is important to notice that we have not tested it in PROD environment we just wanted to know whether it is possible or not; and well, it seemed that it is possible.

Hi
If the only reason you want to avoid csv files is the time users have to spend converting the XL files into csv files why don't consider writing a macro that cycles through all the XL files for them. This is pretty straight forward and all they need to do is go to a maintenance workbook, execute the macro and run the import process.

I have previously implemented what you are trying to do (ODBC to XL) and have not gone back to that kind of solution ever since. The Excel files would get locked at times and the maintenance is just terrible as well: new file -> new ODBC connection.

Thanks for your answers:

Just to give more background about my purpose of asking for ODBC drivers:

I am implementing Tm1 in an insurance client where planning department receives 18 excel files in standard format from actuaries every time they revise certain drivers in a planning cycle (i.e. about 5-6 times). All the files have about 6 sheets, but all i need is one sheet containing data repository. SInce formats are standardised, i felt an ODBC connection is a better solution. I understand that i need to create ODBC connections for 18 files and need to create new when i will receive more files. But it also helps to avoid errors that occurs while converiting files to CSV (e.g. data elment having comma). Further it adds one more process step from users perspective.

Pls share your thoughts.....

Dhims

Do you have a SQL server available? You could have a table to hold the data, with a column to hold cycle #, or something like that, so you can distinguish between planning versions. Then you can query against this table, filtering on the correct version each time. Only one ODBC connection, all the data in one place, including historical data. This is how I would go. It's pretty easy to create VBA code to INSERT into the table from the Excel file, you don't even need an ODBC connection as you can hide the SQL connection info in the VBA and do an ODBC-less connection.

Alternatively, you could put DBS formulas in the Excel sheet. If the actuaries don't have TM1, or don't have write access to the cube then the formulas will be benign. All it would need is someone with WRITE access to open the file and recalc. A little more work than the SQL table but a whole lot cleaner than trying to use Excel as a database.

mvaspal wrote:Hi

I think there is 64-bit ODBC driver for Excel, you can download it from here:
http://www.microsoft.com/download/en/de ... n&id=13255

It seems the 64-bit ODBC driver(s) for Excel and other MS Office programs will work as long as there is no 32-bit Office on the same machine. This is a problem if the server has perspectives installed and also has TI using odbc connection to *.accdb, *.xlsx or *.csv; One of the functions will have to go

also an issue if web is installed on the same server

Hi,

Our environment:
Win Server 2008 64bit, TM1 10.1.1 FP1 64bit, all TM1 components on one box including TM1Web.

After the OS install, I installed first 'AccessDatabaseEngine_x64'. Then the 32bit Microsoft Excel 2007.

I was able to import a dimension from an excel file on the server through ODBC without any issues, even special Eastern-European characters came in. TM1 Web is running, I also tried to display an Active Form, it was converted from excel to websheet correctly.

My question: anybody using this config in a production environment? That is, 32bit Excel because of TM1Web and the 64bit Access database engine for ODBC at the same time?

Thanks
Matyas