June 10, 2014 at 7:14 am
Hi All -
What I am looking to do is use a Bulk Insert to import a cvs file to a table. This table is for zip code rates (tax's based on zip codes). When do so using this code:
Bulk insert ziptaxrate
from 'C:\ziptest.csv'
with
(fieldterminator = ',',
rowterminator = '')
GO
I then get the following error:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (TaxClassID).
This is the table build (also attached) :
USE [Store1]
GO
/****** Object: Table [dbo].[ZipTaxRate] Script Date: 06/09/2014 09:39:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ZipTaxRate](
[ZipTaxID] [int] IDENTITY(1,1) NOT NULL,
[ZipCode] [nvarchar](10) NOT NULL,
[TaxClassID] [int] NOT NULL CONSTRAINT [DF_ZipTaxRate_TaxClassID] DEFAULT ((1)),
[TaxRate] [money] NULL,
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_ZipTaxRate_CreatedOn] DEFAULT (getdate()),
CONSTRAINT [PK_ZipTaxRate] PRIMARY KEY CLUSTERED
(
[ZipTaxID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Attached are screen shots also and the csv test file.
Any help would be great.
June 10, 2014 at 7:26 am
Hello and welcome to the forums.
In your table definition, column 3 is TaxClassID and is an integer. In your data file, column 3 is a decimal numeric.
If I'm interpreting it correctly, it looks like you want to start with column 1 being ZipCode, but the BULK INSERT statement inserts into all columns in the table. Normally, I create a staging table to receive the data and the columns exactly match the text file I'm going to import.
To process the file, I start out by truncating the staging table, then firing the bulk insert, then doing what I need to do with the data. In your case, you'll want to insert from your staging table into your permanent table. You can then compare the staging table against the production table to make sure everything is what you expect it to be.
HTH
June 10, 2014 at 7:28 am
Ed Wagner (6/10/2014)
Hello and welcome to the forums.In your table definition, column 3 is TaxClassID and is an integer. In your data file, column 3 is a decimal numeric.
If I'm interpreting it correctly, it looks like you want to start with column 1 being ZipCode, but the BULK INSERT statement inserts into all columns in the table. Normally, I create a staging table to receive the data and the columns exactly match the text file I'm going to import.
To process the file, I start out by truncating the staging table, then firing the bulk insert, then doing what I need to do with the data. In your case, you'll want to insert from your staging table into your permanent table. You can then compare the staging table against the production table to make sure everything is what you expect it to be.
+1
You forgot about the identity column, so you are trying to put money values in the integer column.
Either use a staging table like Ed suggested, or create a format file.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 10, 2014 at 8:29 am
Thanks to all I got it work!! 🙂
June 10, 2014 at 8:50 am
Good deal. Thanks for the feedback.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply