Solved

Bulk Insert Question

Posted on 2004-09-17
19
1,791 Views
Last Modified: 2012-08-13
I have a text file that I want to import into a table using BULK INSERT.  I each entire row in the text file to go into one column in my database table.  Do I set the FIELDTERMINATOR and ROWTERMINATOR both to "\n"?

Books Online says not to use the same value for ROWTERMINATOR and FIELDTERMINATOR

Also, is there any documentation that indicates what these mean:
\t (I know is tab)
\n (I know is New Line)
\r\n (Don't know wtf this is)
etc.
0
Comment
Question by:ABaruh
  • 7
  • 6
  • 4
  • +1
19 Comments
 
LVL 9

Expert Comment

by:miron
ID: 12087164
without bulk insert you can try to use this script running it in the query analyzer

create table t( i int identity primary key, my_text varchar(8000) )
insert into t ( my_text ) execute master.dbo.xp_cmdshell 'type "C:\my_text_file.txt"'


need to make sure that the
text file is located at C:\my_text_file.txt on the "sql server" where the script is executed
account running this script having high enough privileges to execute xp_cmdshell and see the C:\ or the directory you shose

-- cheers
0
 
LVL 42

Expert Comment

by:Eugene Z
ID: 12087184
try DTS Wizard to import the txt or csv file - to see what are delimeters for row and field..

or\and


BULK INSERT yourDB..TempTable  FROM 'c:\yourimport.txt'
   WITH
      (
         MAXERRORS=100
      )
0
 
LVL 7

Author Comment

by:ABaruh
ID: 12087201
The reason I cannot use DTS is because the name of the text file I will be importing is constantly changing

I will try EugeneZ solution
0
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 9

Expert Comment

by:miron
ID: 12087256
you should try the first solution first :)

-- cheers
0
 
LVL 7

Author Comment

by:ABaruh
ID: 12087300
miron, I'm already trying to test in Query Analyzer.  Here is what I am trying:

BULK INSERT MyDB.dbo.MyTable
FROM "c:\Test.txt"
WITH (FIELDTERMINATOR = '\n', ROWTERMINATOR = '\n', FIRSTROW = 1)

I get 0 rows affected.
0
 
LVL 9

Expert Comment

by:miron
ID: 12087342
well,

INSERT MyDB.dbo.MyTable exec master.dbo.xp_cmdshell 'type c:\Test.txt'

should do it, unless you have a record longer then 8000 character. Yet there are more limitations or possible tweaks due to potential unicode / locale, still this is a quick and simple way of selecting a file row by row into the targret table...

-- cheers
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12087386
Your fieldterminator cannot be \n. \n is carriage return.
If your file is tab delimited, fieldterminator='\t'
0
 
LVL 7

Author Comment

by:ABaruh
ID: 12087411
mcp111, my file is not tab delimeted, it's a log file that a windows service produces.  we want to bring that logged information into a SQL table.  I just want each row to be inserted into one field.  i doubt the row would be more than 4000 characters so I have set the column to nvarchar(4000).  Is there another special character (like \n, \t) that can say the end of the field is the data UP TO the carriage return?

miron, I will try your INSERT method
0
 
LVL 9

Expert Comment

by:miron
ID: 12087445
if the field terminator is set to \n then what sql server ( should ) try to do is to select each row of text into a table column, that would create a flat row with as many column as there are lines in the source file. Obviously this would not work. I am just wondering what is stopping you from using the command shell.

-- cheers
0
 
LVL 7

Author Comment

by:ABaruh
ID: 12087480
Oh, miron, I just realized.  Regarding the INSERT...xp_cmdshell solution you identify, I forgot to mention that I need to be able to start the import at a certain Line in the file.
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12087505
try setting only the row terminator to '\n'
Another strategy would be to try using the DTS wizard and see what it selects as the row terminator and field terminator. Then use that in your bulk insert statement.
0
 
LVL 9

Accepted Solution

by:
miron earned 500 total points
ID: 12087668
well, this can be done dynamically with minor addition of the row number variable

-- define the start row
declare @i int
set @i = row_number

-- select table
create table #t( i int identity primary key, my_text varchar(8000) )
insert into #t ( my_text ) execute master.dbo.xp_cmdshell 'type "C:\my_text_file.txt"'

-- remove rows
deleted from #t where i < @i

-- final select
INSERT MyDB.dbo.MyTable select my_text from #t
0
 
LVL 7

Author Comment

by:ABaruh
ID: 12087747
Miron, I suppose that would work, but I hate adding the overhead of processing all those records then eventually deleting them.  If all else fails though, I'll go with that solution.

mcp111 - I did as you suggested.  In the DTS Wizard I set the Fieldterminator to something like 'sasdfasfwef' and rowterminator to {CR}{LF} and it imported fine.  Then I tried the BULK INSERT from Query Analyzer specifying FIELDTERMINATOR = 'sasdfasfwef' and ROWTERMINATOR = '{CR}{LF}' and it gave a STREAM...Unexpected EOF reached error.  I also tried ROWTERMINATOR = '\n' with the aforementioned FIELDTERMINATOR and got same error
0
 
LVL 9

Expert Comment

by:miron
ID: 12087840
Well, looks like when the the FIELDTERMINATOR set to '' should work

-- cheers
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12087886
try rowterminator='\n' and fieldterminator='\t'
It works!
0
 
LVL 7

Author Comment

by:ABaruh
ID: 12088278
Neither of those work.  THe specific error I get is:

Bulk insert: Unexpected end-of-file (EOF) encountered in data file
OLE DB Provider 'STREAM' reported an error.  The provider did not give any information about the error.

It looks like I will go with Miron's Insert solution and delete the rows I do not want out of the temp table.

Thanks everyone.
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12088403
I just tried this query on a text file and it worked fine. Did you try a query exactly like this?

BULK INSERT abbreviations
   FROM 'd:\partha\abbreviations.txt'
   WITH
      (
         FIELDTERMINATOR = '\t',
         ROWTERMINATOR = '\n'
      )
0
 
LVL 7

Author Comment

by:ABaruh
ID: 12089009
that is what I tried.  I got the error specified above.  The text file is a test file with just two lines:

This freaking thing
better work
0
 
LVL 42

Expert Comment

by:Eugene Z
ID: 12093937
ABaruh:
did it work?

BULK INSERT yourDB..TempTable  FROM 'c:\yourimport.txt'
   WITH
      (
         MAXERRORS=100
      )

It is just for ASCII txt
if you have binary it is almost same just need
specify
 DATAFILETYPE [ =
                { 'char' | 'native'| 'widechar' | 'widenative' } ]

see more

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question