Bulk Insert Problem - 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 2000 Forums

Import/Export (DTS) and Replication (2000)

Bulk Insert Problem

Author

Topic

snake9284
Starting Member

10 Posts

Posted - 2007-11-21 : 14:23:46

I'm trying to use the bulk insert option to insert data from a flat file. Here is an example of the data in that file:

G 00003,00000000000266,+0000000002+0000000000+0000000198+00000001980000000000000002266

I originally tried to create a format file as seen here:

8.0
11
1 SQLCHAR 0 0 "" 1 SaleDate ""
2 SQLCHAR 0 1 "\t" 2 op_co SQL_Latin1_General_Cp1_Cl_AS
3 SQLCHAR 0 5 "," 3 Store_Num ""
4 SQLCHAR 0 14 "," 4 UPC SQL_Latin1_General_Cp1_Cl_AS
5 SQLCHAR 0 10 "+" 5 Units_Sold ""
6 SQLCHAR 0 10 "+" 6 Wgt_sold ""
7 SQLCHAR 0 10 "+" 7 Rtl_Sold ""
8 SQLCHAR 0 10 "" 8 Net_Sold ""
9 SQLCHAR 0 19 "" 9 Vendor SQL_Latin1_General_Cp1_Cl_AS
10 SQLCHAR 0 0 "" 10 Item_Code SQL_Latin1_General_Cp1_Cl_AS
11 SQLCHAR 0 0 "\r\n" 11 UPC_Key SQL_Latin1_General_Cp1_Cl_AS

going into this table

CREATE TABLE [dbo].[Load_Stage] (
[SaleDate] [smalldatetime] NULL ,
[op_co] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Store_Num] [int] NULL ,
[UPC] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Units_Sold] [int] NULL ,
[Wgt_Sold] [int] NULL ,
[Rtl_Sold] [int] NULL ,
[Net_Sold] [int] NULL ,
[Vendor] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Item_Code] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPC_Key] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Using this bulk insert statement

bulk insert [Test_db].[dbo].[Load_Stage]
from 'sourcefile'
with ( FORMATFILE = 'formatfile')

receiving error

Server: Msg 4882, Level 16, State 1, Line 1
Could not bulk insert. Prefix length, field length, or terminator required for source column 1 in format file

snake9284
Starting Member

10 Posts

Posted - 2007-11-21 : 14:29:16

Second thing I tried was to create a raw table

CREATE TABLE [dbo].[Load_Raw] (
[Store] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Data] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Use this format file

7.0
3
1 SQLCHAR 0 7 "," 1 store
2 SQLCHAR 0 14 "," 2 UPC
3 SQLCHAR 0 59 "\r\n" 3 Units_Sold

and received this error:

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.

snake9284
Starting Member

10 Posts

Posted - 2007-11-21 : 15:33:27

As an update, I was able to get the second method to work after I opened the file and went to the very end of it. There was a line at the end of the file (row 1723623) that did not match the rest of the file format. Is there any way to tell the system to stop when the first character in the row is an 'H' for example? Or would it be best to bring the entire file in one row at a time and then break out the columns based on position into a stage table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources