Format file problem again - 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

General SQL Server Forums

New to SQL Server Programming

Format file problem again

Author

Topic

tash
Starting Member

12 Posts

Posted - 2008-07-22 : 14:03:49

Hello, I try to import data from text file into table, created with this query:


USE AR;
CREATE TABLE dbo.InitTransactions (
	 [TransactionID] BIGINT NOT NULL
	, [TransactionDate] BIGINT 
	)
GO

I don't have primary key here, I don't need it.
I need to import only 1st and 6th columns of text file into 1st and 2nd table columns respectively.
This is example of data in text file (this is ONE row, which consists of 23 fields, delimited with $, last field is also terminated with $):


5045669$6071557$F$$5045669-6$20060213$20060616$20060707$EXP$BR-ABBOTT-06P-020-0336330-00$ABBOTT$$$M$Y$$$20060707$$20060611$$$IRELAND$

so the first field "TransactionID" here is 5045669
and the sixth field "TransactionDate" is 20060213 (we use format YYYYMMDD)

I use the following format file:


8.0
23
1  SQLBIGINT  0   8   "$"  1  TransactionID  ""
2  SQLBIGINT  0   8   "$"  0  Extra          ""
3  SQLCHAR    0   100 "$"  0  Extra       SQL_Latin1_General_CP1_CI_AS
4  SQLCHAR    0   100 "$"  0  Extra       SQL_Latin1_General_CP1_CI_AS
5  SQLCHAR    0   100 "$"  0  Extra      SQL_Latin1_General_CP1_CI_AS 
6  SQLBIGINT  0   8   "$"  2  TransactionDate  ""
7  SQLBIGINT  0   8   "$"  0  Extra            ""
8  SQLBIGINT  0   8   "$"  0  Extra            ""
9  SQLCHAR    0   100 "$"  0  Extra       SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR    0   100 "$"  0  Extra       SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR    0   100 "$"  0  Extra       SQL_Latin1_General_CP1_CI_AS
12 SQLBIGINT  0   8   "$"  0  Extra            "" 
13 SQLCHAR    0   50  "$"  0  Extra      SQL_Latin1_General_CP1_CI_AS 
14 SQLCHAR    0   50  "$"  0  Extra      SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR    0   50  "$"  0  Extra      SQL_Latin1_General_CP1_CI_AS 
16 SQLBIGINT  0   8   "$"  0  Extra            "" 
17 SQLCHAR    0   50  "$"  0  Extra      SQL_Latin1_General_CP1_CI_AS 
18 SQLBIGINT  0   8   "$"  0  Extra            "" 
19 SQLCHAR    0   50  "$"  0  Extra     SQL_Latin1_General_CP1_CI_AS 
20 SQLBIGINT  0   8   "$"  0  Extra            "" 
21 SQLCHAR    0   50  "$"  0  Extra     SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR    0   50  "$"  0  Extra     SQL_Latin1_General_CP1_CI_AS 
23 SQLCHAR    0   50  "$\r\n" 0 Extra   SQL_Latin1_General_CP1_CI_AS

I use the following command (bcp)


bcp AR.dbo.InitTransactions IN C:\AR_project\LHT_data\WorkData\DEMO06Q4.TXT -f C:\AR_project\LHT_data\FormatFiles\Transactions.fmt -S -T

I obtain this output in command string:


1000 rows sent to SQL Server. Total sent: 71000
1000 rows sent to SQL Server. Total sent: 72000
1000 rows sent to SQL Server. Total sent: 73000
1000 rows sent to SQL Server. Total sent: 74000
1000 rows sent to SQL Server. Total sent: 75000
1000 rows sent to SQL Server. Total sent: 76000
1000 rows sent to SQL Server. Total sent: 77000
1000 rows sent to SQL Server. Total sent: 78000
1000 rows sent to SQL Server. Total sent: 79000
1000 rows sent to SQL Server. Total sent: 80000
1000 rows sent to SQL Server. Total sent: 81000
1000 rows sent to SQL Server. Total sent: 82000
1000 rows sent to SQL Server. Total sent: 83000

83229 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1141   Average : (72943.91 rows per sec.)

BUT when I open my table AR.dbo.InitTransactions, I see that values of fields don't match corresponding values in initial text file.
They are really strange, nothing similar.
I guess that import is incorrect because of data types...but I don't
understand what problem I have. I checked bounds for SQLBIGINT,
I also thought about wheather I typed correct datatypes for Extra fields, I guess it doesn't matter, because this fields are not imported into table, but still I checked, and it seems that everything is correct.
Help me, please. I need to obtain correct values in corresponding table columns

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-07-22 : 14:42:03

8.0
23
1 SQLCHAR 0 8 "$" 1 TransactionID ""
2 SQLCHAR 0 8 "$" 0 Extra ""
3 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 8 "$" 2 TransactionDate ""
7 SQLCHAR 0 8 "$" 0 Extra ""
8 SQLCHAR 0 8 "$" 0 Extra ""
9 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 8 "$" 0 Extra ""
13 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 8 "$" 0 Extra ""
17 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 8 "$" 0 Extra ""
19 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 8 "$" 0 Extra ""
21 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 50 "$\r\n" 0 Extra SQL_Latin1_General_CP1_CI_AS

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

tash
Starting Member

12 Posts

Posted - 2008-07-22 : 15:06:56

quote:
Originally posted by jhocutt

8.0
23
1 SQLCHAR 0 8 "$" 1 TransactionID ""
2 SQLCHAR 0 8 "$" 0 Extra ""
3 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 8 "$" 2 TransactionDate ""
7 SQLCHAR 0 8 "$" 0 Extra ""
8 SQLCHAR 0 8 "$" 0 Extra ""
9 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 100 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 8 "$" 0 Extra ""
13 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 8 "$" 0 Extra ""
17 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 8 "$" 0 Extra ""
19 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 8 "$" 0 Extra ""
21 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 50 "$" 0 Extra SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 50 "$\r\n" 0 Extra SQL_Latin1_General_CP1_CI_AS

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Do you mean that I should use this format file instead of mine?
I obtain this error in the command line:


C:\Documents and Settings\??????>bcp AR.dbo.InitTransactions IN C:\AR_project\LHT_data\WorkData\DEMO06Q4.TXT -f C:\AR_project\LHT_data\FormatFiles\Transact
ions2.fmt -S -T
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]I/O error while reading BCP format file

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-07-22 : 15:08:35

bcp AR.dbo.InitTransactions IN YOURDATAFILE -f FORMATFILE -E -T

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

tash
Starting Member

12 Posts

Posted - 2008-07-22 : 15:22:56

quote:
Originally posted by jhocutt

bcp AR.dbo.InitTransactions IN YOURDATAFILE -f FORMATFILE -E -T

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Strange thing, I still obtain this error:


C:\Documents and Settings\??????>bcp AR.dbo.InitTransactions IN C:\AR_project\LHT_data\WorkData\DEMO06Q4.TXT -f C:\AR_project\LHT_data\FormatFiles\Transact
ions2.fmt -E -T
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]I/O error while reading BCP format file

I've checked my paths, but it seems that everything is right.
There is a chance that I was inattentive, but I don't think so..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources