mfranz
Shining star

Re: Date Format not correct - Business Importer - FlexNet Manager on Prem

Jump to solution

Hi Roman,

Here's how I usually do all business imports:

2020-10-19_10h10_04.png

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.

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-v... 

Best regards,

Markward

Softline Group is Europe's leading independent expert in Software Asset Management.
romanmensch
Active participant

Re: Date Format not correct - Business Importer - FlexNet Manager on Prem

Jump to solution

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:

romanmensch_0-1603194429477.jpeg

Than we make the physical Table and now the Data is correct:

romanmensch_1-1603194429484.jpeg

 

And now we stand in line. We cannot write the data back.

romanmensch_2-1603194429491.jpeg

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

0 Kudos
mfranz
Shining star

Re: Date Format not correct - Business Importer - FlexNet Manager on Prem

Jump to solution

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

Softline Group is Europe's leading independent expert in Software Asset Management.

View solution in original post

romanmensch
Active participant

Re: Date Format not correct - Business Importer - FlexNet Manager on Prem

Jump to solution
Dear Markward,

We were able to solve it using the physical table. We have now solved it with a workaround. The correct data from the DB (physical Table) is copied into Excel and imported again. Not so nice but it worked for us. Many many thanks.