Hello everyone,
we have current the problem that our Business Importer is importing the dates incorrectly into the database. Although the XML is correct. Has anyone encountered this mistake? Is this because of the version of Business Importer?
FlexnetManager 2020 R1
Business Importer Version: 15.0.0.4
I am very grateful to you for helpful answers.
Oct 16, 2020 08:20 AM
Hi Roman,
Business Imports will always add their own columns, like ROWNUMBER or matched. Therefore you cannot use a query returning the same column names. You'll have to try something like:
SELECT
[ContractNo]
,CONVERT(DATE, [PropertyValue], 126) AS Date
FROM ECMImport_Import1_DATA
Somewhere in your export process, there is a Byte Order Mark (BOM) created. That's those weird looking characters in from of your ContractNo. IF you can get rid of it, it may make things easier.
Best regards,
Markward
Oct 20, 2020 07:08 AM
Hi Roman,
Dates can be tricky because local system settings are usually used when interpreting the format. Do you have any details on the imported file and system language settings?
Best regards,
Markward
Oct 18, 2020 12:57 PM
Hi Markward,
thanks a lot for fast Feedback.
We have installed the Business Importer on the application server. And the DB is on a different server.
Here are the details in the Pictures from the Application Server, where we make the Import.
And the XML also in the Pictures.
Which settings really need to be set?
And which would be the correct regional settings?
Best Regards,
Roman
Oct 19, 2020 01:11 AM
Hi Roman,
Your server settings have day-month-year, while your XML file has year-month-date. I guess that could be an issue. Maybe you try US settings? Be aware that changing this might have an impact on other existing imports.
We usually run a 2 stage approach:
With these individual transformations we can import form different sources, using different date formats.
Best regards,
Markward
Oct 19, 2020 01:29 AM
Hi Roman,
Here's how I usually do all business imports:
Have an import step just transfering the data, without transforming it (1). Please check out the "Advanced Properties" "Use Physical Table" option. This ensures the data persists after the first import step ended. You might have to enable this area in the BAS options first. Then have the actual import step (2) access this data using SQL (3).
Now here you can use all the SQL magic to transform your data. It looks like you might want to use CONVERT. Your date format looks like ISO6801.
Best regards,
Markward
Oct 19, 2020 03:22 AM
Hi Markward
Thank you for your great tip. We did that but can't find the point how we can write the data back?
Here the wrong Data:
Than we make the physical Table and now the Data is correct:
And now we stand in line. We cannot write the data back.
An error has occurred when trying to run the query [SELECT *
FROM ECMImport_Import1_DATA]. The error message is [Column names in each table must be unique. Column name 'ROWNUMBER' in table '#ECMImport_Import3' is specified more than once.].
Column names in each table must be unique. Column name 'ROWNUMBER' in table '#ECMImport_Import3' is specified more than once.
Column names in each table must be unique. Column name 'ROWNUMBER' in table '#ECMImport_Import3' is specified more than once.
How do we get the staging table that references the import table?
We are already very close, maybe you have another great tip for us.
Best Regards,
Roman
Oct 20, 2020 06:48 AM
Hi Roman,
Business Imports will always add their own columns, like ROWNUMBER or matched. Therefore you cannot use a query returning the same column names. You'll have to try something like:
SELECT
[ContractNo]
,CONVERT(DATE, [PropertyValue], 126) AS Date
FROM ECMImport_Import1_DATA
Somewhere in your export process, there is a Byte Order Mark (BOM) created. That's those weird looking characters in from of your ContractNo. IF you can get rid of it, it may make things easier.
Best regards,
Markward
Oct 20, 2020 07:08 AM
Oct 22, 2020 03:25 AM