February 28, 2008 at 9:30 am
I have a file that needs to be bulk inserted into a table. I have two issues. The file may or may not contain an end of file, and may or may not contain an end of line. Each line represents a record that is read into a table row.
How would I have to write the code to resolve these issues?
This is what I have so far (just relevant code snippet):
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[atable]') AND type in (N'U'))
DROP TABLE [dbo].[atable]
GO
CREATE TABLE atable
(
RECORD_FILE VARCHAR(200)
)
GO
Truncate Table atable
if @file_name is null
begin
set @file_name = 'c:\reports\afile.txt'
end
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT dbo.atable
FROM ''' + @file_name + '''
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)
February 28, 2008 at 9:45 am
Try using the new line terminater. It may not have carriage returns but it should have a new line.
= new line
\r = carriage return\line feed
Additional documentation on terminators
February 28, 2008 at 10:00 am
which one should I use newline or \r? Or can I test for both?
Also, how would I have to change my code to include this?
February 28, 2008 at 10:07 am
For some reason the \ n was stripped from my post. This is the new line character.
Well typically you use \r for carriage return/line feed. I would start with this. If you open the file in notepad and the rows are very distiguishable and aligned, then chances are you need \r. If not then you might want to use \ n.
February 28, 2008 at 10:16 am
Thanks, what about checking for end of file?
February 28, 2008 at 10:28 am
There is an end of file marker embedded in there somewhere. You should not have to check for it, unless you have something outside the norm, like a delimiter at the end.
February 28, 2008 at 11:14 am
I appreciate your help. It worked!
February 28, 2008 at 11:43 am
NP 😉 Thanks for the feedback.
March 5, 2008 at 11:29 am
I hope you can help me again. What if the sp reads in the end-of-file marker, how can I prevent that from happening?
March 5, 2008 at 11:46 am
The end of file marker is typically an ASCII 26 and SQL Server will not read it in. You don't have to prevent it from happening.
--Jeff Moden
March 5, 2008 at 11:49 am
It is reading it in though.
March 5, 2008 at 12:02 pm
Ok... since you can't actually see Ascii 26 except maybe as a supurious square, how can you tell the end of file marker is being loaded?
--Jeff Moden
March 5, 2008 at 2:17 pm
I see a square in the lost row inserted. It doesn't have any record information but for the square in the last row.
March 5, 2008 at 2:23 pm
The bulk insert, inserts records from a txt file. The txt file has the square bracket at the very end of the page/ or last record. Should that bracket be there in the txt file?
March 5, 2008 at 2:24 pm
Sounds like you have an extra line your text file. Delete the last row of the file. It should not contain any data just a hard return like in MS Word.
This is a guess, a blank line may or may not exist in your file.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply