Excel buffer ReadSheet() Ignore commas!!

Arturo
Arturo Member Posts: 30
edited 2015-01-14 in NAV Three Tier
Hello!
I have a problem loading a excel file to Nav. When I run the process manually everythink works fine, but when i use Powershell (as scheduled task with no user inteface) the process loads de decimal values with no commas.
If the value is 382,89 it load's 38289 to nav record.
Any idea how to solve this?
Thanks!

Comments

  • vaprog
    vaprog Member Posts: 1,163
    Make sure your automated Task uses the right locale. It's currently using one that uses comma as a thousands separator.
  • Arturo
    Arturo Member Posts: 30
    Thanks for your answer!
    I alredy check that (the problem persists even if I launch the task from powershell, with no task scheduler).
    As you say i think that the problem is the locale, but the question is how I set the locale for excel if I'm not using any user interface?? I have been looking for a while for this, but nothign :(:(
  • Arturo
    Arturo Member Posts: 30
    I tried to launch the task on a windows server with a windows spanish version, wich have a comma as decimal delimiter as default, i also check the international key on the winodws register, and the decimal separator is ok. Even like this, the scheduled task with no user interface keeps using the english decimal setup, ignoring the commas....
  • Arturo
    Arturo Member Posts: 30
    Any ideas?
    The Nav job queue is always hanging or failing... I really need to use power shell to launch my excel scheduled processes.
  • Rikt-It
    Rikt-It Member Posts: 37
    Do I recall..
    In functions as "format", "validate"... there is an option "9" for XML.

    Take a look...
    Regards
    Christer in Stockholm, Sweden
  • Arturo
    Arturo Member Posts: 30
    Rikt-It wrote:
    Do I recall..
    In functions as "format", "validate"... there is an option "9" for XML.

    Take a look...
    Hi,
    Thanks for your answer but still nothing. The problem is not Nav, is excel. If excel removes the commas on open it doesn't matter wich regional setup Nav uses to load data, the information is alredy lost, commas or points.
    I will keep searching.
    Regards
  • vaprog
    vaprog Member Posts: 1,163
    Arturo wrote:
    The problem is not Nav, is excel.

    How did you verify this?

    What kind of "Excel file" do you actually have? is it a .csv, an .xls, a .xlsx or anything else? Does the actual file content match the extension?

    How do you actually use Powershell in the process?

    By what means do you read the excel cell values? Do you use NAV's Excel Buffer? Which version?
  • Arturo
    Arturo Member Posts: 30
    Hi,
    Thanks again for you time.
    It's a xlsx file, i' m using powershell for launching tasks for multiple tenants/DBs with bucles and some other stuff.
    And yes, i'm using excel buffer:

    ExcelBufAux.SETRANGE(ExcelBufAux."Column No.",3);
    IF ExcelBufAux.FINDFIRST THEN
    Decimal := ExcelBufAux."Cell Value as Text";

    I asume that Excel is the problem cause if i load same values from an another file type, .txt or something everything works perect, even launching tasks with no user interface using powershell.
    And if i schedule the excel task using the NAV Job queue, everithing is correct because uses the NAS service loggin account and applies the ruser regional setup.

    I fear that is an Excel bug, i searched and tried for a long time with no results.
    Thanks again 4 your answer!
    Regards.
  • Arturo
    Arturo Member Posts: 30
    Finally I found the solution!
    Before calling the report wich opens the excel (excelbuffer.openbook), i set the following parameters:

    dnGlobalization := dnGlobalization.CultureInfo('es-ES');
    dnThread.CurrentThread.CurrentCulture := (dnGlobalization);

    And the Dotnet Variables used are:
    dnThread DotNet System.Threading.Thread.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
    dnGlobalization DotNet System.Globalization.CultureInfo.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

    Regards.

Categories