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
Solved

Can't find error on a bulk insert of a CSV file to SQL Server Management Studio Express (2005)

Posted on 2008-09-30
7
1,393 Views
Last Modified: 2012-05-05
I am trying to do a BULK INSERT of a .csv file in to SQL Server 2005, but I am getting two errors that I can't seem to fix.  I am using the free version of SQL Server Management Studio.  

Here is my insert statement:

BULK INSERT Temp.dbo.TO_Product
FROM 'C:\Temp\dowto_db_temp.csv'
WITH
      (FIELDTERMINATOR = ',',
       ROWTERMINATOR = '\n')

Here are the two errors I am getting:

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 1 (nID).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 124, column 156 (Pertains_to_Dimension).

I have attached my .csv file in the form of .xls (.csv not allowed).  And i added the snippet of my table schema.  After saving it as .xls, I tried to bulk insert that, and received these errors:

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 1 (nID).
Msg 4866, Level 16, State 8, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 174. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

If anyone has any thoughts, i would GREATLY appreciate it, it's driving me batty!
CREATE TABLE [dbo].[TO_Product](
	[nID] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Display_Title] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Photo_File_Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Headline_1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Headline_2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Headline_3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Headline_4] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_4] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_5] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_6] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_7] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_8] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_9] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_10] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_11] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_12] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_13] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_14] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_15] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_16] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_17] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_18] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_19] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_20] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_21] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_22] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Subhead_23] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_1] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_2] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_3] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_4] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_5] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_6] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_7] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_8] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_9] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_10] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_11] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_12] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_13] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_14] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_15] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_16] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_17] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_18] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_19] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_20] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_21] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_22] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_23] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_24] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_25] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_26] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_27] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_28] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_29] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_30] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_31] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_32] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_33] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_34] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_35] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_36] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_37] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_38] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_39] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_40] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_41] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_42] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_43] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_44] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_45] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_46] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_47] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_48] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_49] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_50] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_51] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_52] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_53] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_54] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_55] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_56] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_57] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_58] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_59] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_60] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_61] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_62] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_63] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_64] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_65] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_66] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_67] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_68] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_69] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_70] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_71] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_72] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_73] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_74] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_75] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_76] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_77] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_78] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_79] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_80] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_81] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_82] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_83] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_84] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_85] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_86] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_87] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_88] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_89] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_90] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_91] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_92] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_93] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_94] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_95] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_96] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_97] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_98] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_99] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_100] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_101] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_102] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_103] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_104] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_105] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_106] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_107] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_108] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_109] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_110] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_111] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_112] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_113] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_114] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_115] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_116] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_117] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_118] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_119] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_120] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_121] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_122] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Copyblock_123] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Confront] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Dimension] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Gallery] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Goal] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Kerb] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Lockup] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Lontrel] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Sapphire] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Scythe] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Showcase] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Snapshot] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Teampro] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Turflonester] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Dithane] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Eagle] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Fore] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Conserve] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Dursban] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Mach2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Pertains_to_Curfew] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__TO_Product__0FA2421A]  DEFAULT ((0)),
 CONSTRAINT [TO_Product$PrimaryKey] PRIMARY KEY CLUSTERED 
(
	[nID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Open in new window

dowto-db-temp.xls
0
Comment
Question by:tarashea
  • 4
  • 3
7 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 22606682
Try by creating a linokles server to excel specifying the path ahd file name to the excel file. If the serever is local use a local path(folder) if the server is remote use a network path.

I tested it and I could select the data from the file. If the data is what you expected you can just insert it to a temp table or directly to your destination table.
EXEC sp_addlinkedserver ExcelFile,
     'Jet 4.0',
     'Microsoft.Jet.OLEDB.4.0',
     'file_path_here\dowto-db-temp.xls', 
      NULL,
     'Excel 5.0;'
GO
 
--Set up login mappings (just ADMIN - jet wants something).
EXEC sp_addlinkedsrvlogin ExcelFile, FALSE, NULL, Admin, NULL
GO
 
--List the tables in the linked server (these are the worksheet names).
EXEC sp_tables_ex ExcelFile
GO
 
 
select * 
--into #tbl				-- to create a temp table on the fly on the server
from ExcelFile...dowto_db_temp$
 
-- select * from #tbl	-- if the #tbl temp table was created
 
GO
-- remove linked server
sp_dropserver 'ExcelFile', 'droplogins'
GO

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22606696
Bad misstype, i meant: "Try by creating a linked server..."
0
 

Author Comment

by:tarashea
ID: 22607703
so everywhere you listed "ExcelFile" i need to replace that with my file name???
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:tarashea
ID: 22607784
Zberteoc - I removed the one row that was giving me problems above, and the error no longer comes up, maybe just some quirky formatting in my csv file???  not sure.  But I still get the nID primary key 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 1 (nID).

Is there some logic around inserting primary key fields???  

this was the column: [nID] [int] IDENTITY(1,1) NOT NULL,
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 22607940
You cannot insert values to an IDENTITY column unless you specify

SET INSERT_IDENTITY ON

run the import  and then

SET INSERT_IDENTITY OFF

However if that column is populated from the excel file and will have duplicates values then you could get in primary key violation error.
0
 

Author Comment

by:tarashea
ID: 22608049
would the INSERT_IDENTITY method be with removing the nID column from my csv file before insert?  

with your method, do i replace all the "ExcelFile" entries with my file name?  

Just trying to find one method that works for me!  with your method, i received this error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelFile" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelFile".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelFile" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelFile".

With the INSERT_IDENTITY method, i received this error:

Msg 195, Level 15, State 5, Line 1
'INSERT_IDENTITY' is not a recognized SET option.
Msg 195, Level 15, State 5, Line 9
'INSERT_IDENTITY' is not a recognized SET option.

I'm a total newbie, so I'm obviously doing plenty wrong....
0
 

Author Comment

by:tarashea
ID: 22608103
oh, i see it's IDENTITY_INSERT....trying it now.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql help 8 58
Loops and updating in SQL Query 9 54
delete the first occurence of a duplicate row in sql 5 38
IIF in access query 19 25
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 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