Bulk insert from xls file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Bulk insert from xls file

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2004-11-08 : 02:18:34

hi, i need to import bulk data from excel file. tell me how i can do that i am new to this topic.

here is table structure.

ksedate smalldatetime 4
symbol varchar 5 1
catid numeric 9 1
compname varchar 50
openrate decimal 9
highrate decimal 9
lowrate decimal 9 1
closingrate decimal 9
changerate decimal 9

here is the some sample data

4-Nov-04 ACF 2 Abamco Cap. XB 10 16.1 29.95 10 15500
4-Nov-04 ACF 2 AC 12 15.1 19.95 30 14500
4-Nov-04 ACF 3 co Cap. XB 11 14.1 39.95 40 13500
4-Nov-04 ACF 4 Abamco Cap. XB 32 11.1 59.95 10 12500
4-Nov-04 ACF 5 Abamco Cap. XB 33 12.1 49.95 20 12500

i tried to use following code but getting error.
BULK INSERT ksedata FROM 'c:\kse\closing.xls'

error:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 1 (ksedate).

kindly tell me how to do that.

Kristen
Test

22859 Posts

Posted - 2004-11-08 : 02:24:35

You are probably better off using DTS so you can see the column allocations etc.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-08 : 03:50:12

I was not aware that you can BULK INSERT from a .xls file !?

Ok, the file extension doesn't have to mean that it is an invalid format in the source file,
and the sample data you provided suggests that it is an ordinary textfile.
Unless you copy/pasted from the excel sheet.

Anyway you have to provide the FIELDTERMINATOR and ROWTERMINATOR arguments in your BULK INSERT command.
( unless of course they are the default '\t' and '\n' characters )
I usually specify CODEPAGE = 'RAW' as well.

rockmoose

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-08 : 07:13:28

you would have to use openrowset with the jet data provider to convert an .xls file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources