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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Bulk Insert using a Fixed Length file

Author  Topic 

starnold
Yak Posting Veteran

83 Posts

Posted - 2005-02-28 : 10:33:38
I am trying to use bulk insert to import a fixed length file. I can find instructions on how to do this for a comma delimited file.

Can anyone help?

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-28 : 11:38:28
Yo need to use a format file

Did you look up Bulk insert in Books Online?



Brett

8-)
Go to Top of Page

starnold
Yak Posting Veteran

83 Posts

Posted - 2005-02-28 : 11:51:58
I have used a format file:
8.0
3
1 SQLCHAR 0 12 "" 1 Test SQL_Latin1_General_CI_AS
2 SQLCHAR 0 9 "" 2 Test1 SQL_Latin1_General_CI_AS
3 SQLCHAR 0 8 "\r\n" 3 Test2 SQL_Latin1_General_CI_AS

But I get a Collation error for the last field?

I checked the collation settings for that database and they are the same?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-28 : 12:37:18
Make sure your format file has another line feed after the last line

Test Code


SET NOCOUNT ON
CREATE TABLE myTable99(Test char(12), Test2 char(9), Test3 char(8))
GO

BULK INSERT Northwind.dbo.myTable99
FROM 'd:\tax\Test.dat'
WITH
(
FORMATFILE = 'D:\tax\test.fmt'
)
GO

SELECT * FROM myTable99
GO

SET NOCOUNT ON
DROP TABLE myTable99
GO



Format File


8.0
3
1 SQLCHAR 0 12 "" 1 Test SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 9 "" 2 Test1 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 8 "\r\n" 3 Test2 SQL_Latin1_General_CP1_CI_AS



Sample Data


12345678901234567890123456789
XXXXXXXXXXXXYYYYYYYYYZZZZZZZZ


Brett

8-)
Go to Top of Page

starnold
Yak Posting Veteran

83 Posts

Posted - 2005-03-01 : 06:27:38
It was beacuse my format file did not have a line feed at the end. Cheers

However when I now try and run this I get an error saying that my PRIMARY filegroup is full?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-01 : 10:24:39
The easiest way is to go into Enterprise Manager, right click on the database, look at the file groups tab, and set the file properties to automatically grow.

What is the current size of the file?



Brett

8-)
Go to Top of Page

i_am_natya
Starting Member

5 Posts

Posted - 2013-05-08 : 01:24:24
hello please help me

bulk insert ats.dbo.myTable999
from 'c:\Temp\yyy.txt'
with
(fieldterminator='\t',
rowterminator='\n')
----------------------
error error error
-------------
text this
-------------
003400410 20130501 142 07742 1 EV1 X NADIRZAD? EL??N NURU O?LU 474 000000000 242 000000000 0 x
003408167 20130501 142 07906 1 EV1 X M?MM?DOVA M?LAK?T ?HM?D 471 000000000 242 000000000 0 x
003408677 20130501 142 07906 1 EV30 X M?MM?DOV MIRZ?A?A NADIR O?LU 474 117150016 242 000000000 0 x
003408922 20130501 731 07906 1 EV22 X MURADOV F?RID? V?KILH?SEYN 000 117150016 242 000000000 0 x
003416005 20130501 142 07200 1 EV32 M?N24 X KAZ?MOV ?FLATUN ?LIZAKIR 474

hello yun jtgjh


hello yun jtgjh
Go to Top of Page

i_am_natya
Starting Member

5 Posts

Posted - 2013-05-08 : 01:25:07
003400410 20130501 142 07742 1 EV1 X NADIRZAD? EL??N NURU O?LU 474 000000000 242 000000000 0 x
003408167 20130501 142 07906 1 EV1 X M?MM?DOVA M?LAK?T ?HM?D 471 000000000 242 000000000 0 x
003408677 20130501 142 07906 1 EV30 X M?MM?DOV MIRZ?A?A NADIR O?LU 474 117150016 242 000000000 0 x

hello yun jtgjh
Go to Top of Page

i_am_natya
Starting Member

5 Posts

Posted - 2013-05-08 : 01:32:11
hello please help me

bulk insert ats.dbo.myTable999
from 'c:\Temp\yyy.txt'
with
(fieldterminator='\t',
rowterminator='\n')
----------------------
error error error
-------------
text this
-------------
003400410 20130501 142 07742 1 EV1 X NADIRZAD? EL??N NURU O?LU 474 000000000 242 000000000 0 x
003408167 20130501 142 07906 1 EV1 X M?MM?DOVA M?LAK?T ?HM?D 471 000000000 242 000000000 0 x
003408677 20130501 142 07906 1 EV30 X M?MM?DOV MIRZ?A?A NADIR O?LU 474 117150016 242 000000000 0 x
003408922 20130501 731 07906 1 EV22 X MURADOV F?RID? V?KILH?SEYN 000 117150016 242 000000000 0 x

hello yun jtgjh
Go to Top of Page
   

- Advertisement -