Solved

SQL Server - Bulk Inset

Posted on 2014-04-07
10
288 Views
Last Modified: 2014-05-09
I'm trying to import a text to the SQL Server using the following systax:

BULK
INSERT dbo.tblCM_files
FROM 'c:\QDesk\CM\Access2010\CMSTI90.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
);


But I'm getting this error message:

Cannot bulk load because the file "c:\QDesk\CM\Access2010\CMSTI90.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.).

I checked the text file is there in the specified location. Please let me know what I did wrong.
0
Comment
Question by:HNA071252
  • 6
  • 3
10 Comments
 
LVL 6

Expert Comment

by:Dulton
ID: 39984013
Just a guess: The file path specified must be in the context of the Sql Server, not a client PC.
If you do have this file on the actual server that you're trying to load to, and you still do not have permission, check the NTFS permissions on that directory. Make sure the account running the SQL Server Process account has access to this local directory.
0
 

Author Comment

by:HNA071252
ID: 39984021
The text file is in my PC, not in the SQL Server. How do I import the text file that is in my local C drive to the SQL Server?
0
 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 166 total points
ID: 39984029
use Remote Desktop and just copy the textfile over to the sql server :)
0
 
LVL 6

Assisted Solution

by:Dulton
Dulton earned 334 total points
ID: 39984048
you still have to consider the account which is doing the bulk insert. that account needs permission into the directory the file is at.

See the section under "data file" in this link. Also see the Security/Permissions section.
http://technet.microsoft.com/en-us/library/ms188365.aspx


It's very more involved having the file on a client PC.... not impossible, but if it's a 1 time thing, I'd consider the Import/Export wizard.  if it's an angoign requirement, I'd consider finding a way to get the file onto a local share/directory of the sql server. (still considering permissions of wherever you put it)
0
 

Author Comment

by:HNA071252
ID: 39984173
I used Remote Desktop and copied the text file over the SQL Server. Now it's in this folder in the Sql server:

BULK
INSERT dbo.tblCM_files
FROM 'C:\SQL Update_CAP\CM\CMSTI90.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
);

Here's a sample of my text file:

000000111    A110201104    2439     2439.06        1.00         1

And now I'm getting this error (please see attach which has the error below and also the  dbo.tblCM_files table properties)

Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)"
SQL-Server-Bulk-insert.docx
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:HNA071252
ID: 39984230
I'm also not sure what's the FIELDTERMINATOR of my text file.
0
 

Author Comment

by:HNA071252
ID: 39984246
Also please ignore the last four columns in table dbo.tblCM_files, only the first 9 columns need to be appended from the text file.
0
 

Author Comment

by:HNA071252
ID: 39984256
Here's the specs of my CMSTI90.txt file:

00001 FILE=CMSTI90,SUFFIX=FIX
00002 SEGNAME=CMSTI90
00003 FIELDNAME=CLM00_TAX_ID,E01,A9,A09,
00004       MISSING=ON,$
00005 FIELDNAME=CLM00_TAX_ID_SUFFIX,E02,A5,A05,
00006       MISSING=ON,$
00007 FIELDNAME=CLM40_REVENUE_CODE,E03,A3,A03,
00008       MISSING=ON,$
00009 FIELDNAME=CONTYRMO,E04,YYM,A06,$
00010 FIELDNAME=AVERAGE_BILLED_DOLLARS,E05,P8,A08,$
00011 FIELDNAME=TOTAL_BILLED,E06,P12.2,A12,$
00012 FIELDNAME=TOTAL_UNIT,E07,P12.2,A12,$
00013 FIELDNAME=TOTAL_CLAIM_COUNT,E08,P10,A10,$
0
 
LVL 6

Accepted Solution

by:
Dulton earned 334 total points
ID: 39985830
If there are individual spaces in between your fields you may get away with a field delimiter of a few spaces like '   '.... you have to then accept that if that string of spaces is found in the field data, it'll be treated like a delimiter, like it or not.  In your sample data posted above, it looks like there are 4 spaces between the first two fields, but it's hard to say if or how that has changed by posting in this thread.


Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.

.....would indicate to me that your field delimiter is wrong and it is not a tab \t.


I am also wondering if you may run into trouble trying to bulk insert a file that doesn't contain all of the fields of the destination table..... you indicated that you only want to use the first 9 destination columns. you may need a view to bulk insert to. if you do, realize that the view must only be based upon 1 table for a bulk insert.
0
 

Author Comment

by:HNA071252
ID: 40054964
Thanks.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

705 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

20 Experts available now in Live!

Get 1:1 Help Now