Upload Excel Format into TM1

[Charles Ang]

Hi,

In Cognos 10.1, the performance modeler able to upload .xls format file.
But there is less information about this technique.
I have google and gone through many documents. Mainly of the TI process is about doing in architect and upload csv file.

The situation are I am trying to upload a report format data in excel which is client refuse to change the format or some extra job.

There are several question regarding upload excel:
1. is it possible to automate upload excel file?
2. how tm1 get the excel file name? i have tried in .xls, it upload weird symbol. if use .csv, it become flat file and unable to choose the crosstable which upload excel have this choice.
3. when i create a process to execute the another upload excel process, it prompt me data source not found. however, when i execute the error process, it run successfully. why this is happening?

Have any advice on how to upload the excel report format data?

Thanks,

Charles

[macsir]

I don't think process can handle a excel file. As you can see, it only processes delimited or fixed width flat files. Or you can write some VBA to transform excel into this type of source file. Or load your excel into DB first and let TI read it from there.

[Charles Ang]

Hi macsir,

I have make separate process to upload separate excel file.
however this lead to another problem, when replace the data source with another file that is same format.
execute the process via action button, the process failed with data source not found.
but if execute in performance modeller, it have success the process.
why this is happening?

[dr.nybble]

TM1 Server is not able to read Excel files.

TM1 Performance Modeler / Cognos Insight use Apache POI (http://poi.apache.org/) to read .xls or .xslx files and convert them to CSV.

If the Excel data is in crosstab format, the client converts it to a list format. You can also do this in Excel with the Transpose feature.

The CSV file is transferred to TM1 using the blob API (if necessary) so as far as TM1 Server is concerned, it is just another CSV file to load.

The downside of this approach is that the data transformation occurs on the client before the import is run, so you cannot schedule this import using a chore.

Another option is to use the Microsoft Excel Driver to create an ODBC connection to the Excel source and load it that way.

[Duncan P]

I have loaded from an Excel file into TM1 using the Excel ODBC driver. Each sheet appears as a table. It works very well on the whole, but can in certain circumstances leave the workbook locked until the TM1 process terminates.

[Charles Ang]

Hi,

I am abit confused.
TM1 is running is 64-bit. Excel 2007 is 32-bit.
The odbc that TM1 connected is 64-bit which do not have excel driver.

How to create the Excel odbc driver that TM1 able to get it ?

[Duncan P]

Here is quite a fresh thread on exactly that topic.

Excel ODBC drivers in 64 bit Tm1 9.5.2 Server

[Charles Ang]

Noted. is there any guide or related post how to write the query to extract the odbc excel driver data?

[Duncan P]

Try this from Stack Overflow. The answer also links to a very comprehensive Microsoft knowledge base article.

[Charles Ang]

Thanks Duncun,