Developer forum

Hi Adrian,
it is not clear what is your destination provider for export from EcomProvider/source provider for import to EcomProvider that is in use use btw the EcomProvider?
Is it Xml provider or Excel, or some other?
BR, Dmitrij

Hi Dmitrij,

I am exporting from EcomProvider to Excel provider and importing from Excel Provider to EcomProvider.

Thank you,

Adrian

Hi Adrian,
I can not reproduce it, I have tried with Ecom/Dynamicweb and ExcelProvider jobs  and ExcelProvider package version 4.0.5.
Can you point which table and which columns values are getting exported/imported not correctly? What it your destination file extension? xls, xlsx, xlsm?
BR, Dmitrij

Hi Dmitrij,

I am exporting from EcomProducts mostly and my extension is xlsx.

And the DW version is 9.12.10.

Adrian

Hi Adrian,

If I have understood you correctly:

Excel is a bit troublesome to work with when it comes to handling NULL values, as NULL is not a fit type for a cell in Excel. This is why NULL ends up as a string value 'NULL' - and that kinda' sux when importing back in.

As it is not possible to script a solution directly in the mapping, I only see two solutions

1. Update the Excel sheet string 'NULL' with empty cell ('')
2. Use table script on the import job to set empty string, if string value is 'NULL'

Shooting straight from the hips the script would be something like

public override void ProcessInputRow(Mapping mapping, Dictionary<string, object> row)
   {
      if (mapping.SourceTable != null)
      {
         for (int i = 0; i < mapping.SourceTable.Columns.Count; i++)
         {
            if (mapping.SourceTable.Columns[i].Type == typeof(string) && mapping.SourceTable.Columns[i].ToString() == "NULL")
            {
               row[mapping.SourceTable.Columns[i].Name] = "";
            }
         }
      }
   }

/Snedker

Hi Morten,

Thank you very much for the suggestion. You have understood it right.

I was hoping that the Excel provider would have a built-in way of handling this scenario.

Of course, if there is no other solution, we'll try the TableScripting.

Thank you,
Adrian

Hi Guys,

Apparently, the problem with NULL happens also with DynamicwebProvider.

And there are a few other issues with int or double fields where the fields are empty.

I understand that Table scripting is probably my only option but I feel that I have to post a feature request with these issues.

Thank you,
Adrian

Hi Adrian,

If your source is Excel, it will make no difference whether your destination provider is Dynamicweb or Ecom. The problem is Excel as source - not the destination. If your source was XML the destination providers would have handled it all well.

But note your findings and post a request, and we will gladly review it.

Can you elaborate "And there are a few other issues with int or double fields where the fields are empty" ?

BR
Snedker

Hi Morten,

Thank you for the feedback.

I have posted my request in a feature request task: https://doc.dynamicweb.com/forum/feature-requests?ThreadID=80759

What I meant with double and int fields is that exporting an int field with no value renders another error in the import, even if the field is empty. For those cases (and maybe for other situations as well) it would be good to have the ability to set a default value in case the field is empty (or NULL). That would probably solve both issues.

If Excel is the problem, then we need to handle the problem when importing back. Which means that having the ability to set a default replacement value for Null and a default replacement value for Empty, would solve all cases. For Int we can set it to 0 for strings we can set it to empty.

One potential issue I see though is that we might end up importing empty lines if those lines have been exported with Null and we replace null with empty.

Thank you,
Adrian