Data Truncation issue while importing excel from Azure Blob storage to Sql Server

Question

I'm trying to import the below excel file present in the azure blob storage into sql server

EXCEL File

Query

SELECT * 
    FROM OPENROWSET(
        BULK 'container/testfile.xlsx', 
        DATA_SOURCE = 'ExternalSrcImport',
        FORMATFILE='container/test.fmt', FORMATFILE_DATA_SOURCE = 'ExternalSrcImport',
        codepage = 1252,
        FIRSTROW = 1
        ) as data

Format file

10.0  
4  
1       SQLCHAR       0       7       "\t"     1     DepartmentID     ""  
2       SQLCHAR       0       100     "\t"     2     Name             SQL_Latin1_General_CP1_CI_AS  
3       SQLCHAR       0       100     "\t"     3     GroupName        SQL_Latin1_General_CP1_CI_AS  
4       SQLCHAR       0       24      "\r\n"   4     ModifiedDate     ""  

Illustration of Format File

when I execute the query, I'm getting the below error

Msg 4863, Level 16, State 1, Line 210 Bulk load data conversion error (truncation) for row 1, column 1 (DepartmentID).

looks like field terminator in the format file is not working, any ideas to import the file ?

Radioactivate

Seems You are providing Extra lenght's data than your column support. – Barnabas

@Barnabas No, I'm not. I have attached the excel i'm trying to upload, please check. – Rome

Hi what is your FORMATFILE_DATA_SOURCE = 'ExternalSrcImport' definition ? – Librettist

Hi i think you have to define relevant column definition in FORMATFILE_DATE_SOURCE (learn.microsoft.com/en-us/sql/relational-databases/…) Please share the container/test.fmt content – Librettist

@pascalsanchez Question has the format file.. – Rome

@Radioactivate yes it's "container/test.fmt " but you doesn't share the content no ? – Librettist

@Radioactivate can you upload your 'container/testfile.xlsx' and 'container/test.fmt' files ? – Librettist

@pascalsanchez buddy, content is present in the question. Check this imgur.com/a/KeKo7S3 – Rome

@Radioactivate yes it's the content off Excel file but i'm speak about you definition file (fmt) content file. Otherwise try to convert your Excel file to bcp or txt or csv , because excel (XLSX) have some special Ms char and encoding. – Librettist

@Radioactivate Does the same occurs if you save file as .xls format? – Caruncle

@Pரதீப், can you create a "staging" table where all columns have type NVARCHAR(4000) and load data there. Then by querying it, perhaps it becomes clear what value is the reason for that error message – Beaujolais

Answer 1

Your format file is representing import of a tab separated values file, but in the source path you are referring to an xslx file.
Xslx file is an ZIP archive of multiple XML files, bulk import will not be able to process it. To open it you need to use Microsoft Jet or ACE driver, you have some examples here: using-openrowset-to-read-excel. You will need to download file from blob storage to local disks before processing it. You can use SQL Agent or SSIS to download it.

Other option will be to save your data as CSV or tab separated file and load it directly from blob storage.

Hildagarde

Thanks for this, but we need to use BULK option to import files from blob storage. Only under BULK option we can mention DATA_SOURCE – Rome

To use BULK import you have to save your data as CSV, tab separated file or something similar supported. Please also explain why do you have to mention DATA_SOURCE - Jet and ACE have equivalent options. – Hildagarde

DATA_SOURCE is the only way to import files from Azure Blob storage. – Rome

There is many options available. From Microsoft doc: Users or client applications can access objects in Blob storage via HTTP/HTTPS, from anywhere in the world. Objects in Blob storage are accessible via the Azure Storage REST API, Azure PowerShell, Azure CLI, or an Azure Storage client library. Client libraries are available for a variety of languages, including .NET, Java, Node.js, Python, Go, PHP, and Ruby. – Hildagarde

I'm trying to do this in Sql Server. In Sql Server we can use OPENROWSET or BULK INSERT both requires DATA_SOURCE to import files from Blob storage. – Rome

Could you consider then CLR stored procedure? With OPENROWSET or BULK INSERT you will have to use different format than xslx. – Hildagarde