How to transfer the data from Excel to SQL2005

Question

Post reply

How to transfer the data from Excel to SQL2005

Viewing 15 posts - 31 through 45 (of 49 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

Are you connecting to sql server using sql or windows authentication?

Which is the service account for sql server service? (Go to Run > services.msc > Goto SQL Server > Properties)

--Ramesh

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

I connect to sql server using sql.

For the service account for sql server service, how can I figure which service is for my sql server ?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Try executing the query by connecting to server using windows authentication.

And in services, look for "SQL Server (MSSQLSERVER)" (in case of default instance) or "SQL Server ("InstanceName")" (in case of named instance).

--Ramesh

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 4

Josephptran2002

You are not the only one having problems to import data from Excel (xls) to SQL-server.

This has been on the agenda in this forum many times.

I always save the Excelsheet in tab txt format and then use bulkinsert. Maybe this is not

what you are looking for but it works.

//Gosta

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

Hi Gosta,

Thanks for the comments, can you show me how to save ExcelSheet in tab txt format and how to use bulkinsert (i mean what is the command of using Bulk Insert).

Thanks.

P.S: I don't care what method that people use, at least it might work...that is a goal.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 6

If you're only needing to do this as a one-time shot, you could try what those of us without any real rights do.

In Excel, (I'm pretending your data is in column A and is a string, so needs single quotes) set a formula in the first blank column, row 1:

="INSERT INTO myTable (myColumn) VALUES ('"&A1&"')"

then just double-click the handle to drop the formula all the way to the end of your data (or drag/drop).

Copy/paste your insert statements into your sql query window and execute.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

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

What if the first row is header name? where should I type the formula? However, what do you call this method? and in my excel file, there are multiple columns that I wish to transfer to SQL database. So this method is only transfering one column at a time?

Thanks

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 8

If the first row is the header, then put the formula on row 2 and change it accordingly. the column it's adding to the string should be on the same row.

If you want multiples, then just add multiples to your string, the same as if you were typing it into your query window. Example, where I'm pretending that I'm grabbing columns a, b, c in the values on row 2:

="INSERT INTO myTable (myColumnA, myColumnB, myColumnC) VALUES ('"&A2&"','"&B2&"','"&C2&"')"

Or if you want to use the column headers as your column names rather than hardcoding into the formula,

="INSERT INTO myTable ("&A$1&", "&B$1&", "&C$1&") VALUES ('"&A2&"','"&B2&"','"&C2&"')"

keep in mind your datatypes, char/varchar/datetime values need the single quotes, money/int/decimal/etc datatypes don't, so you'll want to remove from the formula where appropriate.

You may also have to use trim() around your cell references to remove unwanted white space, and if your dates are formatted in Excel, I usually have to do (I'll pretend column C is the date) month(C2)&"/"&day(C2)&"/"&year(C2) in place of just 'C2' above.

You're just using Excel to generate your SQL, same rules apply as if you were typing them into the query window.

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Why all this hassle with OPENROWSET? What's wrong with SSIS or the import/export wizard? This is exactly what those tools are there for. Why don't you try using one of them and stop trying to hammer a nail with a crowbar.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Hi

Where can I find the SSIS or import/export wizard? can you send me a link of how to use it?

Thanks

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 11

josephptran2002 (5/20/2009)
Hi
Where can I find the SSIS or import/export wizard? can you send me a link of how to use it?
Thanks

Are you serious?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

josephptran2002 (5/20/2009)
Where can I find the SSIS or import/export wizard?

They're both part of SQL server.

can you send me a link of how to use it?

http://www.google.com/search?q=SSIS

http://www.google.com/search?q=%22Import+Export+Wizard%22+%22SQL+Server%22

Or perhaps you could just start by reading up on them in Books Online.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 13

Really funny reading this thread, if it were not that sad. Joseph, do you actually have Excel installed on your machine? Do you have a clue as to how to drive it to do simple things like copying statements down a range of cells?

Honestly, you are wasting everybody's time. How about doing a course like ICDL (International Computer Drivers Licence, yeah, that does exist, my kids do it at school), and then come back before you start playing with the real stuff like SQL?

Sorry mate, but I'm not feeling very sympathetic with you and your problems.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

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

Hi there,

I used the SSIS or import/export wizard to import the data from excel to SQL and it gives me syntax errors. DO YOU KNOW WHAT WRONG? I checked the excel and SQL to make sure nothing wrong with the format (espcially with the Fiscal Year Column).

- Copying to [Regional Office Claims Adjudication Process-Disability (ROCAP)].[dbo].[ROCAPData] (Error)

Messages

•Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unspecified error".