September 28, 2016 at 1:34 pm
Hello All,
Trying to do bulk insert in SQL 2014 Express but receiving an error. Seems like the process fails on the first float value of the table it is uploading to.
I am using excel office 2013 and save as CSV with "," delimitor.
Have already removed any commas with "_" before processing
Change my file extension to a txt before processing
Error:
Msg 4864, Level 16, State 1, Line 6
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 7 (NEW_PARTS_TERM).
Sample Import File is:
ID,DEALER_NUM,DEALER_NAME,NEW_WARRANTY_SKU_NUM,NEW_WARRANTY_SKU_NAME,NEW_PARTS_TERM,EARNING_TYPE,PROGRAM_TYPE,PURCHDT,FAILDT,CLAIMCNT,TOTAL_LOSSES
1,1060,Officemax,189400,OM 2YR ADH PREM SVC $450-$549.99 P,24.00,U,SERVICE,4/1/2015,8/1/2015,1.00,341.16
2,1060,Officemax,189400,OM 2YR ADH PREM SVC $450-$549.99 P,24.00,U,SERVICE,4/1/2015,9/1/2015,1.00,181.16
*** Executing Stored Procedure ***
Exec [dbo].[BulkInsertFiles] 'Losses', 'C:\Users\hb\Desktop\FileUpload\', 'AllCNA_LossesDataThru_Aug-2016_Purch.txt'
*** Stored Procedure Code ***
ALTER PROCEDURE [dbo].[BulkInsertFiles]
-- Add the parameters for the stored procedure here
@index nvarchar(20),
@PATH nvarchar(100),
@FILENAME nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Varaibles
DECLARE @FULLPath nvarchar(MAX)
DECLARE @QUERY nvarchar(MAX)
-- Insert statements for procedure here
SET @FULLPATH = '''' + @Path + @FILENAME +''''
SET @QUERY = 'BULK INSERT ' + @index +
' FROM' + @FULLPATH + ' WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '','',
ROWTERMINATOR = '''',
TABLOCK
)';
--Print @query;
EXEC(@query);
END
*** Table Schema ***
CREATE TABLE [dbo].[Losses](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DEALER_NUM] [nvarchar](200) NULL,
[DEALER_NAME] [nvarchar](200) NULL,
[UNDERWRITER] [nvarchar](50) NULL,
[NEW_WARRANTY_SKU_NUM] [nvarchar](200) NULL,
[NEW_WARRANTY_SKU_NAME] [nvarchar](200) NULL,
[NEW_PARTS_TERM] [float] NULL,
[EARNING_TYPE] [nvarchar](50) NULL,
[PROGRAM_TYPE] [nvarchar](200) NULL,
[PURCHDT] [date] NULL,
[FAILDT] [date] NULL,
[CLAIMCNT] [float] NULL,
[TOTAL_LOSSES] [float] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
September 28, 2016 at 4:39 pm
Have a look at the columns in your table and compare them to the fields in your text file.
The rowterminator looks dodgy too.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply