• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

SQL Server - Bulk Inset

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
HNA071252
Asked:
HNA071252
  • 6
  • 3
3 Solutions
 
DultonCommented:
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
 
HNA071252Author Commented:
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
 
plusone3055Commented:
use Remote Desktop and just copy the textfile over to the sql server :)
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
DultonCommented:
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
 
HNA071252Author Commented:
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
 
HNA071252Author Commented:
I'm also not sure what's the FIELDTERMINATOR of my text file.
0
 
HNA071252Author Commented:
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
 
HNA071252Author Commented:
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
 
DultonCommented:
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
 
HNA071252Author Commented:
Thanks.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now