locked
excel importer problem(s) RRS feed

  • Question

  • Hi!

    i've noticed that if i have lightswitch desktop application, i cannot use excel importer on win7 x64 (in this case), every time when i tried to import data messagebox shows and says silverlight security error, document must be in "my documents" folder.. ok so i've tried to put it in "users\user" folder, "users\user\documents" folder, and since i have libraries, documents path spans to another drive (eg. "E:\my documents") and even then it don't want to work.. BUT when i put it on xp in my documents folder, everything works just fine! is it "my documents" hard coded in excel importer?

    is it possible to change delimiters in excel importer (for web part), cause most of my exports is csv document, but with ";" sign, not ",", and even excel itself creates file with delimiters in ";" sign..

    and last one (for now), when i tried to import some datetime fields from excel, there are problems with localization, my localization is eg. Croatian and excel document have datetimes in US (or France) format, so when i import data sometimes it messes up months with dates and vice versa.. can this be fixed in some kind of way, or i must first open excel document, convert datetime fields to match my localization, and then import?

    thanks!

    Kivito 

    Thursday, August 11, 2011 8:53 AM

Answers

  • Having the file to be imported in your "My Documents" folder is a security restriction imposed by Silverlight. I don't believe there is a way round it.

    The restriction is to your "My Documents" folder not to the "Documents" library.


    Simon Jones
    • Proposed as answer by Yann DuranModerator Thursday, August 11, 2011 11:42 AM
    • Marked as answer by Kivito Thursday, August 11, 2011 1:11 PM
    Thursday, August 11, 2011 9:35 AM
  • I agree with Kivito that the CSV delimiter should be ';' as it is EXCEL standard. Ok, it's no work to replace all ';' by ',' in a file for us developers; but users dont' like such things...
    But fortunately there is the sourcecode available; under \Client\ExcelImporter\ExcelImporter.vb you find the code, in the ImportCommDelimitedStream() method you can change it easy to ';'. Good for learning how to build/modify LS extensions ;-) I will do it...
    Also, here you should be able to handle the problem with localized datetime fields.

    Importer problem with win7 x64 I couldn't reproduce in a standard os setup; reason seems to be the span of documents path to another drive; is E: a network drive?

    Regards Robert

     

    • Marked as answer by Kivito Thursday, August 11, 2011 1:11 PM
    Thursday, August 11, 2011 10:12 AM

All replies

  • Having the file to be imported in your "My Documents" folder is a security restriction imposed by Silverlight. I don't believe there is a way round it.

    The restriction is to your "My Documents" folder not to the "Documents" library.


    Simon Jones
    • Proposed as answer by Yann DuranModerator Thursday, August 11, 2011 11:42 AM
    • Marked as answer by Kivito Thursday, August 11, 2011 1:11 PM
    Thursday, August 11, 2011 9:35 AM
  • well, you're right, must have mixed something (cause i have 2 "my documents" in "user" folder, one original and one as library).. thanks for that..

    Kivito

    Thursday, August 11, 2011 9:56 AM
  • I agree with Kivito that the CSV delimiter should be ';' as it is EXCEL standard. Ok, it's no work to replace all ';' by ',' in a file for us developers; but users dont' like such things...
    But fortunately there is the sourcecode available; under \Client\ExcelImporter\ExcelImporter.vb you find the code, in the ImportCommDelimitedStream() method you can change it easy to ';'. Good for learning how to build/modify LS extensions ;-) I will do it...
    Also, here you should be able to handle the problem with localized datetime fields.

    Importer problem with win7 x64 I couldn't reproduce in a standard os setup; reason seems to be the span of documents path to another drive; is E: a network drive?

    Regards Robert

     

    • Marked as answer by Kivito Thursday, August 11, 2011 1:11 PM
    Thursday, August 11, 2011 10:12 AM
  • thanks Robert, i will try to modify excel importer..

    E:\ is local drive, but now it works, it must be something i messed up "in steps".. it happens.. often.. :)

    cheers!

    Kivito

    Thursday, August 11, 2011 10:34 AM
  • Umm, doesn't CSV stand for COMMA separated values?

    Yann

    (plus ça change, plus c'est la même chose!)

    Thursday, August 11, 2011 11:42 AM
    Moderator
  • Yes, CSV stands for Comma Separated Values.

    The use of semi-colon as the column separator is a localisation issue for cultures that use comma as the decimal point instead of a fullstop (period).


    Simon Jones
    Thursday, August 11, 2011 12:56 PM
  • If your users are using Windows, they could change the delimiter character as described in the link below.

    If they are Windows7 users the change is in Formats > Additional Settings (similar to below).

    http://answers.yahoo.com/question/index?qid=20071105104642AAFn4pc 


    Mark

    Monday, March 19, 2012 11:54 PM
  • My issue is that the importer is not respecting the format of the comma separated file.

    e.g.

    "J\d{3,}",UpdateTicket,5,,English,TRUE

    it is ignoring the fact that the first value is everything in the "" and instead is trying to split it on the comma inside the quotes.

    Anyone go any ideas?

    Thursday, October 4, 2012 3:30 PM
  • Yeah for me.

    I modified the ExcelImporter to use ; instead of ,

    Changed my list separator to ;

    It now imports correctly.

    Now just got to work out how to save my worksheets as csv with  ; separator from the Excel macro I am using.

    Thursday, October 4, 2012 4:35 PM