Solved

SQL Server - Bulk Inset

Posted on 2014-04-07
10
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 96
CREATE DATABASE ENCRYPTION KEY 1 85
Can I skip a node in XML? 9 46
How to create and use encrypted columns in SQL 2005? 15 63
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 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