bulk import help please

Question

bulk import help please

RightOnTarget

Hall of Fame

Points: 3041
More actions
October 25, 2012 at 12:01 pm

#283565

Hi all,
I need to be able to import foreight language data into sql server 2K8R2 from csv file.
Here is the table:
CREATE TABLE [dbo].[ept](
[f_id] [int] NULL,
[t_id] [int] NULL,
[l_id] [nvarchar](2) NULL,
[t] [nvarchar](max) NULL,
[a_exp] [char](1) NOT NULL,
[dep] [char](1) NOT NULL,
[f] [char](1) NOT NULL,
)
Here is the sample of the file:
872,89801,'cs','Vyberte pole, podle kterého chcete vyhledávat a pak zadejte několik prvních písmen hledat.','y','n','n'
872,89805,'cs','Maximální počet zaměstnanců byly vráceny. Výsledky hledání omezit vrácené výsledky.','y','n','n'
Here is the statement:
BULK
INSERT ep_tran
FROM 'c:\temp\ep\ep.csv'
WITH
(FIELDTERMINATOR = ',', ROWTERMINATOR = '')
Here is the 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 (l_id).
If I remove single quotes(and commas in 4th column) - it works, but I can't do it as the file is too large.
Is there a way to tell SQL Server to look for values enclosed in single quotes,
similar to what Oracle has in its external tables?
Thanks,

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SQL_Loser SSC Eights! Points: 829 More actions · Answer 1

I am not sure what you mean but if you have SSIS you can build a package that will import multiple CSV files into a table.

Lowell SSC Guru Points: 323518 More actions · Answer 2

your data is not just split by commas, is the issue; they are delimited or "Text Qualifed" with single quotes.

to handle taht gracefully, I think it is much easier to use the Import export wizard in this case, as you can identify the Text Qualifier.

Otherwise, if you are going to stick with BULK INSERT, you will need to use a format file, so you can show that certain fields are spit by either a comma, or singlequote-comma

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 3

I don't use SSIS, but I tried Import Wizard and ... no luck.

Here is what I've got:

Executing (Error)

Messages

Error 0xc002f210: Drop table(s) SQL Task 1: Executing the query "drop table [dbo].[ep]

" failed with the following error: "Cannot drop the table 'dbo.ep', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.