| Author |
 Topic  |
|
|
nomadsoul
Yak Posting Veteran
USA
89 Posts |
 Posted - 11/29/2006 : 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 |
Edited by - nomadsoul on 11/29/2006 17:36:37
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/29/2006 : 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
USA
89 Posts |
Posted - 11/29/2006 : 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 |
Edited by - nomadsoul on 11/29/2006 18:38:02 |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 11/29/2006 : 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
Sweden
29138 Posts |
Posted - 11/29/2006 : 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
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 11/29/2006 : 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
USA
89 Posts |
Posted - 11/29/2006 : 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
USA
89 Posts |
Posted - 11/29/2006 : 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
USA
89 Posts |
Posted - 11/29/2006 : 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
USA
89 Posts |
Posted - 11/29/2006 : 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 |
Edited by - nomadsoul on 11/29/2006 19:11:48 |
|
|
|
nomadsoul
Yak Posting Veteran
USA
89 Posts |
Posted - 11/29/2006 : 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 |
Edited by - nomadsoul on 11/29/2006 19:18:14 |
|
|
|
nomadsoul
Yak Posting Veteran
USA
89 Posts |
Posted - 11/29/2006 : 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
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 11/29/2006 : 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 |
Edited by - jezemine on 11/29/2006 19:20:10 |
|
|
|
nomadsoul
Yak Posting Veteran
USA
89 Posts |
Posted - 11/29/2006 : 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 |
|
|
| |
Topic |
|
BULK INSERT, what am I doing wrong?
