Solved

BULK INSERT WITH FORMATFILE

Posted on 2004-08-23
3
1,234 Views
Last Modified: 2007-12-19
Hi Experts,

I want to insert records from a Comma seperated text file. I want to use FORMATFILE  to ignore Salary Field.

BULK INSERT TestEmp
From 'D:\Code\Refresh\New\SQLScripts\Empltest.txt'
  WITH(FIRSTROW = 2, FORMATFILE = 'd:\Code\Refresh\New\SQLScripts\Empl.fmt')

I am getting the following error:

"Bulk Insert fails. Column is too long in the data file for row 1, column 3. Make sure the field terminator and row terminator are specified correctly."


FORMATFILE - Empl.fmt
===============
8.0
5
1       SQLCHAR       0       10      ","                       1     EmpCode      SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       50      ","                       2     EmpName      SQL_Latin1_General_CP1_CI_AS
3       SQLDATETIME   1       4       ","                       3     DOJ          ""
4       SQLMONEY      1       8       ","                       0     Salary       ""
5       SQLCHAR       0       0       "\r\n"                    5     Comments     SQL_Latin1_General_CP1_CI_AS
-----------------------------------------------------------

TABLE - TESTEMP
===========
CREATE TABLE [TestEmp] (
      [EmpCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [EmpName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DOJ] [smalldatetime] NULL ,
      [Salary] [money] NULL ,
      [Comments] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

TEXTFILE - EMPLTEST.TXT
================

"EmpCode","EmpName","DOJ","Salary","Comments"
"001","Empl-A","10/01/2004","50000.00","AA"
"002","Empl-B","12/01/2004","45000.00","CC"
"003","Empl-C","05/25/2004","25000.00","DD"
"004","Empl-D","06/30/2004","35000.00","EE"
"005","Empl-E","02/28/2004","60000.00","FF"
-----------------------------------

Can you tell me, what's wrong with this formatfile?

Thanks in advance,
sps
0
Comment
Question by:sps2004
[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
3 Comments
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 125 total points
ID: 11878071
FORMATFILE - Empl.fmt
===============
8.0
5
1       SQLCHAR       0       10      ","                       1     EmpCode      SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       50      ","                       2     EmpName      SQL_Latin1_General_CP1_CI_AS
3       SQLDATETIME   1      8      ","                       3     DOJ          ""
4       SQLMONEY      1       8       ","                       0     Salary       ""
5       SQLCHAR       0       0       "\r\n"                    5     Comments     SQL_Latin1_General_CP1_CI_AS
-----------------------------------------------------------

USe This Format file


Why  ? cuz SQL BOL says that :
"
Note  Using default data type sizes (field length) can lead to an "unexpected end of file" error message. This generally occurs with the money and datetime data types when only part of the field occurs in the data file (for example, a datetime value of mm/dd/yy with no time component) rather than an entire string, as expected by SQL Server. When using the default size option, SQL Server expects to read 24 characters (the length of the datetime data type when stored in char format). To avoid this problem, bulk copy data using field terminators, or fixed-length data fields.


Specifying a field length too short for numeric data when bulk copying data causes bcp to print an overflow message and not copy the data. When datetime data is copied to a data file as a character string of less than 26 bytes, the data is truncated without an error message. When creating an ASCII data file, use the default field length to ensure that data is not truncated and that numeric overflow errors causing bcp to fail do not occur. To change the default field length, supply another value.


"
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

707 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