SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Bulk Insert using a Fixed Length file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

starnold
Yak Posting Veteran

83 Posts

Posted - 02/28/2005 :  10:33:38  Show Profile  Reply with Quote
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 - 02/28/2005 :  11:38:28  Show Profile  Reply with Quote
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 - 02/28/2005 :  11:51:58  Show Profile  Reply with Quote
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 - 02/28/2005 :  12:37:18  Show Profile  Reply with Quote
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 - 03/01/2005 :  06:27:38  Show Profile  Reply with Quote
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 - 03/01/2005 :  10:24:39  Show Profile  Reply with Quote
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

Azerbaijan
5 Posts

Posted - 05/08/2013 :  01:24:24  Show Profile  Reply with Quote
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

Azerbaijan
5 Posts

Posted - 05/08/2013 :  01:25:07  Show Profile  Reply with Quote
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

Azerbaijan
5 Posts

Posted - 05/08/2013 :  01:32:11  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000