Solved

Bulk Insert MS SQL 2008

Posted on 2012-03-30
9
584 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
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

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TOOLS - convert T-SQL TO PL/SQL 3 53
Help Required 3 106
Need a SQL query that creates a header row and one or more detail rows. 7 50
sql 2008 how to table join 2 26
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

789 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