LOADING DATA FROM EXCEL TO MS SQL SERVER 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

Import/Export (DTS) and Replication (2000)

LOADING DATA FROM EXCEL TO MS SQL SERVER TABLE

Author

Topic

getsaby
Starting Member

11 Posts

Posted - 2006-08-24 : 10:10:22

Hello all:

I have gone through most of the question posed by people, about importing data from EXCEL sheet to some table using SQL server database. I have a slight variation of this problem.

My excel file contains some information apart from the normal data.
Lets say some 5-6 lines always gives me some info about the data, like its purpose, client info, date etc...
After this INFO my actually data start, which I want to load into table.

I have found some wizard for the same, "EMS SQL MANAGER 2005", which supports most of the file formats, and load data into the database.

But we are planning to not use this tool, instead everything should be done using TSQL.

If somebody can please gives me some idea how this problem can be tackle, it would be a great help. We won't be using any Third party tools, like scripting etc...

We want to stick with just TSQL for this problem.

Thanks a bunch,
sabegh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-24 : 10:32:29

See if this helps
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

hellilyntax
Starting Member

19 Posts

Posted - 2006-08-27 : 04:51:20

hi guys,
im new in programmin and dont quite understand much what u r discussing in he. however i guess my problem is something to do with this topic.
here is my problem.
i upload few excel files to my webserver at hosting company and need to export the excel rows to sql server on their side to be displayed in my website. i cant use DTS because of some restriction from them. so i try to use TSQL which read from every row in exvel and insert into SQL Server table. i tried to execute the TSQL using asp codes from wensite but the connection always timed out because the rows is too many...up to 10000.
do you have any idea to speed up the transaction?

amitsyadav
Starting Member

15 Posts

Posted - 2006-08-30 : 08:48:41

hi,

i am facing some problem when i try to import data from excel to SQL Server.
after executing these lines:

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'H:\DPProject\Script\Data\20060828\Data_from_DEV_ON_20060809.xls',
NULL,
'Excel 8.0'
GO

SELECT code FROM ExcelSource...[UserInput$]

i am getting this error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

please help me to understand this error. is there any error in sql code?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 08:59:00

Try this, as suggested in [url]http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926[/url].

SELECT	f1 AS Code
FROM	OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 5.0;HDR=No;IMEX=0;Database=H:\DPProject\Script\Data\20060828\Data_from_DEV_ON_20060809.xls', 'select * from [Sheet1$a1:q50]')

If you still get errors, try this.

SELECT f1 AS Code FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=H:\DPProject\Script\Data\20060828\Data_from_DEV_ON_20060809.xls', 
    'SELECT * FROM [Sheet1$]')

Peter Larsson
Helsingborg, Sweden

amitsyadav
Starting Member

15 Posts

Posted - 2006-08-30 : 09:05:37

hi peter,
thanks for your reply.

now i am getting error as:
Server: Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.

and one more thing, i do not have proper rights in SQL Server. i have made some changes in the sp_addlinkedserver to execute this procedure.

waiting for quick reply.

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-12-02 : 23:30:24

hi all,
i have used following query

SELECT * into EMPBOOK
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\mySERVER\PROJECTS\EMPBOOK.xls;',
'SELECT * FROM [sheet1$]')

Server: Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 01:21:45

You can find answer here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

TVMom
Starting Member

4 Posts

Posted - 2009-01-27 : 13:47:49

I am encountering a different error along the same lines. I have an Excel spreadsheet that contains data I want to import into existing SQL tables and have written a script to do so. For the most part, I've been successful but I cannot seem to overcome the issue of leading zeros in text field and text fields that contain a value of zero. The import drops leading zeros and converts fields containing only zeros to null. I've checked the formatting on my Excel fields and SQL fields and both are set to text. Anyone have a way of overriding this conversion?

TVMom
Starting Member

4 Posts

Posted - 2009-01-28 : 11:29:30

Discovered a solution to my issue...posted it here:

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources