SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Bulk insert from excel file to table.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rudie
Starting Member

South Africa
1 Posts

Posted - 07/08/2009 :  09:03:48  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 07/08/2009 :  10:10:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Did you try this?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/08/2009 :  13:33:19  Show Profile  Reply with Quote
are you getting any error? also are your delimiters consistent?
Go to Top of Page

djorre
Yak Posting Veteran

94 Posts

Posted - 07/15/2009 :  09:31:18  Show Profile  Reply with Quote
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?

Edited by - djorre on 07/15/2009 09:34:18
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 07/15/2009 :  11:07:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

djorre
Yak Posting Veteran

94 Posts

Posted - 07/16/2009 :  03:11:41  Show Profile  Reply with Quote
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.

Edited by - djorre on 07/16/2009 03:21:48
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 07/16/2009 :  03:27:09  Show Profile  Visit jezemine's Homepage  Reply with Quote
if you save the xls as a csv or tab-delimited file then you could use bcp or BULK INSERT to import.


elsasoft.org
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000