Solved

SQL Server - Bulk Inset

Posted on 2014-04-07
10
297 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
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.

 
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
 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 63
SSIS how to COMPARE a data column from different servers? 6 110
My Query is not giving correct result. Please help 5 51
Sql Server group by 10 45
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard 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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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