Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server - Bulk Inset

Posted on 2014-04-07
10
Medium Priority
?
303 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 664 total points
ID: 39984029
use Remote Desktop and just copy the textfile over to the sql server :)
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 6

Assisted Solution

by:Dulton
Dulton earned 1336 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 1336 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

Technology Partners: 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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 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