BULK INSERT, what am I doing wrong? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Import/Export (DTS) and Replication (2000)

BULK INSERT, what am I doing wrong?

Author

Topic

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-29 : 17:28:37

Hi,
I made this PROC to run a bulk insert:

CREATE PROC bld_BULKINSERT
AS
BULK INSERT dbo.building FROM "c:\build.doc"
GO
EXEC bld_BULKINSERT

========and here are the first few records of the document I'm trying to insert:
MOR 103
MOR 114A
MOR 103
MOR 103
EN 105
EN 201
EN 104
EN 104
EN 103
====
The table(Build)has 2 columns: Building and RoomNum both Varchar(5)
The .doc flatfile is 36 pages in 1NF as you can see. I'd like to get the table loaded before I do 2 and 3NF
Here are the errors:
Msg 4866, Level 16, State 1, Procedure bld_BULKINSERT, Line 3
The bulk load failed. The column is too long in the data file for row 1,
column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Procedure bld_BULKINSERT, Line 3
The OLE DB provider "BULK" for linked server "(null)" reported an error.
The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Procedure bld_BULKINSERT, Line 3
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

=====
can I do any normalizing in .doc or excel to shrink this file? and should I be using a text file? The data in the .doc file is in a two column table format.
Thanks for any insight

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 18:30:10

Check that the destination table only has two column, and both these columns are at least varchar(5).

Peter Larsson
Helsingborg, Sweden

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-29 : 18:35:37

Peso,
Did that. I even gave them both (MAX) and other various sizes. And even shorted the file to one page to test it. Same errors

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-29 : 18:39:52

I have never heard of doing a bulk insert from a word doc. .doc is a binary format understood only by office. you need to use a delimited txt file, like csv.

easiest way for you to produce this probably is to open up your doc in excel and save as csv. Then change your statement to:

BULK INSERT dbo.building FROM 'c:\build.csv' WITH (FIELDTERMINATOR=',')

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-29 : 18:42:19

I don't think the file extension has impact on the import result.

Nomadsoul, please post some sample data here (first 10 rows or so) from your file to import. Also explain the delimiters, both column delimiters and row delimiters.

Peter Larsson
Helsingborg, Sweden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-29 : 18:49:39

true that the file extension itself doesn't matter. I was inferring from the extension that the file is a word doc, not a text file. and sql server will certainly barf on a generic word doc - it has all manner of binary junk in it that would not be expected.

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-29 : 18:58:31

I'm getting an incorrect syntax error with:
CREATE PROC us_BULKINSERT
WITH (FIELDTERMINATOR = ',')
AS
BULK INSERT dbo.building FROM "c:\build.csv"

Msg 102, Level 15, State 1, Procedure us_BULKINSERT, Line 2
Incorrect syntax near '('.

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-29 : 19:00:42

========and here are the first few records of the document I'm trying to insert:
MOR 103
MOR 114A
MOR 103
MOR 103
EN 105
EN 201
EN 104
EN 104
EN 103

I'm using tab delimited in excel

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-29 : 19:01:38

========and here are the first few records of the document I'm trying to insert:
MOR 103
MOR 114A
MOR 103
MOR 103
EN 105
EN 201
EN 104
EN 104
EN 103

I'm using tab delimited in excel

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-29 : 19:02:23

I'ts the comma in the WHERE? not sure how the delimiting thing works with regards to BI

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-29 : 19:07:18

Here is one Sample from the book I'm using that works perfectly and I'm trying to model my building BI after it:

CREATE PROC usp_BULKINSERT
AS
BULK INSERT Classes FROM "c:\NewClasses.txt"
GO

EXEC usp_BULKINSERT
GO

SELECT * FROM Classes
GO

And here is what the Classes.txt file looks like:
2 ---- Learning Visual Basic for DBAs
3 ---- Learning Visual Web Developer Express for DBAs

===
Im using the dashes to separate the fiels(here, not in the .txt file)
You can see it's a plain ole .txt file and it works perfectly only exception; it's in a different db (same server)

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-29 : 19:10:49

Please don't think me rude but I have to leave for a couple hours and when I come back I will check replies first thing. Please continue replies if you have them.
I appreciate everyone taking time to answer me.

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-11-29 : 19:14:07

I'm getting an incorrect syntax error with:


CREATE PROC us_BULKINSERT
WITH (FIELDTERMINATOR = ',')
AS
BULK INSERT dbo.building FROM "c:\build.csv"

if your file is tab-delimited, you don't need the WITH clause, as tabs are the default for bulk insert. also, you put the WITH clause in the wrong place (it's part of the bulk insert statement!), hence your syntax error. You might want to read up on bulk insert: http://msdn2.microsoft.com/en-us/library/ms188365.aspx

EDIT: one other thing - can you open up your file in notepad and it displays ok? if not, it's a binary file and you need to save it as csv in excel.

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-29 : 22:32:01

It finally worked. It needed the ROWTERMINATOR '\n'
Since like you said it didn't need the FIELDTERMINATOR so I left it out.
So for anyones benefit here's what I did:

BULK INSERT dbo.building FROM 'c:\build.txt'
WITH (ROWTERMINATOR = '\n')

Was all it needed aparently
Thanks again

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources