Import/export xls un Universal gui

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] ([status],[available_for_planning],[work_arrangement],[cursus_needed]) VALUES (@p0, @p1, @p2, @p3);
SELECT SCOPE_IDENTITY() AS [SCOPE_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.[wfp_function_id], t1.[wfp_company_id], t1.[code], t1.[description], t1.[status], t1.[available_for_planning], t1.[cost_price], t1.[tariff_group_id], t1.[work_arrangement], t1.[cursus_needed], t2.[tariff_group_name] AS [lookup_911760671], t3.[display_column] AS [lookup_157457567]
FROM [wfp_function] t1
LEFT OUTER JOIN [tariff_group] t2
 ON t1.[tariff_group_id] = t2.[tariff_group_id]
LEFT OUTER JOIN 
(
SELECT t1.[wfp_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 [wfp_company] t1
) t3
 ON t1.[wfp_company_id] = t3.[wfp_company_id]
WHERE (t1.status in ('RGS','ACT')
)
 AND t1.[work_arrangement] = @p0
ORDER BY t1.[wfp_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