Bulk upload from Excel in SQL Server 2008

Question

I'm trying to bulk upload from an Excel file using the following query:

BULK INSERT TempRevenueForecast 
   FROM 'E:\RevenueTracker\Demo\UserTemplate.xls' 
   WITH (FORMATFILE = 'E:\RevenueTracker\Demo\TRF.FMT');
GO

But, I'm getting this error:

Bulk load data conversion error (truncation) for row 1, column 2

How can I solve it?

BULK INSERT will not load an Excel file. You either need to save as a text file first or use OPENROWSET, much like these examples (the first ones that appeared in Google when I searched) social.msdn.microsoft.com/Forums/sqlserver/en-US/… — Nick.Mc, Commented Jul 21, 2014 at 11:29

Stefan Steiger · Accepted Answer · 2014-07-21 11:48:07Z

If you open the table TempRevenueForecast in edit mode (right-click-table - edit top 200 rows) in SSMS, you can just copy-paste the excel data over.

That's a very handy feature of SSMS.

Otherwise, use OpenRowSet:

INSERT INTO [TempRevenueForecast ] ([Column1], [Column2], [Column3], [Column4])

SELECT A.[Column1], A.[Column2], A.[Column3], A.[Column4]
FROM OPENROWSET 
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\RevenueTracker\Demo\UserTemplate.xls;HDR=YES', 'select * from [Sheet1$]') AS A;

For that to work, the Microsoft Access Database Engine (ACE) components must be installed.
http://www.microsoft.com/en-us/download/details.aspx?id=23734
or
http://www.microsoft.com/en-us/download/details.aspx?id=13255

Collectives™ on Stack Overflow

Bulk upload from Excel in SQL Server 2008

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related