SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 BULK INSERT, what am I doing wrong?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/29/2006 :  17:28:37  Show Profile  Reply with Quote
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

Edited by - nomadsoul on 11/29/2006 17:36:37

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 11/29/2006 :  18:30:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Check that the destination table only has two column, and both these columns are at least varchar(5).


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/29/2006 :  18:35:37  Show Profile  Reply with Quote
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

Edited by - nomadsoul on 11/29/2006 18:38:02
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 11/29/2006 :  18:39:52  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 11/29/2006 :  18:42:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 11/29/2006 :  18:49:39  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/29/2006 :  18:58:31  Show Profile  Reply with Quote
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
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/29/2006 :  19:00:42  Show Profile  Reply with Quote
========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
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/29/2006 :  19:01:38  Show Profile  Reply with Quote
========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
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/29/2006 :  19:02:23  Show Profile  Reply with Quote
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

Edited by - nomadsoul on 11/29/2006 19:11:48
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/29/2006 :  19:07:18  Show Profile  Reply with Quote
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

Edited by - nomadsoul on 11/29/2006 19:18:14
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/29/2006 :  19:10:49  Show Profile  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 11/29/2006 :  19:14:07  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Edited by - jezemine on 11/29/2006 19:20:10
Go to Top of Page

nomadsoul
Yak Posting Veteran

USA
89 Posts

Posted - 11/29/2006 :  22:32:01  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000