How to transfer the data from Excel to SQL2005

Question

How to transfer the data from Excel to SQL2005

Viewing 5 posts - 46 through 49 (of 49 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I realize that you have only been a member of SSC for about six months, but in this time I would have hoped you would have learned something about SQL Server. And what I mean by this is how to read Books Online for the information you are looking for, or to use Google Search to find other lines of information regarding SQL Server and how to resolve the issues you have encountered.

How to go about basic problem resolution, interpreting error messages and figuring out what they mean (or may mean).

We here at SSC are here to help, but it seems like we are doing your work for you and you aren't learning how to do any of the things you need to do in order to be successful in this career field.

I am sorry, but it makes me wonder how you managed to get where you are today. We need you to start taking the lead in resolving your problems and issues. Instead of just jumping on SSC and posting a problem, please take the some time to research your problem first. Try resolving it on your own, and if that fails, then come and ask for help.

When you do, tell us what the problem is your are having (should be concise, yet detailed enough to tell us what is wrong), what you have done so far in trying to resolve the problem or issue (show us the code and what you have found regarding the problem/issue) and what the results of these attempts have been.

If you'd like to know more on the "Why" you should do those, please read this blog entry, The Flip Side

The key to getting the best help from this site is to help us help you, don't make us do your work for you.

Ike-844273 SSC Enthusiast Points: 109 More actions · Answer 2

I am guessing SSIS is the way to go, but FYI I've previously done this by setting up the Excel file as a LINKED SERVER and then immediately dropping it:

DECLARE @rc INT

DECLARE @server VARCHAR(128)

DECLARE @lnk_server_nm VARCHAR(128)

DECLARE @srvproduct VARCHAR(128)

DECLARE @provider VARCHAR(128)

DECLARE @datasrc VARCHAR(4000)

DECLARE @datasrc_folder VARCHAR(4000)

DECLARE @datasrc_filenm VARCHAR(256)

DECLARE @location VARCHAR(4000)

DECLARE @provstr VARCHAR(4000)

DECLARE @catalog VARCHAR(128)

DECLARE @sqlcmdtext VARCHAR(1000)

-- TODO: Set parameter values

SET @srvproduct = 'Excel'

SET @provider = 'Microsoft.Jet.OLEDB.4.0'

SET @provstr = 'Excel 8.0'

SET @lnk_server_nm = 'XL_FILE'

SET @datasrc_folder = 'C:\MyFolder\' --Fill in your folder to .xls file

SET @datasrc_filenm = 'MyExcelFile.xls' --Fill in your name to .xls file

SET @datasrc = @datasrc_folder + @datasrc_filenm

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @lnk_server_nm)

BEGIN

EXEC master.dbo.sp_dropserver @server=@lnk_server_nm, @droplogins='droplogins'

END

EXEC @rc = [master].[dbo].[sp_addlinkedserver] @lnk_server_nm, @srvproduct, @provider,

@datasrc, @location, @provstr, @catalog

--DELETE TARGET TABLE IF IT EXISTS

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblNew]') AND type in (N'U'))

DROP TABLE [dbo].[tblNew]

--PULL EXCEL DATA INTO TABLE (CREATES TABLE AUTOMATICALLY WITH CORRECT COLUMNS)

SET @sqlcmdtext = 'SELECT * INTO tblNew FROM OPENQUERY(' + @lnk_server_nm + ',' + CHAR(39) + 'SELECT * FROM [Sheet1$]' + CHAR(39) + ')'

EXEC(@sqlcmdtext)

--NOW, do what you need by moving data from tblNew to whatever predefined tables you need the data within.

josephptran2002 Hall of Fame Points: 3275 More actions · Answer 3

Hi Ike,

How are you doing? I am greatly appreciated your helps, I already solved the problem by using SSMS or Import/export wizard. You are very nice person, and may god bless you for what you did or do to help others in needed of helps in SQL 2005.

Again, thank you very much

Joe

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

josephptran2002 (5/24/2009)
Hi Ike,
How are you doing? I am greatly appreciated your helps, I already solved the problem by using SSMS or Import/export wizard. You are very nice person, and may god bless you for what you did or do to help others in needed of helps in SQL 2005.
Again, thank you very much
Joe

The others tried to help. Did you ever get the ad-hoc query thing working? If not, then you need to go back to the very beginning of this thread and read because it was told how to do it and, from what I can see, you didn't do it. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

josephptran2002 (5/24/2009)
Hi Ike,
How are you doing? I am greatly appreciated your helps, I already solved the problem by using SSMS or Import/export wizard. You are very nice person, and may god bless you for what you did or do to help others in needed of helps in SQL 2005.
Again, thank you very much
Joe

And if you solved the problem, forum etiquette would have you post your solution. Others may have the same or similar problem and how you resolved your problem may benefit others.