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

build sql script in excel

Hi,
I have been given an excel spreadsheet with two columns with data.
There are about 20 records in this excel sheet.
Would like to write an insert query to insert these data.
I am thinking of writing an insert query for the first line iin the excel sheet and then drag it down to the last row of data so that it automatically writes the values of the columns in the insert query and i just copy and paste the script into sql to run.
This is what I have but the values of the cells do not get reflected.
Any thoughts pls?
'insert into TBLData (RE_ID, EMAIL) values (' & c2 & ',' & d2 ')'I used a similar thing just today.

I wrote the first cell as:
"INSERT INTO into TBLData (RE_ID, EMAIL) "

then dragged the following formula down:

=" SELECT '" & c2 & "', '" & d2 & "' UNION ALL "

Copy -> Paste to QA and remove the last UNION ALL

HTH