Solved

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

Posted on 2010-08-15
14
647 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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…

820 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