sps2004
asked on
BULK INSERT WITH FORMATFILE
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\SQLSc ripts\Empl test.txt'
WITH(FIRSTROW = 2, FORMATFILE = 'd:\Code\Refresh\New\SQLSc ripts\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.0 0","AA"
"002","Empl-B","12/01/2004 ","45000.0 0","CC"
"003","Empl-C","05/25/2004 ","25000.0 0","DD"
"004","Empl-D","06/30/2004 ","35000.0 0","EE"
"005","Empl-E","02/28/2004 ","60000.0 0","FF"
-------------------------- ---------
Can you tell me, what's wrong with this formatfile?
Thanks in advance,
sps
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\SQLSc
WITH(FIRSTROW = 2, FORMATFILE = 'd:\Code\Refresh\New\SQLSc
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_
2 SQLCHAR 0 50 "," 2 EmpName SQL_Latin1_General_CP1_CI_
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_
--------------------------
TABLE - TESTEMP
===========
CREATE TABLE [TestEmp] (
[EmpCode] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_
[EmpName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
[DOJ] [smalldatetime] NULL ,
[Salary] [money] NULL ,
[Comments] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
) ON [PRIMARY]
GO
TEXTFILE - EMPLTEST.TXT
================
"EmpCode","EmpName","DOJ",
"001","Empl-A","10/01/2004
"002","Empl-B","12/01/2004
"003","Empl-C","05/25/2004
"004","Empl-D","06/30/2004
"005","Empl-E","02/28/2004
--------------------------
Can you tell me, what's wrong with this formatfile?
Thanks in advance,
sps
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.