Solved

Bulk Insert Question

Posted on 2004-09-17
19
1,752 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
Comment Utility
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:EugeneZ
Comment Utility
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
Comment Utility
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
 
LVL 9

Expert Comment

by:miron
Comment Utility
you should try the first solution first :)

-- cheers
0
 
LVL 7

Author Comment

by:ABaruh
Comment Utility
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
Comment Utility
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
Comment Utility
Your fieldterminator cannot be \n. \n is carriage return.
If your file is tab delimited, fieldterminator='\t'
0
 
LVL 7

Author Comment

by:ABaruh
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Author Comment

by:ABaruh
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Well, looks like when the the FIELDTERMINATOR set to '' should work

-- cheers
0
 
LVL 6

Expert Comment

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

Author Comment

by:ABaruh
Comment Utility
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
Comment Utility
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
Comment Utility
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:EugeneZ
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now