Solved

Bulk Insert MS SQL 2008

Posted on 2012-03-30
9
581 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
Comment Utility
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
Comment Utility
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
Comment Utility
also dont use ; at the end of line, if you do not want to change that, use

ROWTERMINATOR = ';\n'
0
 

Author Comment

by:ezzadin
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 51

Accepted Solution

by:
HainKurt earned 450 total points
Comment Utility
ok, use this :)

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

Author Comment

by:ezzadin
Comment Utility
@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
Comment Utility
Just add another column to your table (Filler, for example) to "swallow" the last ;
0
 

Author Closing Comment

by:ezzadin
Comment Utility
Thanks all.
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
@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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now