Excel buffer ReadSheet() Ignore commas!!

Comments

• Options

  vaprog Member Posts: 1,118

  2014-11-06

  Make sure your automated Task uses the right locale. It's currently using one that uses comma as a thousands separator.

  0
• Options

  Arturo Member Posts: 30

  2014-11-06

  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

  0
• Options

  Arturo Member Posts: 30

  2014-11-13

  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....

  0
• Options

  Arturo Member Posts: 30

  2014-11-24

  Any ideas?
  The Nav job queue is always hanging or failing... I really need to use power shell to launch my excel scheduled processes.

  0
• Options

  Rikt-It Member Posts: 37

  2014-11-25

  Do I recall..
  In functions as "format", "validate"... there is an option "9" for XML.
  
  Take a look...

  Regards
  Christer in Stockholm, Sweden

  0
• Options

  Arturo Member Posts: 30

  2015-01-08

  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

  0
• Options

  vaprog Member Posts: 1,118

  2015-01-08

  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?

  0
• Options

  Arturo Member Posts: 30

  2015-01-12

  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.

  0
• Options

  Arturo Member Posts: 30

  2015-01-14

  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.

  0