Converting Excel File to SQL - 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

General SQL Server Forums

New to SQL Server Programming

Converting Excel File to SQL

Author

Topic

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-02 : 02:32:05

I want to conver ta excel file int o a sql file how can i do that.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-02 : 02:45:16

you can use DTS to import a excel file into a table

-----------------
[KH]
Learn something new everyday

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-02 : 03:08:18

or

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-02 : 05:42:49

i treid using DTS but i get error while transferring, the process stops in beteween and diplays error saying
The log File for database is full.Back up the transaction log for the database to free up some log space.
How to clear the transaction log?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-02 : 06:34:36

use

backup log dbname with truncate_only

or

sp_dboption 'YourDatabase','trunc. log on chkpt','True'

or

dump tran YourDatabase with no_log

also read this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56434

-----------------
[KH]
Learn something new everyday

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-02 : 07:04:51

iam still getting the same error after some data is transfered.
i get syntax error with backip log dbname with truncate_only.
the error in syntax near With truncate_only.

i have a excel file which has one column and around 65000 rows.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-02 : 07:20:31

Do you have sufficient space for your log file to growth ? Did you limit the size of the log file ?

-----------------
[KH]
Learn something new everyday

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-02 : 07:40:05

how do i know that?

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-02 : 07:58:46

it shows that error occurs at second last row of the column.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-02 : 08:01:16

Use Enterprise Manager, right click on your Database and choose property. Make sure automatic growth is checked and there isn't any max log file size. Also check the disk space available where the log file is located.

-----------------
[KH]
Learn something new everyday

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-02 : 08:07:49

now i get the the error primary work group is full

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-02 : 08:25:42

you mean primary filegroup ? looks like you might not have sufficient free diskspace for both your data & log file.

-----------------
[KH]
Learn something new everyday

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-03 : 01:51:13

Iam uusin this query in my page
"Insert into Random Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\book1.xls;HDR=NO','SELECT * FROM [Sheet1$]')"

but it returns error saying

Could not process object 'SELECT * FROM [Sheet1$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.

what to do ?which columns is it talking abt i have column in both tables

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 02:36:25

Make sure you used correct SheetName and the file has headers

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-03 : 04:16:48

yes the sheet name is correct and excel file has header as well(i think u r taking abt header and footer in the sheet)still iget the same error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 04:43:47

If the first row has headers then you need to specify HDR=YES

"Insert into Random Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\book1.xls;HDR=YES','SELECT * FROM [Sheet1$]')"

Madhivanan

Failing to plan is Planning to fail

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-03 : 05:03:10

Still getting same error
Could not process object 'SELECT * FROM [Sheet1$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.

here is stack trace if u want to see that
Stack Trace:

[SqlException: Could not process object 'SELECT * FROM [Sheet1$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.]
System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
WebApplication2.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\WebApplication2\WebForm1.aspx.vb:45
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()

hotshot_21
Yak Posting Veteran

97 Posts

Posted - 2006-01-04 : 02:26:19

Is Thereany sql statement which i can use to convert one form of database to another format.
without using DTS or BCP.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-04 : 03:10:47

Look for OpenRowSet, OpenDataSource, Linked Servers in Books On Line, SQL Server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources