Bulk insert from excel file to table.

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

Transact-SQL (2000)

Bulk insert from excel file to table.

Author

Topic

Rudie
Starting Member

1 Post

Posted - 2009-07-08 : 09:03:48

Hi all,

I need some help. I am trying to insert data from an excel file to a table using the bulk insert command. The command executes successfully but there is no data inserted to the table. Here is the code which I am using:

bulk insert Sonar2.dbo.PaymentsQArrangementsInputRaw FROM 'R:\WorkingFolder\lstArrangements.xls' WITH (FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n', FIRSTROW = 2)

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-08 : 10:10:44

Did you try this?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 13:33:19

are you getting any error? also are your delimiters consistent?

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-07-15 : 09:31:18

I import my tables from one excel file like this:

ALTER PROCEDURE [dbo].[sp_SetReportTablesExcel] AS
BEGIN

delete from tbl_Report
Insert INTO tbl_Report
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\sql_report_tables.xls', [tbl_Report$])

delete from tbl_ReportingSiteMap
Insert INTO tbl_ReportingSiteMap
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\sql_report_tables.xls', [tbl_ReportingSiteMap$])

...

I think BULK insert only works for BULK files not excel?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-15 : 11:07:31

quote:
Originally posted by djorre

I import my tables from one excel file like this:

ALTER PROCEDURE [dbo].[sp_SetReportTablesExcel] AS
BEGIN

delete from tbl_Report
Insert INTO tbl_Report
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\sql_report_tables.xls', [tbl_Report$])

delete from tbl_ReportingSiteMap
Insert INTO tbl_ReportingSiteMap
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\sql_report_tables.xls', [tbl_ReportingSiteMap$])

...

I think BULK insert only works for BULK files not excel?

Did your procedure execute without error?

Madhivanan

Failing to plan is Planning to fail

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-07-16 : 03:11:41

Sorry I use it in SQL 2005 don't know if it works in 2000...

There it works fine. But you have to make sure the excel files have no 'empty' rows, so sometimes I need to select all rows except the table rows and than delete them, even though they looked empty. And of course the number of columns need to be the same i guess. And the first excel row needs to be empty/headers.

It is very usefull to edit the tables in excel because if you work with id's and you need to insert a record in the midle than you can easely add that id in the centre and edit the next id's by typical excel drag down, so everything in the table keeps having a nice order (cfr sitemap). copy pasting is also easier, and it is faster to navigate trough the different tables.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-07-16 : 03:27:09

if you save the xls as a csv or tab-delimited file then you could use bcp or BULK INSERT to import.

elsasoft.org

sgondesi
Posting Yak Master

200 Posts

Posted - 2014-12-10 : 04:23:19

quote:
Originally posted by madhivanan

Did you try this?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Sorry for delayed response.
When I tried to use the below query, I am getting an error saying "This will not work with distributed queries". Any idea?

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Junior SQL Server DBA,
Miracle Software systems, Inc.

sgondesi
Posting Yak Master

200 Posts

Posted - 2014-12-10 : 04:29:13

quote:
Originally posted by visakh16

are you getting any error? also are your delimiters consistent?

Yes I am getting an error like data types are incompatible.
But when I tried to do the same work with separate ".dat" files.
But when I am working with .xls files, I am unable to.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Junior SQL Server DBA,
Miracle Software systems, Inc.

sgondesi
Posting Yak Master

200 Posts

Posted - 2014-12-10 : 04:30:57

quote:
Originally posted by djorre

I import my tables from one excel file like this:

ALTER PROCEDURE [dbo].[sp_SetReportTablesExcel] AS
BEGIN

delete from tbl_Report
Insert INTO tbl_Report
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\sql_report_tables.xls', [tbl_Report$])

delete from tbl_ReportingSiteMap
Insert INTO tbl_ReportingSiteMap
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\sql_report_tables.xls', [tbl_ReportingSiteMap$])

...

I think BULK insert only works for BULK files not excel?

When I tried to do in your way, I am getting an error saying "This will not work in distributed queries"

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Junior SQL Server DBA,
Miracle Software systems, Inc.

sgondesi
Posting Yak Master

200 Posts

Posted - 2014-12-10 : 04:36:02

quote:
Originally posted by jezemine

if you save the xls as a csv or tab-delimited file then you could use bcp or BULK INSERT to import.

elsasoft.org

The method which you have suggested is very tuff to me. Because each tab in my xl sheet has around 100 fields.

--
Thanks and Regards
Srikar Reddy Gondesi,
Junior SQL Server DBA,
Miracle Software systems, Inc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources