August 8, 2006 at 3:47 am
Hello,
Im attempting to set up a bulk insert which takes all the files in a specific folder and imports them into a table. Its the first time Ive attempted to do this and its not going too well at the moment. The table has 2 extra rows at the end so I have used
FORMATFILE = ''\\ws-72865\cee\FOLDER_NAME\gpcll.fmt''
within my stored procedure.
I then created the fmt file
8.0
40
1 SQLCHAR 0 255 "," 1 NO Latin1_General_CI_AS
2 SQLCHAR 0 255 "," 2 FormerNO Latin1_General_CI_AS
3 SQLCHAR 0 255 "," 3 Surname Latin1_General_CI_AS
4 SQLCHAR 0 255 "," 4 Forename Latin1_General_CI_AS
5 SQLCHAR 0 255 "," 5 NameOrderIndicator Latin1_General_CI_AS
6 SQLCHAR 0 255 "," 6 MiddleNames Latin1_General_CI_AS
7 SQLCHAR 0 255 "," 7 DateOfBirth Latin1_General_CI_AS
8 SQLCHAR 0 255 "," 8 Gender Latin1_General_CI_AS
9 SQLCHAR 0 255 "," 9 FormerSurname Latin1_General_CI_AS
10 SQLCHAR 0 255 "," 10 PreferredForname Latin1_General_CI_AS
11 SQLCHAR 0 255 "," 11 PreferredSurname Latin1_General_CI_AS
12 SQLCHAR 0 255 "," 12 PreferredNameOrderIndicator Latin1_General_CI_AS
13 SQLCHAR 0 255 "," 13 ETH Latin1_General_CI_AS
14 SQLCHAR 0 255 "," 14 ETHSource Latin1_General_CI_AS
15 SQLCHAR 0 255 "," 15 T1 Latin1_General_CI_AS
16 SQLCHAR 0 255 "," 16 T1Source Latin1_General_CI_AS
17 SQLCHAR 0 255 "," 17 N1 Latin1_General_CI_AS
18 SQLCHAR 0 255 "," 18 R1 Latin1_General_CI_AS
19 SQLCHAR 0 255 "," 19 CountryOfBirth Latin1_General_CI_AS
20 SQLCHAR 0 255 "," 20 C2 Latin1_General_CI_AS
21 SQLCHAR 0 255 "," 21 LLL Latin1_General_CI_AS
22 SQLCHAR 0 255 "," 22 DDDLatin1_General_CI_AS
23 SQLCHAR 0 255 "," 23 DEW Latin1_General_CI_AS
24 SQLCHAR 0 255 "," 24 HTW Latin1_General_CI_AS
25 SQLCHAR 0 255 "," 25 Address1 Latin1_General_CI_AS
26 SQLCHAR 0 255 "," 26 Address2 Latin1_General_CI_AS
27 SQLCHAR 0 255 "," 27 Address3 Latin1_General_CI_AS
28 SQLCHAR 0 255 "," 28 Address4 Latin1_General_CI_AS
29 SQLCHAR 0 255 "," 29 Address5 Latin1_General_CI_AS
30 SQLCHAR 0 255 "," 30 PostCode Latin1_General_CI_AS
31 SQLCHAR 0 255 "," 31 PhoneType Latin1_General_CI_AS
32 SQLCHAR 0 255 "," 32 PhoneNumber Latin1_General_CI_AS
33 SQLCHAR 0 255 "," 33 TYE Latin1_General_CI_AS
34 SQLCHAR 0 255 "," 34 SSR Latin1_General_CI_AS
35 SQLCHAR 0 255 "," 35 SSR2 Latin1_General_CI_AS
36 SQLCHAR 0 255 "," 36 CYUU Latin1_General_CI_AS
37 SQLCHAR 0 255 "," 37 CYUU3 Latin1_General_CI_AS
38 SQLCHAR 0 255 "," 38 CYUU4 Latin1_General_CI_AS
39 SQLCHAR 0 0 "" 0 DUMMY1 Latin1_General_CI_AS
40 SQLCHAR 0 0 "" 0 DUMMY2 Latin1_General_CI_AS
41 SQLCHAR 0 0 "" 0 ImportDate Latin1_General_CI_AS
And when I run my stored procedure
Exec usp_ImportMultipleFiles '\\ws-72865\cee\Import\', '*.csv', 'tblImport'
I get the following error message:
Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 41 in format file '\\ws-72865\cee\Groupcall_Phoenix_Changes\groupcallphoenixchanges.fmt'.
If I take column 41 out of the file and SQL table it gives me the message for row 40, etc. Im wondering if there should be some kind of end of character at the end of the fmt file.
Any help would be really appreciated!
Debbie
August 8, 2006 at 4:06 am
Basically it works like
Fill column1 with all you find till it hits the "," character
Fill column2 with all you find till it hits the "," character ...
So the last column needs a "new line" separator, so sql server can go on to the next line.
41 SQLCHAR 0 0 "\r\n" 0 ImportDate Latin1_General_CI_AS
Fill column41 with all you find till it hits the first carriage-return/newline character.
August 8, 2006 at 4:14 am
Thanks for the reply,
Im struggling to understand what you mean with 'Fill column1 with all you find'
Im editing the fmt file in textpad. Ive edited the last line to
41 SQLCHAR 0 0 "\r\n" 0 ImportDate Latin1_General_CI_AS
but Im still getting the same error message.
August 8, 2006 at 5:31 am
My formatfile knowlegde isn't so good either. Sql server is quite picky in its format files. Terminator "\r\n" tells it should go to the next line.
A small resume:
The target table has 41 columns
The format file has 38 columns?
The last column specified "should" be then
38 SQLCHAR 0 255 "\r\n" 38 CYUU4 Latin1_General_CI_AS
or
38 SQLCHAR 0 255 "\n" 38 CYUU4 Latin1_General_CI_AS
skipping the last columns.
I will test my next version.
August 8, 2006 at 6:09 am
You also need to make the 2nd line of the format file equal to the number of columns listed in the format file...
--Jeff Moden
Change is inevitable... Change for the better is not.
August 8, 2006 at 7:02 am
Hi again,
I have made sure the second line says 41 and the 38th row has "\r\n" against it as this is the last record in the source file. Still getting the error message
Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 41 ....
I have made sure that it is the correct collation name so I dont think thats the problem.
We are proper stumped on this one!!!
August 8, 2006 at 12:11 pm
Have you tried (erase the blank line (forum))
8.0
39
1 SQLCHAR 0 255 "," 1 NO Latin1_General_CI_AS
2 SQLCHAR 0 255 "," 2 FormerNO Latin1_General_CI_AS
3 SQLCHAR 0 255 "," 3 Surname Latin1_General_CI_AS
4 SQLCHAR 0 255 "," 4 Forename Latin1_General_CI_AS
5 SQLCHAR 0 255 "," 5 NameOrderIndicator Latin1_General_CI_AS
6 SQLCHAR 0 255 "," 6 MiddleNames Latin1_General_CI_AS
7 SQLCHAR 0 255 "," 7 DateOfBirth Latin1_General_CI_AS
8 SQLCHAR 0 255 "," 8 Gender Latin1_General_CI_AS
9 SQLCHAR 0 255 "," 9 FormerSurname Latin1_General_CI_AS
10 SQLCHAR 0 255 "," 10 PreferredForname Latin1_General_CI_AS
11 SQLCHAR 0 255 "," 11 PreferredSurname Latin1_General_CI_AS
12 SQLCHAR 0 255 "," 12 PreferredNameOrderIndicator Latin1_General_CI_AS
13 SQLCHAR 0 255 "," 13 ETH Latin1_General_CI_AS
14 SQLCHAR 0 255 "," 14 ETHSource Latin1_General_CI_AS
15 SQLCHAR 0 255 "," 15 T1 Latin1_General_CI_AS
16 SQLCHAR 0 255 "," 16 T1Source Latin1_General_CI_AS
17 SQLCHAR 0 255 "," 17 N1 Latin1_General_CI_AS
18 SQLCHAR 0 255 "," 18 R1 Latin1_General_CI_AS
19 SQLCHAR 0 255 "," 19 CountryOfBirth Latin1_General_CI_AS
20 SQLCHAR 0 255 "," 20 C2 Latin1_General_CI_AS
21 SQLCHAR 0 255 "," 21 LLL Latin1_General_CI_AS
22 SQLCHAR 0 255 "," 22 DDDLatin1_General_CI_AS
23 SQLCHAR 0 255 "," 23 DEW Latin1_General_CI_AS
24 SQLCHAR 0 255 "," 24 HTW Latin1_General_CI_AS
25 SQLCHAR 0 255 "," 25 Address1 Latin1_General_CI_AS
26 SQLCHAR 0 255 "," 26 Address2 Latin1_General_CI_AS
27 SQLCHAR 0 255 "," 27 Address3 Latin1_General_CI_AS
28 SQLCHAR 0 255 "," 28 Address4 Latin1_General_CI_AS
29 SQLCHAR 0 255 "," 29 Address5 Latin1_General_CI_AS
30 SQLCHAR 0 255 "," 30 PostCode Latin1_General_CI_AS
31 SQLCHAR 0 255 "," 31 PhoneType Latin1_General_CI_AS
32 SQLCHAR 0 255 "," 32 PhoneNumber Latin1_General_CI_AS
33 SQLCHAR 0 255 "," 33 TYE Latin1_General_CI_AS
34 SQLCHAR 0 255 "," 34 SSR Latin1_General_CI_AS
35 SQLCHAR 0 255 "," 35 SSR2 Latin1_General_CI_AS
36 SQLCHAR 0 255 "," 36 CYUU Latin1_General_CI_AS
37 SQLCHAR 0 255 "," 37 CYUU3 Latin1_General_CI_AS
38 SQLCHAR 0 255 "," 38 CYUU4 Latin1_General_CI_AS
39 SQLCHAR 0 255 "\r\n" 41 Importdate Latin1_General_CI_AS
August 8, 2006 at 12:28 pm
it works. Tested with
CREATE TABLE test
(
[NO] varchar(50)
, FormerNO varchar(50)
, Surname varchar(50)
, Forename varchar(50)
,NameOrderIndicator varchar(50)
, MiddleNames varchar(50)
,DateOfBirth varchar(50)
, Gender varchar(50)
,FormerSurname varchar(50)
,PreferredForname varchar(50)
, PreferredSurname varchar(50)
, PreferredNameOrderIndicator varchar(50)
, ETH varchar(50)
, ETHSource varchar(50)
, T1 varchar(50)
, T1Source varchar(50)
, N1 varchar(50)
, R1 varchar(50)
, CountryOfBirth varchar(50)
, C2 varchar(50)
, LLL varchar(50)
, DDD varchar(50)
, DEW varchar(50)
, HTW varchar(50)
, Address1 varchar(50)
, Address2 varchar(50)
, Address3 varchar(50)
, Address4 varchar(50)
, Address5 varchar(50)
, PostCode varchar(50)
,PhoneType varchar(50)
, PhoneNumber varchar(50)
, TYE varchar(50)
, SSR varchar(50)
, SSR2 varchar(50)
, CYUU varchar(50)
, CYUU3 varchar(50)
, CYUU4 varchar(50)
, DUMMY1 varchar(50)
, DUMMY2 varchar(50)
,ImportDate varchar(50)
)
'bcp file (lines closed with \r\n)
a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,v,w,x,y,z,aa,ab,ac,ad,ae,af,ag,ah,ai,aj,ak,al,am,an
a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,v,w,x,y,z,aa,ab,ac,ad,ae,af,ag,ah,ai,aj,ak,al,am,an
'format file (got wrapped)
8.0
39
1 SQLCHAR 0 50 "," 1 NO Latin1_General_CI_AS
2 SQLCHAR 0 0 "," 2 FormerNO Latin1_General_CI_AS
3 SQLCHAR 0 50 "," 3 Surname Latin1_General_CI_AS
4 SQLCHAR 0 50 "," 4 Forename Latin1_General_CI_AS
5 SQLCHAR 0 50 "," 5 NameOrderIndicator Latin1_General_CI_AS
6 SQLCHAR 0 50 "," 6 MiddleNames Latin1_General_CI_AS
7 SQLCHAR 0 50 "," 7 DateOfBirth Latin1_General_CI_AS
8 SQLCHAR 0 50 "," 8 Gender Latin1_General_CI_AS
9 SQLCHAR 0 50 "," 9 FormerSurname Latin1_General_CI_AS
10 SQLCHAR 0 50 "," 10 PreferredForname Latin1_General_CI_AS
11 SQLCHAR 0 50 "," 11 PreferredSurname Latin1_General_CI_AS
12 SQLCHAR 0 50 "," 12 PreferredNameOrderIndicator Latin1_General_CI_AS
13 SQLCHAR 0 50 "," 13 ETH Latin1_General_CI_AS
14 SQLCHAR 0 50 "," 14 ETHSource Latin1_General_CI_AS
15 SQLCHAR 0 50 "," 15 T1 Latin1_General_CI_AS
16 SQLCHAR 0 50 "," 16 T1Source Latin1_General_CI_AS
17 SQLCHAR 0 50 "," 17 N1 Latin1_General_CI_AS
18 SQLCHAR 0 50 "," 18 R1 Latin1_General_CI_AS
19 SQLCHAR 0 50 "," 19 CountryOfBirth Latin1_General_CI_AS
20 SQLCHAR 0 50 "," 20 C2 Latin1_General_CI_AS
21 SQLCHAR 0 50 "," 21 LLL Latin1_General_CI_AS
22 SQLCHAR 0 50 "," 22 DDD Latin1_General_CI_AS
23 SQLCHAR 0 50 "," 23 DEW Latin1_General_CI_AS
24 SQLCHAR 0 50 "," 24 HTW Latin1_General_CI_AS
25 SQLCHAR 0 50 "," 25 Address1 Latin1_General_CI_AS
26 SQLCHAR 0 50 "," 26 Address2 Latin1_General_CI_AS
27 SQLCHAR 0 50 "," 27 Address3 Latin1_General_CI_AS
28 SQLCHAR 0 50 "," 28 Address4 Latin1_General_CI_AS
29 SQLCHAR 0 50 "," 29 Address5 Latin1_General_CI_AS
30 SQLCHAR 0 50 "," 30 PostCode Latin1_General_CI_AS
31 SQLCHAR 0 50 "," 31 PhoneType Latin1_General_CI_AS
32 SQLCHAR 0 50 "," 32 PhoneNumber Latin1_General_CI_AS
33 SQLCHAR 0 50 "," 33 TYE Latin1_General_CI_AS
34 SQLCHAR 0 50 "," 34 SSR Latin1_General_CI_AS
35 SQLCHAR 0 50 "," 35 SSR2 Latin1_General_CI_AS
36 SQLCHAR 0 50 "," 36 CYUU Latin1_General_CI_AS
37 SQLCHAR 0 50 "," 37 CYUU3 Latin1_General_CI_AS
38 SQLCHAR 0 50 "," 38 CYUU4 Latin1_General_CI_AS
39 SQLCHAR 0 50 "\r\n" 41 ImportDate Latin1_General_CI_AS
August 9, 2006 at 2:01 am
Thanks for that.
Ill try that. I havent used the BCP utility so Im not sure how that file fits in though. Im running the format file from a stored procedure.
Ill let you know how it goes.
Debbie
August 9, 2006 at 4:47 am
You can use bulk insert also. It's only named bcp-file (c:\bcptest.txt) because I've exported the data with bcp.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply