Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Bulk Insert Question

Posted on 2004-09-17
19
Medium Priority
?
1,806 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 43

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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 2000 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 43

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

705 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