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

x
?
Solved

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

Posted on 2010-08-15
14
Medium Priority
?
656 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
[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
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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 insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

722 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