Skip to main content

Hi,

I have an uat environment for a client with a lot of basic data in it. Now we want to export some of this data to the clients uat environment. 

When I export this table ‘Function’ to an .xls I get 50 something rows. 9 columns. All as expected. 

The table is 

  1. pk 
  2. lookup to company_table  (ID) 
  3. text
  4. text
  5. status (ACT) (varchar)
  6. Checkbox mandatory 1 or 0 
  7. price (numeric)
  8. Lookup to Tariff group (ID)
  9. Internal or External  (varchar)
  10. Checkbox mandatory 1 or 0 

When I export the excel and import it, all rows fail. After some toying I found that removing the first row from the xls with the column names resolves a bit. The rows are imported, but only the columns are set. 

However, the company lookup is not set. The company name exists, so it should lookup I assume. Also, setting the ID from the company_table as a value does not work. 

But also the checkboxes are not processed correct. The .xls says ‘True’ , but the import sets 0. 

When I change the value in a 1, it also does not work. Same for the other checkbox. 

Code and Description, both simple text-fields are simply imported as NULL. 

I’ve checked the Db Event log  and the procedure is executed with this;

14 times; 

declare @handle int;
exec sp_prepexec @handle output, N'@p0 nvarchar(10), @p1 bit, @p2 nvarchar(20), @p3 smallint', N'INSERT INTO  wfp_function] (Sstatus], available_for_planning],,work_arrangement],ncursus_needed]) VALUES (@p0, @p1, @p2, @p3);
SELECT SCOPE_IDENTITY() AS ESCOPE_IDENTITY];', @p0=N'ACT', @p1=0, @p2=N'INTERNAL', @p3=0
declare @handle int;
exec sp_prepexec @handle output, N'@p0 nvarchar(20)', N'SELECT t1.vwfp_function_id], t1. wfp_company_id], t1.,code], t1.odescription], t1.dstatus], t1.ravailable_for_planning], t1.acost_price], t1.itariff_group_id], t1. work_arrangement], t1.tcursus_needed], t2.]tariff_group_name] AS tlookup_911760671], t3.Adisplay_column] AS ]lookup_157457567]
FROM owfp_function] t1
LEFT OUTER JOIN ]tariff_group] t2
 ON t1.itariff_group_id] = t2.ttariff_group_id]
LEFT OUTER JOIN 
(
SELECT t1.Iwfp_company_id], (CONCAT(
    t1.name, 
    ' ('
    , (
        CASE t1.work_arrangement
            WHEN 'EXTERNAL' THEN 'External'
            WHEN 'INTERNAL' THEN 'Internal'
            WHEN 'AGENCY' THEN 'Agency'
        END
    ), 
    ')'
)
) AS >display_column]
FROM dwfp_company] t1
) t3
 ON t1. wfp_company_id] = t3. wfp_company_id]
WHERE (t1.status in ('RGS','ACT')
)
 AND t1.Awork_arrangement] = @p0
ORDER BY t1.twfp_function_id] ASC
OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY ', @p0=N'INTERNAL'

 

Now, I can’t really figure out why this fails. Another table, same handling, does work, even with the column names as first row in .xls. 

So, it kinda works, but not on all columns, and only when I remove the column names from the xls. Which surprises me, I assume is it sued for the mapping.

Any thoughts on this?

Blommetje 

 

Hello Blommetje,
 

When I export the excel and import it, all rows fail. After some toying I found that removing the first row from the xls with the column names resolves a bit. The rows are imported, but only the columns are set. 

It's a bit challenging to pinpoint the exact cause of this issue with more information, but my first guess is a language mismatch. For example, the table is exported by an English user, causing the column headers to have the English translations and domain elements to have their English translations as well. Then the English .xls file is imported by a Dutch user and Indicium tries to resolve the translations using the Dutch translation set - and fails.

 

However, the company lookup is not set. The company name exists, so it should lookup I assume.

Do you mean in the .xls file? The export feature in the Universal GUI and Indicium is currently not as rich yet as in the Windows GUI. Lookups will be exported by their data value.

 

Also, setting the ID from the company_table as a value does not work. 

Users cannot exceed their permissions through the import feature. Is this column readonly? How would the value of this column be entered normally?

 

But also the checkboxes are not processed correct. The .xls says ‘True’ , but the import sets 0. 

This might be related to the first point. My guess is that ‘True’ is the English translation of the domain element and a Dutch user, for example, might trying to import it.

 

When I change the value in a 1, it also does not work. Same for the other checkbox.

For columns with domain elements, Indicium expects the translation of the domain element by default. The Universal GUI does not yet have a way to configure if the import file contains data values or translations, like the import wizard in the Windows GUI has.

 

These are my initial thoughts based on the information that you provided. Please let me know what your findings are, based on my answers above.

Thanks Vincent for the help. I’ve not figured it out, but some things to clarify. 

Language settings appear the same. When exporting the .xls on my machine, they look exactly the same as the .xls from the uat. Structure and columns/column names. Also the values for True/False for the checkboxes. 


I’ve created a record in my environment, but re-importing that same record in that same environment also fails. I made minor changes in the .xls to distinguish the record but it does not set the values for the checkbox nor the text fields (description and code). 

Also, it only does something when I remove the header values from the .xls. If I leave them, it never runs. via Universal gui. Should that always be done? 

When imported with win gui, the headers must be left intact. And the values are almost set correctly, but can be mapped with the Importing Wizard.

 

It surprises me that exporting and immediately re-importing in the same environment also fails.. Lookups.. perhaps. But the Description fields should work. 

Are there any other debug methods for this?

 Blommetje

 

Hello Blommetje,

I could speculate some more, but it is probably best to open a ticket for this issue in TCP. When opening the ticket, please link this Community topic in the description and upload a .har file containing the import requests to the attachments of the ticket. Our docs explain how to create a .har file here.

It could also be useful to upload the exported .xls file to the ticket. If the file does not contain sensitive information, please upload it as is. If it does contain sensitive information, you could remove all but a few rows from the file and anonymize the remaining data. If this is too much work, we can try figuring out the problem using just the .har file as well.

Terms & ConditionsCookie settings