Problem while importing data into table - General SQL Database Question & Answer

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » SQL Server Database Administration » General SQL Database Question & Answer » Problem while importing data into table

Problem while importing data into table

Options

Previous Topic · Next Topic

tash

Posted: Monday, July 28, 2008 3:46:40 AM

Rank: Newbie

Joined: 7/28/2008
Posts: 2
Points: 6

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

Code:

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 $):

Code:

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:

Code:

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:

Code:

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

Scott Whigham

Posted: Monday, July 28, 2008 10:21:27 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

The only reason I can think of that this would occur is that you have nulls in the source data. One suggestion I would have is, if you are on SQL 2005+, use an OPENROWSET query against the data.

SELECT a.* FROM OPENROWSET( BULK 'C:\AR_project\LHT_data\WorkData\DEMO06Q4.TXT', FORMATFILE = 'C:\AR_project\LHT_data\FormatFiles\Transactions.fmt') AS a

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.