Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 Tags
Subject: BULK INSERT from CSV file errors
Thread ID: 395928 Message ID: 395928 # Views: 44 # Ratings: 0
Version: Not Applicable Category: Databases, Tables and SQL Server
Date: Tuesday, January 21, 2014 2:42:41 PM         
   


Well, I am not sure how to handle the errors, which center on the GrpNo and EMP_SSN fields, both of which do not have double quotes around the data in each of their fields. The errors are as follows, regarding data types conflicting:

TWM0042 has no quotes around it and this BULK INSERT fails for some reason due to a data type issue. It may assume that everything that is string data must have double quotes. In this CSV, not all character or string data has double quotes surrounding the data.

Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (GrpID).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (Emp_SSN).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 3 (Emp_SSN).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 3 (Emp_SSN).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 3 (Emp_SSN).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 3 (Emp_SSN).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 3 (Emp_SSN).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 3 (Emp_SSN).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 14, column 3 (Emp_SSN).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 15, column 3 (Emp_SSN).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 17, column 3 (Emp_SSN).
Msg 4865, Level 16, State 1, Line 2
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


Here's the T-SQL: Code to create the table I am importing data into:
USE GA
DROP TABLE dbo.WebTPA_INPUT_File
create table dbo.WebTPA_INPUT_File (
	GrpNo nvarchar(10), Ins_Name nvarchar(50), Emp_SSN nvarchar(9), MemberID nvarchar(12),
	FirstName nvarchar(15), LastName nvarchar(25), Mid_Init nchar(1), Gender nchar(1),
	Dep_SSN nvarchar(9), cRelation nvarchar(6), DOB date, Age int, Age_Band int, 
	Eff_Date date, Term_Date date, cStatus nchar(1), StatBegDt date, Benefits nvarchar(60),
	Med_Tier nvarchar(2), Den_Tier nvarchar(2), GrpCntrct nvarchar(30), Dept nchar(1), 
	Location nchar(1), Address1 nvarchar(30), Address2 nvarchar(30), City nvarchar(25), 
	cState nvarchar(2), ZipCode nvarchar(5), Hire_Date date)


Here's the BULK INSERT command in T-SQL:
use GA
BULK
INSERT dbo.WebTPA_INPUT_File
FROM "K:\Data\Common\Feed\In\TWM0042_20140116.CSV"
WITH
(
FIELDTERMINATOR=',',
ROWTERMINATOR='\n'
Cecil Champenois, Jr.

ENTIRE THREAD

BULK INSERT from CSV file errors Posted by Cecil Champenois @ 1/21/2014 2:42:41 PM
RE: BULK INSERT from CSV file errors Posted by Cetin Basoz @ 1/21/2014 3:46:45 PM
RE: BULK INSERT from CSV file errors Posted by Gene Wirchenko @ 1/21/2014 6:44:49 PM