Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 664
  • Last Modified:

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

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
bobbellows
Asked:
bobbellows
  • 7
  • 6
1 Solution
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
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
 
ardukCommented:
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
bobbellowsAuthor Commented:
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
 
cyberkiwiCommented:
Try this file.
You need a better tool than MS Word for checking file line ending types! Notepad++ is free...
test.txt
0
 
bobbellowsAuthor Commented:
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
 
cyberkiwiCommented:
>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
 
cyberkiwiCommented:
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
 
bobbellowsAuthor Commented:
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
 
cyberkiwiCommented:
You could try the 2 queries above and check which one works.
0
 
bobbellowsAuthor Commented:
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
 
cyberkiwiCommented:
>I only had 5 hours of sleep last night

Lucky you!
0
 
bobbellowsAuthor Commented:
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
 
bobbellowsAuthor Commented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now