Forum Discussion
Helper IIIImport Excel to Fabric Warehouse error
An error occurs while attempting to insert data from an Excel file in a Blob into a Fabric Warehouse using a Copy Job and pipeline.
I would like to know the cause.
<Test Details>
1. Columns with a VARCHAR data type appear to work without issues, but errors occur for columns of other types, such as BigInt.
2. When all column types in the test table are changed to VARCHAR, the data is loaded successfully.
<Error Message>
ErrorCode=DWCopyCommandOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message='DataWarehouse' Copy Command operation failed with error 'Column 'project_id' of type 'BIGINT' is not compatible with external data type 'Parquet physical type: BYTE_ARRAY, logical type: UTF8', please try with 'VARCHAR(8000)'. Underlying data description: file 'https://olssgqbvvo9frj3mtbl3i.dfs.core.windows.net/a45fc635-a044-4a86-a280-ea6d2bfae79b/0a2a9eb1-0b4d-4447-a 807-c3c75f848e87/Staging/3bcbc5e3-17ed-47e9-898e-c4563ce394af/MSSQLImportCommand/jira_test_data.xlsx.parquet'.
Statement ID: {DBE1590C-EFF3-46A5-B4E7-D7833347F118} | Query hash:
Hi ericjo
1. You mentioned in your response that the file is converted to Parquet. Even if the file is read from Excel, is it converted to Parquet before loading?
Answer: Yes, in fabric pipelines, when you will use Copy activity to shift data frome xcel to warehouse the service stage the file in parquet format. so this staging is used for optimizing the bulk load mechnaisam of warehouse. that's why in your error clearnly mentiuoned .xlsx.parquet so excel file was tranforemd into parquet.
2. When I tested this on a table with the same structure in Lakehouse, it loaded successfully even though it contained numeric columns. What could be the difference?
Answer: Here lakehouse and warehouse handles schemas differently. In Lakehouse tables is loading in the form of delta tables. so this delat tables can still accpet them inot numeric columns, why bcz Spark ingestion engine performs implicit casting. While coming to Warehouse, it is using SQL synapse engine that's why here schema is very strict. here copy command looking for parquet physuical type to match the SQL column type. that's the resons here you're able to see compatabilty error.
Hope this clarifies you.
Thank you!!
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
4 Replies
- suparnababu8
Super User
Hello ericjo
I thin it's a data type mismathc issue between your soruce (Excel Blob) and destination (warehouse) . why it's working with VARCHAR means ., string UTF8 clarly maps without conversions.
why BIGINTv fails, during the excel file loaded into warehouse, which means to say converting it to parquet thoise value still same as UTF8 string. Therfore, warehouse can not interpret automatically UTF8 string as BIGINT.
In your excel you can save your numerica columns(projectid) as numeric or number datatype. the you can try to do it will work. if nto work after imprting the data into ware house u can use CAST method.
Hope this helps you
Thank you
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
ericjoThank you for your reply. I have a question regarding additional testing, so I apologize for the inconvenience, but I am asking again.
1. You mentioned in your response that the file is converted to Parquet. Even if the file is read from Excel, is it converted to Parquet before loading?
2. When I tested this on a table with the same structure in Lakehouse, it loaded successfully even though it contained numeric columns. What could be the difference?
- suparnababu8
Hi ericjo
1. You mentioned in your response that the file is converted to Parquet. Even if the file is read from Excel, is it converted to Parquet before loading?
Answer: Yes, in fabric pipelines, when you will use Copy activity to shift data frome xcel to warehouse the service stage the file in parquet format. so this staging is used for optimizing the bulk load mechnaisam of warehouse. that's why in your error clearnly mentiuoned .xlsx.parquet so excel file was tranforemd into parquet.
2. When I tested this on a table with the same structure in Lakehouse, it loaded successfully even though it contained numeric columns. What could be the difference?
Answer: Here lakehouse and warehouse handles schemas differently. In Lakehouse tables is loading in the form of delta tables. so this delat tables can still accpet them inot numeric columns, why bcz Spark ingestion engine performs implicit casting. While coming to Warehouse, it is using SQL synapse engine that's why here schema is very strict. here copy command looking for parquet physuical type to match the SQL column type. that's the resons here you're able to see compatabilty error.
Hope this clarifies you.
Thank you!!
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
v-pnaroju-msftCommunity Support
Thankyou, suparnababu8 for your response.
Hi ericjo,
We appreciate your inquiry through the Microsoft Fabric Community Forum.
We would like to inquire whether have you got the chance to check the solution provided by suparnababu8 to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.
Thank you.