| 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! |
|
|
|
|
Bulk Insert - Excluding SQL Fields