Topic
3 replies Latest Post - ‏2012-03-22T17:19:36Z by Jean_Anderson
DonH
DonH
58 Posts
ACCEPTED ANSWER

Pinned topic Text translated to Scientific Notation

‏2012-03-21T16:45:37Z |
I'm importing three fields in one feed (Name, Version, Amount) and merging it with another feed - the merge key is "Name" but this is not particularly relevant.

The "Version" field contains variable text - generally it's in the form : 4.0.30319 or similar. ie: numbers (sometimes there is the occasional letter but it's mainly numbers) Other examples - 10.50.1600.1, 4.01a36 and 1.0.0.0

After the "merge" the Version field is translated to Scientific Notation!
I've tried casting as a string.
I've tried casting as a string and replacing the "." with "-"
I've tried casting as a string and concatenating with the word "version" on either side of the number but each time the numbers in the field are translated into something else (resembling Scientific Notation).

The original feed is derived from a spreadsheet. I've formatted "Version" as text and I've even exported to CSV but nothing seems to work.

Any ideas???
Updated on 2012-03-22T17:19:36Z at 2012-03-22T17:19:36Z by Jean_Anderson
  • Jean_Anderson
    Jean_Anderson
    27 Posts
    ACCEPTED ANSWER

    Re: Text translated to Scientific Notation

    ‏2012-03-21T23:37:38Z  in response to DonH
    Hi, Don,

    What version of Mashup Center are you using? Also, the Version field is correct before the Merge operator?

    I can't reproduce your problem on 3.0.0.1, but below is what I tried. Maybe you could tweak this to something that reproduces the problem.

    1) Create a "Versions" CSV feed from a file with this content:

    
    Name,Version A,4.0 B,10.50.1600.1 C,4.01a36 D,1.0.0.0
    


    2) Create a "Descriptions" CSV feed from a file with this content:

    
    Name,Description A,Here is A B,Here is B C,Here is C D,Here is D
    


    3) Create a data mashup
    3.1) Added a source operator that loads the "Versions" feed
    3.2) Added a source operator that loads the "Descriptions" feed
    3.3) Added a merge operator that merges both on the "Name" element

    At this point, I ran the mashup inside the data mashup builder and had the output I expected.

    So, let's see if you can modify this to reproduce the problem.

    thanks,

    -jean
  • DonH
    DonH
    58 Posts
    ACCEPTED ANSWER

    Re: Text translated to Scientific Notation

    ‏2012-03-22T10:51:07Z  in response to DonH
    I created some test feeds using your suggestions and they worked as expected.
    I then copied some of the version numbers from my original source into the first CSV. This worked as it should too.

    So, clearly, the problem lies with my original input data...

    This is an XLSL file from Microsoft. It's a sample output from their software inventory analysis tool.
    MashupCenter doesn't handle this format of spreadsheet, so I converted it to XLS format to do my original feed creation. When that gave me the "scientific notation" problem, I exported the page in CSV format (from my XLS file) and, as in my original problem statement, I still had the problem. I also created a CSV directly from the original XLSL file but that did not work either. (at this point, I should say that I only have MS Excel 2003)

    To investigate further, I made a copy of the original CSV, deleted the content, pasted the content from your first suggested test feed into it, and created a new feed in the hub. (So I now have the original csv with new content). This worked as expected too.

    I'll send you the original XLSL offline - maybe you can see something in the format that I'm missing.

    In the meantime I might try opening and converting the original file using OpenOffice to see if that has any effect.

    Thanks
    • Jean_Anderson
      Jean_Anderson
      27 Posts
      ACCEPTED ANSWER

      Re: Text translated to Scientific Notation

      ‏2012-03-22T17:19:36Z  in response to DonH
      Yeah, Mashup Center doesn't support the Microsoft Excel 2007 XLSX format[1]. We're looking into supporting XLSX in a future release.

      regards,

      -jean

      [1] http://publib.boulder.ibm.com/infocenter/mashhelp/v3/topic/com.ibm.muh.useradmin.doc/userhelp/feedhelp/help_feeds_excel.htm