Solved

Bulk Insert MS SQL 2008

Posted on 2012-03-30
9
583 Views
Last Modified: 2012-03-30
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
0
Comment
Question by:ezzadin
  • 4
  • 3
  • 2
9 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37788223
Try setting ROWTERMINATOR = '\n'.  It seems the telco file was created on a non-Windows environment, so it does not include \r.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 37788337
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 37788348
also dont use ; at the end of line, if you do not want to change that, use

ROWTERMINATOR = ';\n'
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:ezzadin
ID: 37788505
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
 
LVL 51

Accepted Solution

by:
HainKurt earned 450 total points
ID: 37788697
ok, use this :)

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

Author Comment

by:ezzadin
ID: 37789023
@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
 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 50 total points
ID: 37789115
Just add another column to your table (Filler, for example) to "swallow" the last ;
0
 

Author Closing Comment

by:ezzadin
ID: 37789183
Thanks all.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 37789285
@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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

822 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