July 28, 2008 at 2:38 am
Hello, I try to import data from text file into table, created with this query:
USE AR;
CREATE TABLE dbo.InitTransactions (
[TransactionID] VARCHAR(50) NOT NULL
, [TransactionDate] VARCHAR(50)
)
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 SQLCHAR 0 50 "$" 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 50 "$" 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" 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 NOT ALL values of the second field "TransactionDate" are inserted. I mean that in some rows I still have value NULL in this field.
At the same time data in other rows is inserted correctly.
It is really strange. I tried to "play" with format file (changing data type) but it doesn't work.
I still think that import is incorrect because of data types...but I don't
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
July 28, 2008 at 6:26 am
Are you sure the sixth field is filled in for every row in your original file?
July 28, 2008 at 6:40 am
No, so this is the exact problem:
" when I open my table AR.dbo.InitTransactions, I see that NOT ALL values of the second field "TransactionDate" are inserted. I mean that in some rows I still have value NULL in this field."
for e. g.
TransactionID TransactionDate
5045669 20060213
5047244 NULL
5047315 NULL
5049307 NULL
5049687 20051114
5050538 20060101
5050583 20060615
July 28, 2008 at 7:02 am
Have you viewed the source file to see what the data should be for these rows, or to see if there is some other data issue in the file for those rows?
If it was easy, everybody would be doing it!;)
July 28, 2008 at 7:15 am
Yes, these rows in my text file contain data, and data format is the same: YYYYMMDD. I tried to do this import operation with BULK INSERT, but obtained just the same table 🙁
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply