Bulk Insert - Excluding SQL Fields

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 - Excluding SQL Fields

Author

Topic

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2009-10-02 : 09:33:22

Here is my stored proceudre:


CREATE PROCEDURE AlliedInventoryDelete AS

TRUNCATE TABLE AlliedInventorymaster
GO

BULK
INSERT AlliedInventoryMaster
FROM '\\CDREXCH1\shared\ScannedImages\dee-elect-part-file.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
GO

It is failing because it is trying to put data into my field called ImportDateTime, which is just a field with an autodate attached to it.

So, I wish to exclude placing any data into this field with the above insert...any ideas on how to accomplish this?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-10-02 : 10:06:46

Look up "bcp format files" in Books Online.

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2009-10-02 : 11:07:53

I have been playing around with my .fmt file, and have come up with a dead end. I receive the following error:

error 4839: cannot perform bulk insert. Invalid collation name for soucre column 1 in format file xxxx

Here is the stored procedure:

CREATE PROCEDURE AlliedInventoryDelete AS

TRUNCATE TABLE AlliedInventorymaster
GO

BULK
INSERT AlliedInventoryMaster
FROM '\\CDREXCH1\shared\ScannedImages\dee-elect-part-file.txt'
WITH
(
FORMATFILE = '\\CDREXCH1\shared\ScannedImages\AlliedInventoryMaster.fmt'
)
GO

Here is the .fmt file:

8.0
12
1 SQLCHAR 0 255 "\t" 1 Allied Stk #
2 SQLCHAR 0 255 "\t" 2 Mfr Name
3 SQLCHAR 0 255 "\t" 3 Mfr Part #
4 SQLCHAR 0 255 "\t" 4 Product Desc
5 SQLFLT8 0 8 "\t" 5 Price
6 SQLCHAR 0 255 "\t" 6 UOM and Qty
7 SQLINT 0 4 "\t" 7 Lead Days
8 SQLINT 0 4 "\t" 8 Sells in mults of
9 SQLINT 0 4 "\t" 9 Available Stock
10 SQLINT 0 4 "\t" 10 Min Qty
11 SQLINT 0 4 "\t" 11 Catalog Page
12 SQLCHAR 0 255 "\r\n" 12 URL

I'm using sql 2000.

here are the column types:
1: varchar 255
2: varchar 255
3: varchar 255
4: varchar 255
5: float 8
6: varchar 255
7: int 4
8: int 4
9: int 4
10: int 4
11: varchar 255
12: int 4

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-10-02 : 11:17:08

2 things:

1. If you're importing plain text files, all column types in your format file must be SQLCHAR. The other datatypes can only be used with native format files.
2. The best way to create a format file is to do a bcp out of the table, but do not specify -c or -n. You will get an interactive prompt for each column. Accept the defaults, except be sure to change any data types to CHAR if they don't default to it. This will include the missing collations that are causing the error. You can then edit the format file in notepad to move or skip columns for your import.

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2009-10-02 : 14:10:59

Unfortunately, I still get the same error with the following file:

8.0
12
1 SQLCHAR 0 255 "\t" 1 Allied Stk #
2 SQLCHAR 0 255 "\t" 2 Mfr Name
3 SQLCHAR 0 255 "\t" 3 Mfr Part #
4 SQLCHAR 0 255 "\t" 4 Product Desc
5 SQLCHAR 0 255 "\t" 5 Price
6 SQLCHAR 0 255 "\t" 6 UOM and Qty
7 SQLCHAR 0 255 "\t" 7 Lead Days
8 SQLCHAR 0 255 "\t" 8 Sells in mults of
9 SQLCHAR 0 255 "\t" 9 Available Stock
10 SQLCHAR 0 255 "\t" 10 Min Qty
11 SQLCHAR 0 255 "\t" 11 Catalog Page
12 SQLCHAR 0 255 "\r\n" 12 URL

how do i create a bcp directly from the table?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-10-02 : 15:32:41

bcp "myDatabase..AlliedInventoryMaster" out myFile.txt -Sserver -T

Run that, you'll be prompted for column information. Make sure you enter "char" for data types if they default to something else. All other prompt defaults are fine. When you're finished you'll be prompted to save the format file. Pick a new name for it and then open that file in Notepad. You'll see collation information added after the column names.

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2009-10-05 : 09:45:15

I am doing somethign wrong because at a commant prompt, it keeps saying I am using the incorrect format.

Our server name is cdrsrv8...how do I substitute that in there?

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2009-10-05 : 13:10:25

Same error with the following:

8.0
12
1 SQLCHAR 0 255 "\t" 1 Allied Stk # ""
2 SQLCHAR 0 255 "\t" 2 Mfr Name ""
3 SQLCHAR 0 255 "\t" 3 Mfr Part # ""
4 SQLCHAR 0 255 "\t" 4 Product Desc ""
5 SQLCHAR 0 255 "\t" 5 Price ""
6 SQLCHAR 0 255 "\t" 6 UOM and Qty ""
7 SQLCHAR 0 255 "\t" 7 Lead Days ""
8 SQLCHAR 0 255 "\t" 8 Sells in mults of ""
9 SQLCHAR 0 255 "\t" 9 Available Stock ""
10 SQLCHAR 0 255 "\t" 10 Min Qty ""
11 SQLCHAR 0 255 "\t" 11 Catalog Page ""
12 SQLCHAR 0 255 "\r" 12 URL ""

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2009-10-05 : 13:15:36

Oh, and the above file was created using the automated file creation, and then modified as needed.

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2009-10-05 : 13:22:50

And, here is the first couple lines of the data that would be imported...first is file headers, rest is data.

Allied Stk # Mfr Name Mfr Part # Product Desc Price UOM and Qty Lead Days Sells in mults of Available Stock Min Qty Catalog Page URL
201-0001 CARLTON BATES LCOMP 3RV1021-1CA10 187.390 EA/1 21 1 4 1 0 http://www.alliedelec.com/Search/SearchResults.asp?SearchQuery=2010001
201-7125 CARLTON BATES LCOMP PFC375-4002-F Return 630.480 EA/1 21 1 8 1 -1 http://www.alliedelec.com/Search/SearchResults.asp?SearchQuery=2017125

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2009-10-05 : 15:01:35

I have narrowed the problem down to the field names that contain spaces in them, aka, Allied Stk #, will cause this error; however, the field URL does not. Any ideas?

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2009-10-05 : 17:06:18

From reading this:

http://msdn.microsoft.com/en-us/library/ms191479.aspx

I changed 'Allied Stk #' to just 'Allied' - basically the name seems to have very little to do with it, and it's the server column order field which seems to be the important one.

Everything works now, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources