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

Hi there,

I am trying to accomplish the same, with the following code in a job that exports products to Excel using the ExcelProvider as the destination:

foreach (var column in _mapping.SourceTable.Columns)
{
    if (row.ContainsKey(column.Name)) // It could be that the field exists in the source but is not mapped.
    {
        if (column.Type == typeof(string) && row[column.Name] == DBNull.Value)
        {
            row[column.Name] = "";
        }
    }
}

That works fine for string columns (which I am filtering out explicitly). However, I can't get it to work with other types like int or DateTime. When I tried to set it to an empty string, I get this: "Specified cast is not valid". I think that makes sense as at this point, the underlying type is still an int or DateTime.

In the ExcelDestinationWriter I see this:

if (row[columnMapping.SourceColumn.Name] == DBNull.Value)
{
    r[columnMapping.DestinationColumn.Name] = "NULL";
}

So when the underlying value is DBNull, the value is set to a string with the text NULL. Would it be an option to move the NULL string into a setting on the Excel provider (defaulting to NULL) so I can set it to an empty string in the settings? That way, I can remove my table script and handle this automatically, for all types, and not just strings and for all jobs in the future?

Please?

Imar

And while we're on this topic, can the DateTime format string be moved to the desintation provider's settings as well?

private string GetValue(ColumnMapping columnMapping, Dictionary<string, object> row)
{
    if (columnMapping.SourceColumn.Type == typeof(string) || columnMapping.SourceColumn.Type == typeof(int) || columnMapping.SourceColumn.Type == typeof(double)
                || columnMapping.SourceColumn.Type == typeof(float) || columnMapping.SourceColumn.Type == typeof(decimal) || columnMapping.SourceColumn.Type == typeof(bool)
                || columnMapping.SourceColumn.Type == typeof(long))
    {
        return row[columnMapping.SourceColumn.Name].ToString();
    }
    else if (columnMapping.SourceColumn.Type == typeof(DateTime))
    {
        return ((DateTime)row[columnMapping.SourceColumn.Name]).ToString("dd-MM-yyyy HH:mm:ss:fff");
    }
    return null;
}

I realy need a date only but with the current format string I always get the time as well.

Imar

Hi Imar,
that is already implemented but for DW10: Integration activities | Dynamicweb 10 Developer Documentation

You can set the value to DBNull.Value for the int/DateTime columns in the table script

row[column.Name] = DBNull.Value;

Or try to use the calls to ValueFormatter.GetFormattedValue or columnMapping.ConvertInputToOutputFormat methods.
BR, Dmitrij

Unless I am misunderstanding, I think my case is a bit different than what you are suggesting. I would like to control the value that is inserted in an Excel cell when Excel is the destination. My value currently is already DNBull which causes this code in the Excel writer to insert the string literal "NULL" in my sheet:

if (row[columnMapping.SourceColumn.Name] == DBNull.Value)
{
    r[columnMapping.DestinationColumn.Name] = "NULL";
}

Instead of "NULL", I want an empty string. If the null string was a parameter on the job I could set it to "NULL" to get nulls or to "" to get nothing. I.e:

if (row[columnMapping.SourceColumn.Name] == DBNull.Value)
{
    r[columnMapping.DestinationColumn.Name] = nullString;
}

where nullString is a constructor parameter for the writer coming from the Provider.

Same for the date format: it now seems hardcoded in the writer to a fixed format including time. If the format string was on the job, I could control the output.

return ((DateTime)row[columnMapping.SourceColumn.Name]).ToString(formatString ?? "dd-MM-yyyy HH:mm:ss:fff");

Also, I am on DW 9 and upgrading is not an option right now.

Thanks!

Imar

Attached a ZIP with the provider and destination, containing the changes I had in mind. Happy to create them as a PR if they make sense.

I've just tried this in DW 10 and the Null Handling works for me. I set it to a constant and then left it empty. That then inserted empty strings in the Excel file instead of the text NULL. Nice!

However, there are two things still I'd like to see improved:

1. Set the Null handling at the job level and then inherit and override at the column level as needed. In my example, I am exporting an Excel sheet with 150 columns., Setting the null handling 150 times isn't fun :-) Setting it at the job level would be much easier and then I could override it on just a few columns as needed.

2. Allow me to set the date/time format and other formats like numbers as well. Currently the date is exported as dd-MM-yyyy HH:mm:ss:fff which isn't always what I need. Allowing me to set a format string for date / time at the job level with overrides at the column level would give me full control. Same with other types like decimals.

Should I create these as a feature request on Github?

Imar