Importing External Data from Excel to Cognos 10.1 - Solved

[antexity]

Hi,

When I import and external data source, I choose the XLS file.   It contains to column, Model # and Desc.  Model number are Text/number and Desc just text.

Now when I import them into Cognos ,  it coverts the Model # all the numbers and now the relationship does match which I am missing values. 

I have formatted the cells in excel as text,  I saved the XLS as a txt document and then import, still the same.   In cognos I have determined the Model # as Int, and Tried Decimal and tried Text all the same issue.  I hope someone can help me out.

Thanks
Jonathan

[antexity]

Finally figured it out,  I hope this will save alot of time for users using C10 and using the new feature "Importing External Data"

If you want to import an Excel spreadsheet and you have values that contain Letters and numbers and once you import it to Cognos the letters and numbers are converted to a number string.  To prevent this from happening:

1) Save your spreadsheet as a XLS file (XL 2003)
2) Save As again as a TXT (delimited file)
3) Close the new txt spreadsheet and reopen it.
4) It will ask you to convert it to be readable by excel,  leave the default Windows (ANSI), click next and click each column that contains the letter and number values and select 'TEXT' and then click finish.   Save as a XLS file again.
5) In cognos re import the newly XLS file that previously was a TXT document
6) Sucessful import, and all the values stay the same.


if there are easier methods, I would like to know.

Thanks,
Jonathan