Solved

How do I get bulk insert to recognize end of row?

Posted on 2010-08-15
14
634 Views
Last Modified: 2012-05-10
I have started having trouble with a more complex bulk insert stored procedure. So I decided to build the sp from the basic and now I'm having trouble with a very simple one. I should note I'm used to sql srvr 2005 and am now using 2008 if that makes a difference.  The txt file is tab delimited. It looks like this:
aaa     bbb     ccc
ddd     eee     fff
ggg     hhh     iii

When transfered from the temp table to the db table I get this in the db table
last_name    first_name     middle_name
aaa               bbb               cccdddeeefffggghhhiii

I have tried the insert with the terminators below  -- also with the row terminator \r -- and with no terminators. I always get the same results.

I need to get this complex bulk insert I started done tonight so in keeping with only asking one question at a time there will be at least one follow up question. Everything will be worth 500 pts. I'll make a closing comment at the end of this after solution for title and location of next issue.

Below is the code for the sp:

ALTER PROCEDURE [dbo].[uspImportStudentInfoNoDuplicates]
      -- Add the parameters for the stored procedure here
--      @p1 int = 0,
--      @p2 int = 0
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;


CREATE TABLE ImportStudentTempTable
(last_name nvarchar(255),
first_name nvarchar(255),
middle_name nvarchar(255)
)

BULK INSERT ImportStudentTempTable
FROM *****This was the path and txt file location
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)

-- Insert the new student records
INSERT INTO ImportDataTest
   SELECT last_name, first_name, middle_name
   FROM ImportStudentTempTable

DROP TABLE ImportStudentTempTable

END

0
Comment
Question by:bobbellows
  • 7
  • 6
14 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33442312
Funny, your code worked perfectly for me, and I am using SQL Server 2008.  My test file is exactly as you have it, 3 fields, tabs in between and Windows CRLF between lines, including last line.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33442321
Using a tool like Notepad++, I converted the file first to Unix format, then to Mac format (cr or lf but not both); only then do I get your problem.  Where did you get your file from?
0
 
LVL 7

Expert Comment

by:arduk
ID: 33442344
you may need to open up the file in a editor that will allow you to see what the actual hex value of the row terminator is - depending on where it was created, I have found files have a mixture of row terminators - \r, \n, \r\n etc,

also, have seen problems where people had to specify the rowterminator as the hex value as well
ie. ROWTERMINATOR = '''+char(0x0A)+'''

Hope this helps.
0
 

Author Comment

by:bobbellows
ID: 33442358
I at first typed it up in notepad with "," delimited. Ran into this problem. Went to Word -- changed to Tab delimited -- chose just LF for terminator -- CR for terminator or both and it always gives me the same problem. This is driving me nuts. Could it be something in the db table?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33442368
Try this file.
You need a better tool than MS Word for checking file line ending types! Notepad++ is free...
test.txt
0
 

Author Comment

by:bobbellows
ID: 33442426
cyberkiwi,

I'll go get Notepad++ right now. But this is what happened. Saved your file to my hd -- 39 bytes. Used filezilla for ftp transfer to server and noticed it is reporting only 36 bytes. Don't know if that helps or not.
Bob
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33442434
>Used filezilla for ftp transfer to server and noticed it is reporting only 36 bytes.
It's either lost the CR or LF from each line, 3 of them.
I actually use the PortableApps version of Notepad++ which is always on USB - but that's an aside.
When you open the file, in menu Format, the first 3 options are
Convert to Windows
Convert to Unix
Convert to Mac
whichever one is disabled is the current format of the file.
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 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33442486
fwiw, this is what works with unix files

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT ImportStudentTempTable
FROM ''C:\test.txt''
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

and with mac files

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT ImportStudentTempTable
FROM ''C:\test.txt''
WITH (ROWTERMINATOR = '''+CHAR(13)+''')'
EXEC(@bulk_cmd)

'\n' is actually interpreted as char(13)+char(10).
If you used Char(13) on a windows file, you get unexpected end of file when it finds the last char(10).
If you use char(10), you actually get char(13) as part of the data of the middle name.
0
 

Author Comment

by:bobbellows
ID: 33442530
OK,
I must have downloaded a different version of Notepad++ than you. Don't have a Menu button nor a Format button in the menu items across the top. searched help for "conver" and didnt get anything.

when I went to "save as" the file it claimed it as a "normal text file."

I transfered the file from the server back to my machine and it reports 39bytes and when opened in notepad ++ it looks fine.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33442546
You could try the 2 queries above and check which one works.
0
 

Author Comment

by:bobbellows
ID: 33442856
cyberkiwi,
I only had 5 hours of sleep last night -- it's starting to get late now -- I don't know what my clients are going to say in the morning but I'm going to have to pick this up tomorrow. Stay tuned.
Thanks
Bob
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33442917
>I only had 5 hours of sleep last night

Lucky you!
0
 

Author Closing Comment

by:bobbellows
ID: 33445208
The unix code worked. Final code that worked, below. See other comments for followup question worth 500 points!

ALTER PROCEDURE [dbo].[uspImportStudentInfoNoDuplicates]
      -- Add the parameters for the stored procedure here
--      @p1 int = 0,
--      @p2 int = 0
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;


CREATE TABLE ImportStudentTempTable
(last_name varchar(50),
first_name varchar(50),
middle_name varchar(50)
)

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT ImportStudentTempTable
FROM ''\\*********Location of txt file'
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)


-- Insert the new student records
INSERT INTO ImportDataTest
   SELECT last_name, first_name, middle_name
   FROM ImportStudentTempTable


DROP TABLE ImportStudentTempTable

END
0
 

Author Comment

by:bobbellows
ID: 33445223
Follow up question on how to expand the field list for the bulk insert with limited mapping.  500 pts.
Title: How do I bulk insert only a partial record into a MS SQL db with a stored proc.?
ID # 26405383
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

747 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

8 Experts available now in Live!

Get 1:1 Help Now