Topic
  • 6 replies
  • Latest Post - ‏2011-05-15T07:23:46Z by SystemAdmin
SystemAdmin
SystemAdmin
9224 Posts

Pinned topic can't import a spreadsheet using excelservice

‏2011-05-10T16:07:27Z |
NOTE: this thread was migrated from projectzero.org. Some content and formatting may have been lost in the move.


Originally posted by projectzero userid: barny - f=4&t=2307#p9366
I've got a simple Excel 2003 spreadsheet I want to import to use in a datagrid, so I added a line to ivy.xml:

<dependency name="zero.excelservice" org="zero" rev="[1.0.0.0, 2.0.0.0["/>

I don't have the faintest idea if this is correct - how can I validate it in general? I've put it as the last dependency, is that OK?

Anyway zero resolve works and I can start the app and get to the /excel.html and try to upload my file but whatever I do I get:

CWPZC7008W: The resource named qwe could not be created.

I've tried: deleting the other worksheets, specifying the worksheet name (Sheet1), deleting all but two rows of data, deleting all but two columns of data, replacing my data with qwe, asd, etc., explicitly specifying the rows to be imported as 1-3, checked all my column names are alphabetic (no symbols), making sure all cells have something in them, saving in excel95 xls format, removing all cell background colours, ...

Help?!
Updated on 2011-05-15T07:23:46Z at 2011-05-15T07:23:46Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    9224 Posts

    Re: can't import a spreadsheet using excelservice

    ‏2011-05-10T16:42:32Z  

    Originally posted by projectzero userid: barny - f=4&t=2307#p9367
    Turns out the error message was only half the story - the models and fixtures for all my attempts (15 or more) were created although presumably there is something wrong with one of them. Sigh. Any suggestions for the best way to debug this process of uploading a spreadsheet? How can I check what has been created in Derby, or do I have to delete all the spurious attempts, then do a model reset?

    When the constraints listed for spreadsheet say that "cells must not have a null value", does that mean they must not be empty?
  • SystemAdmin
    SystemAdmin
    9224 Posts

    Re: can't import a spreadsheet using excelservice

    ‏2011-05-12T06:16:34Z  

    Originally posted by projectzero userid: EricWang - f=4&t=2307#p9368
    Turns out the error message was only half the story - the models and fixtures for all my attempts (15 or more) were created although presumably there is something wrong with one of them. Sigh. Any suggestions for the best way to debug this process of uploading a spreadsheet? How can I check what has been created in Derby, or do I have to delete all the spurious attempts, then do a model reset?

    When the constraints listed for spreadsheet say that "cells must not have a null value", does that mean they must not be empty?


    Hi Barny,
    I'm afraid the error was caused by zero.excelService not support your spreadsheet file type. The best file type will be "Microsoft Excel 97-2003(*.xls)". Please save as your spreadsheet to Microsoft Excel 97-2003 type and try again.

    When the resource was created, you can check the ZRM data model created by zero.excelService in the app/models directory. If you set "Overwrite existing resource" checked, I think you need do no more things.
    You can find more details here http://www.projectzero.org/sMash/1.1.x/ ... vices.html

    here is my data.xls and test result for your information, wish that will help you.

    thanks,
    Eric
  • SystemAdmin
    SystemAdmin
    9224 Posts

    Re: can't import a spreadsheet using excelservice

    ‏2011-05-12T13:26:22Z  

    Originally posted by projectzero userid: barny - f=4&t=2307#p9369
    Eric

    Thank you very much for your help. Being able to import your example really cheered me up! Can you please raise a bug on the documentation that it should specify excel 2003 xls file format. I am using Excel 2003 but the default file format seems to be different and I had to explicitly save as 2003 format to get the excelservice to import my file.

    So what I've found now is that excelservice (or ZRM) is incredibly sensitive to the names in the first row. The sensitivity doesn't seem very predictable which makes the whole thing really difficult. When it fails it has created the model and fixture files but then I'm guessing it goes to Derby to create the tables and that fails, and it bombs out before creating the resource stub (containing just ZRM.delegate()). After the failure it doesn't tidy up so you're left with lurking model and fixture files to tidy up manually. Yuk, can someone create a bug on that too?

    My original column names (from my data source) were:
    INSTANCENAME BUNDLESUBCOMPONENTID STARTTIME METAL DATACENTER Hours Rate Cost Usage Customer RealOwner

    Without changing anything except the first row, I've got this to work:
    instName bundle startTime metal datacenter hoursRun rateHour costTotal usage1 customer realOwner

    BUT change usage1 to usage and it doesn't import any more. Or sometimes it does. I'm confused. All I want to do is import a spreadsheet. This is all so appallingly opaque that I'm losing the will to use Smash.

    Thanks anyway for your help
    Ian
  • SystemAdmin
    SystemAdmin
    9224 Posts

    Re: can't import a spreadsheet using excelservice

    ‏2011-05-13T06:43:39Z  

    Originally posted by projectzero userid: EricWang - f=4&t=2307#p9370
    sorry for your confusion, "usage" and "hours" are reserved words of sMash. I think we should list them on the document.

    A useful way to check the data model is like this:
    1. use excel.html to import your excel file
    2. look for the ${Resource Name}.json in /app/models and ${Resource Name}_data.json in /app/models/fixtures direcotry.
    3. delete db directory. (Please remember backup your db directory first if it has useful data)
    4. issue "zero model sync" and check the output "CWPZT0600I: Command model sync was successful"
    5. if your excel column contain a reserved word, it will give an error: zero.resource.exceptions.DataFormatException: CWPZC9244E: Field name -> Hours of model -> data1 is a reserved word and cannot be used.

    Then import the excel file again and the stub files will be generated.

    For the column name, zero.excelService will convert the name to a capital character with lowercase others and trim the space character.

    Hope this will helpful for you.
  • SystemAdmin
    SystemAdmin
    9224 Posts

    Re: can't import a spreadsheet using excelservice

    ‏2011-05-13T08:50:03Z  

    Originally posted by projectzero userid: barny - f=4&t=2307#p9371
    Ah, an explanation. Thanks!

    > "usage" and "hours" are reserved words of sMash. I think we should list them on the document.
    So there is a documentation bug, in the ZRM section http://www.projectzero.org/sMash/1.1.x/ ... ation.html where it says:
    Field name restrictions

    Several restrictions exist on resource model field names. A field name:

    * cannot be a Java™, Groovy, or SQL reserved word
    * cannot contain a hyphen
    * cannot begin with an underscore or contain more than one underscore in a row, due to the way the query lookup syntax works
    * cannot be an empty string
    * must begin with an alpha character
    * cannot contain spaces

    "Smash" should be added to the first bullet, with a link to the list of Smash reserved words

    > "usage" and "hours" are reserved words of sMash. I think we should list them on the document.
    And that's another documentation bug on the excelservice http://www.projectzero.org/sMash/1.1.x/ ... vices.html that it should link to the field name restrictions documented above, maybe in a 'troubleshooting' section which also contains your 5-step debugging sequence.

    > "usage" and "hours" are reserved words of sMash.
    So that's another couple of bugs on the excelservice implementation to a) not leave model and fixture files lying around after a failed import, and b) give more (any!) detail in the error message so the user doesn't have to debug it themselves given that the only way of debugging is trial and error which is a very disappointing way to reward the user for trying Smash.

    I really like Smash but I'm finding it difficult and time-consuming when things don't work because error messages aren't detailed at all, because of incompleteness in the implementation (like excelservice leaving files after a failed import), and because of the incompleteness of the documentation.
  • SystemAdmin
    SystemAdmin
    9224 Posts

    Re: can't import a spreadsheet using excelservice

    ‏2011-05-15T07:23:46Z  

    Originally posted by projectzero userid: EricWang - f=4&t=2307#p9375
    Thank you very much.
    I'll fix the document bugs.