Problem running Bulk Insert to import a text file

Question

Problem running Bulk Insert to import a text file

Lester Vincent

Ten Centuries

Points: 1227
More actions
January 7, 2006 at 11:19 pm

#364630

Hi people.   I hope you can help me overcome this challenge with a 'dynamic' BULK INSERT' stored procedure.   The procedure parses OK but aborts when I try to run it in Query Analyser.   Details, including the stored proc, an extract from the text file and the (very unhelpful) error message are as follows: -
As my programme has to import 60-100 of these from emailed Word docs each day, I'm in a fix until this can be solved.     An/all help is greatly appreciated.
Now the details: -
EXTRACTS FROM IMPORT SOURCE FILE, CONVERTED TO A TEXT FILE FROM A MS WORD DOCUMENT.
Note: (1) VBA CODE USED TO CREATE A NUMBERED COLUMN, FOR LINE NUMBERS, AND ALL TEXT IN EACH LINE IS IN THE SECOND COLUMN.
      (2) Text length in second column can vary from NULL to 255 ANSI characters.
      (3) Apart from this, each source file follows the same kind of format.
FIELD SEPARATOR IS TAB CHARACTER: -
87 Access=BY APPOINTMENT ONLY
88 SiteInstructions=
89 Location=FRONT
90 WorkInstructions=
91 PropertyStatus=OCCUPIED
92 OCHProperty=N
93 WorkContainsDismods=N
94 Programme=NA
95 RoofType=CTE - CONCRETE TILES
96 HotWaterSystem=ESE - ELECTRIC STORAGE EXTERNAL
97 StoveType=E - ELECTRIC
98 BuildingMaterial=B - BRICK
99 TypeOfWindows=T - TIMBER
100 RoomHeaterType=NA
101 DateFirstTenanted=26-DEC-1994
102 Visit=NA
103 WorkProgram=REP
104 Field4=NA
105 Field5=NA
106 Field6=NA
107 Field7=NA
108 Field8=NA
109 Field9=NA
110 Field10=NA
111 JobLocValids=AIRL BALC BATH BED1 BED2 BED3 BED4 BED5 BED6 BED7 BED8 CAEXT CAINT CALDY CARP COMR DIN DWEL ENS ENT FAM FEE FRONT GAR GARBC HALL KIT LDY LGE LHS LIFT METER REAR RHS ROOF SHED STOR STW UNKN WC
112 REPDOOR   ~GNC~     1~FRONT~Inspect and Repair door~Front door - jamb broken 2 months    ago - Difficulty latching door
113 -----------------------------------------------------------
PURPOSE OF EXERCISE: -
TO IMPORT WORD DOCUMENT INTO A SQL SERVER TABLE AND FROM THERE,
CREATE ROWS IN SEVERAL SQLS TABLES.
STRATEGY:-
1.   CREATE THE SUBJECT TEXT FILE, WITH LINE NUMBERS AND TAB SEPARATORS
2.   USING AN ADO COMMAND IN CLIENT WORKSTATION, CREATE PARAMETER OF SOURCE-FILE NAME AND PATH AND CALL STORED PROC WITH DYNAMIC 'BULK INSERT' STRING
3.   STORED PROC PARSES OK, BUT NOTHING HAPPENS TO IMPORT THE DATA.
HERE IS THE 'DYNAMIC' STORED PROCEDURE: -
-----------------------------------------
CREATE PROCEDURE proc_TransferToTable_Server
(@DataFile varchar(255)='', @Return int =NULL output)
/*Uses @DataFile parameter as data source for BULK INSERT command, which pulls data into "tbl_OrImports_Temp" table.
This table is emptied before each Order import.   The table has only two fields, LineNo and Text and the field separator is TAB
and assigns @@ROWCOUNT value to @Rows variable.
*/
AS
DECLARE @Query varchar(255)
SET @Query='BULK INSERT dbo.tbl_OrdImports_Temp FROM '+CHAR(39)+ @DataFile+CHAR(39)
SET @Query=@Query + ' WITH (FIELDTERMINATOR='+CHAR(39)+'\t'+CHAR(39)+','
SET @Query=@Query + ' ROWTERMINATOR='+CHAR(39)+'\n'+CHAR(39)+','
SET @Query=@Query + ' CODEPAGE='+CHAR(39)+'RAW'+CHAR(39)+')'
PRINT @Query --Prints full text of @Query
EXEC (@Query)
RETURN @Return
GO
-----------------------------------------
WHEN TESTED IN QUERY ANALYSER, THE SPROC IS ABORTED, WITH THE FOLLOWING RESPONSE: -
(PRINTED TEXT OF THE BULK INSERT STATEMENT): -
"BULK INSERT dbo.tbl_OrdImports_Temp FROM 'C:\NewOrdersForTesting\4186584_1-104870880_Numbered.TXT' WITH (FIELDTERMINATOR='\t', ROWTERMINATOR='\n')")

(RESPONSE):
" Server: Msg 4832, Level 16, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.
@RETURN_VALUE = 0 "
I have tried BULK INSERT without any arguments, ie using only the defaults.
I have then added the Fieldterminator and RowTerminator arguments.
I have then added the CodePage argument, using values, 'ACP' and 'RAW'.
I have also tried it, adding the "KEEPNULLS argument.
None of the above arguments makes a difference to the result.   I have been using the same file format in an MS Access app and importing, using the "TransferText" method without any trouble for the past 2 years.   Obviously, SQLS found something it doesn't like in there.

Regards, (looking forward to getting your good advice)

Lester Vincent
Sydney

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Lester Vincent Ten Centuries Points: 1227 More actions · Answer 1

Thanks to the guys that tried to help me with this one. I now have it working a treat! I had it running from within Query Analyser, but it would not run from an ADO Command object in my MSACCESS front-end app.

The solution was that I had to change the method of passing an input parameter (the path and filename) to the stored proc. Instead of using the Parameters.Append method, I used the .Parameters..Refresh method first, then passed the parameter value. Some of you other Access gurus will understand what this means. Hope this will help some others in the same position. The VBA code is available if anyone would like to have it.

Regards,

Lester Vincent