Bulk Insert MS SQL 2008

Hi,

I have a CSV file that in being generated from my Telephone company. I'm trying to import these numbers into MS SQL using Bulk Insert.

When I open the file in notepad, I see a box characters at the end of each row. I contact the telephone company, they are saying that each row is ended by a standard line break.

These box character is causing the Bulk Insert to fail. I use the following Statement but it fails. I tried the same statement by manually removing the square at the end of few rows and it works.

BULK 
INSERT dbo.Table1
 FROM 'D:\20120327_20120327.txt' 
 WITH
 (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\r\n'
)

Open in new window


When I run the above statement, I get the following error:

The bulk load failed. The column is too long in the data file for row 1, column 20. Verify that the field terminator and row terminator are specified correctly.

Can someone help me with this please? I have also attached a sample file.

Thanks.

here is the statement to create Table1:

CREATE TABLE [dbo].[table1](
	[CallType] [varchar](50) NULL,
	[StartTime] [varchar](50) NULL,
	[StopTime] [varchar](50) NULL,
	[CallDuration] [varchar](50) NULL,
	[BillDuration] [varchar](50) NULL,
	[CallMinimum] [varchar](50) NULL,
	[CallIncrement] [varchar](50) NULL,
	[BasePrice] [varchar](50) NULL,
	[CallPrice] [varchar](50) NULL,
	[TransactionId] [varchar](50) NULL,
	[CustomerIP] [varchar](50) NULL,
	[ANI] [varchar](50) NULL,
	[ANIState] [varchar](50) NULL,
	[DNIS] [varchar](50) NULL,
	[LRN] [varchar](50) NULL,
	[DNISState] [varchar](50) NULL,
	[DNISLATA] [varchar](50) NULL,
	[DNISOCN] [varchar](50) NULL,
	[OrigTier] [varchar](50) NULL
) ON [PRIMARY]

Open in new window

20120214-20120214.txt
ezzadinAsked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
ok, use this :)

BULK INSERT dbo.Table1
FROM 'c:\temp\20120214-201202141.txt'
WITH (
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '0x0a'
)
0
 
wdosanjosCommented:
Try setting ROWTERMINATOR = '\n'.  It seems the telco file was created on a non-Windows environment, so it does not include \r.
0
 
HainKurtSr. System AnalystCommented:
here it is :)

use notepad to edit

BULK INSERT dbo.Table1
FROM 'c:\temp\20120214-20120214.txt'
WITH (
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '\n'
)

the file you posted does not have right eol characters... I opened a notepad, copy [aste the lines and saved it... used a hex editor and I see x0D and x0A characters in it... yours only has one... maybe it is saved as unix file?
20120214-20120214.txt
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
HainKurtSr. System AnalystCommented:
also dont use ; at the end of line, if you do not want to change that, use

ROWTERMINATOR = ';\n'
0
 
ezzadinAuthor Commented:
Thanks.

@wdosanjos: I actually tried \n and forgot to mention in the post. \n inserts all the data into one field (last field in the table)

@HainKurt: Yes, I have also edited this file in notepad and remove that character with space and it works but I'm looking to automate this process, I cannot manually modify these files every day.

Anything else that you guys can think of?

Thanks.
0
 
ezzadinAuthor Commented:
@HainKurt: Good. that actually works but last field contains the data with ;

so how can I set the ROWTERMINATOR to be ;0x0a?

Thanks again
0
 
wdosanjosConnect With a Mentor Commented:
Just add another column to your table (Filler, for example) to "swallow" the last ;
0
 
ezzadinAuthor Commented:
Thanks all.
0
 
HainKurtSr. System AnalystCommented:
@HainKurt: Good. that actually works but last field contains the data with ;

so how can I set the ROWTERMINATOR to be ;0x0a?

I guess you missed the post @ 37788348

also dont use ; at the end of line, if you do not want to change that, use

ROWTERMINATOR = ';\n

so in your case,

ROWTERMINATOR = ';0x0a'

should be fine...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.