Getting error while bulk insertion

Question

I am getting error when I am trying to do bulk insert:

BULK INSERT #tbl_InterCompanyUploadDetail_Staging 
FROM '\\SVRP03546008461D\QA\UploadTemplatewithvalidation.xlsx'
 WITH (FIRSTROW = 6, FIELDTERMINATOR ='\t', ROWTERMINATOR ='\\n' )

Error that I am getting is :

Bulk load data conversion error (truncation) for row 6, column 2 (Oracle Company Code).

The column in Excel has data as 470 and in database column is varchar (10). So what could be the reason for the error.

Answer 1

The Issue

BULK INSERT may not work with xlsx files, try converting the .xlsx file to .csv file to achieve this (using BULK INSERT)

1st Solution - using OPENROWSET

Try using OPENROWSET with Microsoft.ACE.OLEDB.12.0 provider:

Insert into <rawdatatable> 
select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
                         'Excel 12.0;Database=D:\SSIS\FileToLoad.xlsx;HDR=YES', 
                         'SELECT * FROM [Sheet1$]')

OR

SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=D:\Desktop\Data.xlsx', [Sheet1$]);

2nd Solution - using OPENDATASOURCE

SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=D:\Desktop\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$];

References

• Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server
• Import data from Excel to SQL Server or Azure SQL Database
• How to Bulk Insert from XLSX file extension?