Bulk Insert of excel sheet

Hi

I need to bulk insert a excel sheet into a sql 2005 db datatable. I have to do this with three different excel files, inserting them into three different tables (each excel file has one sheet). This works like a charm for two of them, one excel file is causing troubles, as data types of the columns of the inserted data sheet are 'ntext'. This ntext declaration is causing problems within my app where I access that table.

So, any idea where I can set what datatype the columns of an inserted excel sheet should be within the sql datatable? I need the columns to be varchar(255) as it is by doing this with the two other excel files. The excel file causing troubles is being generated by another app.

Any help would be much appreciated!

t-sql code:

USE KOMAX
GO

EXEC sp_dboption Komax, 'select into/bulkcopy',True
EXEC sp_dboption Komax, 'ansi_nulls',True
EXEC sp_dboption Komax, 'ansi_warnings',True
GO

-- Delete existing Table
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Messages')
DROP TABLE Messages
-- Insert Excel Sheet
SELECT * INTO Messages FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\Messages.XLS', [Messages$])
GO

EXEC sp_dboption Komax, 'select into/bulkcopy',False
EXEC sp_dboption Komax, 'ansi_nulls',False
EXEC sp_dboption Komax, 'ansi_warnings',False
GO

Best Regards
Benjamin

Capone wrote:

The excel file causing troubles is being generated by another app.

I would say you've already found the problem.

Adamus

|||But it somehow has to be possible to define what datatype the excel columns are after inserted in sql!|||

Of course there are many ways.

Traditionally:

ALTER TABLE MyTable

ALTER COLUMN MyField varchar(255)

Adamus

|||That's a way, right. I'm just trying to figure out where this ntext is coming from and where to change that behaviour. My concern is: what if one of the other fil starts causing this problem as well? For example: if the 3rd part app which generates this files changes just something? I prefer a safe solution...

I don't wanna alter my whole database after a bulk insert.

But thanks a lot for your posts though!|||

Is the application creating the table (at runtime) before the insert?

If so, the datatype is hardcoded into the application. You can make the change there.

If the table already exists, change the datatype with the previous posted code and that should resolve the issue. There shouldnt' be a conversion problem frm ntext to varchar

Adamus