Bulk Insert XLS file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Bulk Insert XLS file

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2008-09-25 : 14:34:21

Hi,

Can someone tell me what I am doing wrong. It seems like the rowterminator isn't good for the item 12.

I received this error:
Msg 4866, Level 17, State 66, Procedure ExcelUpload, Line 13
Bulk Insert fails. Column is too long in the data file for row 1, column 12. Make sure the field terminator and row terminator are specified correctly.
~~This is my format file:~~

8.0
12
1 SQLCHAR 0 50 "" 1 [Clli_Code] SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "" 2 [Location_Name] SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "" 3 [Node_Address] SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "" 4 [Card_Notes] SQL_Latin1_General_CP1_CI_AS
5 SQLNUMERIC 0 4 "" 5 [Portaddr_Seq] SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 10 "" 6 [Rate_Code] SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 50 "" 7 [Circuit_Node_Status] SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 50 "" 8 [PA_Condition_Code] SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 250 "" 9 [PA_Comments] SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 11 "" 10 [A_Clli_Code] SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 11 "" 11 [Z_Clli_Code] SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 10 "\r\n" 12 [Service_type_Code] SQL_Latin1_General_CP1_CI_AS

And this is my stored procedure:
BULK
INSERT tbl_BW_RawData
FROM '\\DeviceName\Sharedfolder\Asset.xls'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FORMATFILE = '\\DeviceName\Sharedfolder\Asset.fmt',
KEEPNULLS
)

Thanks!

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2008-09-26 : 00:27:47

does your target table structure have a column data width wide enough to support the max width of the source data?

You can do anything at www.zombo.com

infodemers
Posting Yak Master

183 Posts

Posted - 2008-09-26 : 07:41:56

Hello clarkbaker1964,

Thanks for taking time to help solve my problem.
Yes, the target table width for that column is 250 (I didn't take any chance). When I look at my excel file(xls), the longest the data can be is 2 characters only. Can that has something to do with the way and terminate it. This is not a CSV file but a XLS file. So, instead of "\r\n", can that be something else?

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources