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.
| Author |
Topic |
|
tash
Starting Member
12 Posts |
 Posted - 2008-07-15 : 05:46:10
|
Hello, I need to import data from text file.I'm using BULK INSERT + format file because I need to skip some fields in my data file.But I can't fix error appearing during query execution, I've tried to find the answer in the Internet, but didn't succeed.This is my text datafile: (example of record: 12 fields delimited by $, row is terminated with $ too, some of them are empty as you can see from this example))5045669$1007449353$PS$TEAM$1$TRY$$$$UNKNOWN$$21226$ This is the format of my table in database:[id, name, transaction_id] created with the following query:USE MyDB;GOCREATE TABLE dbo.table1(id int NOT NULL,name varchar(50) NOT NULL,transaction_id int PRIMARY KEY NOT NULL)GO in this table:"transaction_id" should match 1st field in datafile (5045669 in my example)"name" = 4th field (TEAM in my example)"id" = 2nd field in datafile (1007449353 in ex.)all other fields must be skipped.I use myformat.fmt file, created manually:9.0122 SQLINT 0 4 "$" 1 id ""4 SQLCHAR 2 50 "$" 2 name SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 2 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS1 SQLINT 0 4 "$" 3 transaction_id ""5 SQLINT 0 4 "$" 0 Extra "" 6 SQLCHAR 2 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS7 SQLCHAR 2 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 2 8 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS9 SQLCHAR 2 8 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS10 SQLINT 0 4 "$" 0 Extra ""11 SQLINT 0 4 "$" 0 Extra ""12 SQLINT 0 4 "$" 0 Extra "" I'm doing my import using this query:BULK INSERT MyDB.dbo.table1FROM 'C:\Documents and Settings\??????\project\data\data.TXT' WITH (FIELDTERMINATOR='$', ROWTERMINATOR='$', FORMATFILE='C:\Documents and Settings\??????\project\data\myformat.fmt');GO but I obtain the following error:Msg 4823, Level 16, State 1, Line 1Cannot bulk load. Invalid column number in the format file "C:\Documents and Settings\??????\project\data\myformat.fmt".I don't know how to fix it! I tried to check whether I was inprecise while creating format file, but it seems to me that everything is correct, I've put myformat.fmt into the same directory with my text datafile (it was in "C:\SQL Server Management Studio\Projects" folder initially), but I still get this error.Help me, please |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-15 : 06:25:59
|
Hi there,I don't use BULK IMPORT but do use bcp and I can get your data working with that. The format file should work for you.DDLCREATE TABLE foo ( [id] INT NOT NULL , [name] VARCHAR(50) NOT NULL , [transaction_id] INT PRIMARY KEY NOT NULL ) IMPORT FILE5045669$1007449353$PS$TEAM$1$TRY$$$$UNKNOWN$$21226$5045343$1023449353$PS$TEAM$1$TRY$$$$UNKNOWN$$21226$ FORMAT FILE8.0121 SQLCHAR 0 50 "$" 3 transaction_Id SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 50 "$" 1 Id ""3 SQLCHAR 0 50 "$" 0 EXTRA SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 0 100 "$" 2 Name SQL_Latin1_General_CP1_CI_AS5 SQLINT 0 4 "$" 0 Extra "" 6 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS7 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 0 8 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS9 SQLCHAR 0 8 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS10 SQLINT 0 4 "$" 0 Extra ""11 SQLINT 0 4 "$" 0 Extra ""12 SQLINT 0 4 "$\r\n" 0 Extra "" BCP STRINGbcp <db>..foo IN import.txt -f "format.fmt" -S<server> -Usa -P<pass> RESULTSStarting copy...2 rows copied.Network packet size (bytes): 4096Clock Time (ms.): total 16 SELECT * FROM fooid name transaction_id1023449353 TEAM 50453431007449353 TEAM 5045669 All the best,Charlie.-------------Charlie |
 |
|
|
tash
Starting Member
12 Posts |
Posted - 2008-07-15 : 08:07:00
|
quote:
Originally posted by Transact Charlie
Hi there,I don't use BULK IMPORT but do use bcp and I can get your data working with that. The format file should work for you.DDLCREATE TABLE foo ( [id] INT NOT NULL , [name] VARCHAR(50) NOT NULL , [transaction_id] INT PRIMARY KEY NOT NULL ) IMPORT FILE5045669$1007449353$PS$TEAM$1$TRY$$$$UNKNOWN$$21226$5045343$1023449353$PS$TEAM$1$TRY$$$$UNKNOWN$$21226$ FORMAT FILE8.0121 SQLCHAR 0 50 "$" 3 transaction_Id SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 50 "$" 1 Id ""3 SQLCHAR 0 50 "$" 0 EXTRA SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 0 100 "$" 2 Name SQL_Latin1_General_CP1_CI_AS5 SQLINT 0 4 "$" 0 Extra "" 6 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS7 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 0 8 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS9 SQLCHAR 0 8 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS10 SQLINT 0 4 "$" 0 Extra ""11 SQLINT 0 4 "$" 0 Extra ""12 SQLINT 0 4 "$\r\n" 0 Extra "" BCP STRINGbcp <db>..foo IN import.txt -f "format.fmt" -S<server> -Usa -P<pass> RESULTSStarting copy...2 rows copied.Network packet size (bytes): 4096Clock Time (ms.): total 16 SELECT * FROM fooid name transaction_id1023449353 TEAM 50453431007449353 TEAM 5045669 All the best,Charlie.-------------Charlie
Thank you very much for answer,it works for few seconds, but after importing some data I've got an error:1000 rows sent to SQL Server. Total sent: 2850001000 rows sent to SQL Server. Total sent: 2860001000 rows sent to SQL Server. Total sent: 2870001000 rows sent to SQL Server. Total sent: 2880001000 rows sent to SQL Server. Total sent: 2890001000 rows sent to SQL Server. Total sent: 2900001000 rows sent to SQL Server. Total sent: 291000SQLState = 23000, NativeError = 2627Error = [Microsoft][SQL Native Client][SQL Server]Violation of PRIMARY KEY constraint 'PK__table1__09DE7BCC'. Cannot insert duplicate key in object 'dbo.table1'.SQLState = 01000, NativeError = 3621Warning = [Microsoft][SQL Native Client][SQL Server]The statement has been terminated.BCP copy in failed |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2008-07-15 : 08:47:48
|
| You may need to bcp your data into an intermediate table that doesn't contain the primary key constraint. You have a duplicate either in your destination table or in the source file. Put the data in an intermediate table and run 2 queries on it. First, query the intermediate table using the fields that make up the primary key in the production table and do a count(*), group by the pk fields and look for count(*)>1. For example:select pkfield1,pkfield2,count(*) from intermediatetable group by pkfield1,pkfield2 having count(*)>1If that doesn't produce any rows then you know your file is not the source of the dupes. Run the same query again but join to the prod table. select d.pkfield1,d.pkfield2,count(*) from prodTable d inner join intermediatetable i on d.pkfield1=i.pkfield1 and d.pkfield2=i.pkfield2 group by d.pkfield1,d.pkfield2 having count(*)>1Mike"oh, that monkey is going to pay" |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-07-15 : 08:58:32
|
| Exactly.I also notice that (if the sample file is as you specified) your primary key is only going to be 7 digits long and probably not have negatives.There's a good chance of dupes with a key that short.Charlie.-------------Charlie |
|
|
|
|
|
|
|
|
format file error, I can't fix it!