Import From Excel

Hi:

I have an xls file formatted like so:

Loc Nbr jan feb mar apr may jun jul aug sep oct           .....
0000000 9.86 9.85 0 0 0 10.03 9.96 9.94 9.93 9.92         .....
0000021 9.46 9.58 9.49 9.5 9.7 9.67 9.84 9.67 9.59 9.68         ......
 

But I changed my table structure to this:

Loc Nbr   Month Value
0000021   jan 9.46
0000021   feb 9.58

My goal is to copy the data from my xls sheet into a SQL table. But I don't know how to manipulate the cells so that it all lines up correctly.

Any ideas?

Thanks!

0
XD40_Man
12/19/2008 5:13:50 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
765 Views

Similar Articles

[PageSpeed] 45
Get it on Google Play
Get it on Apple App Store

You can use the OPENDATASOURCE function in a SQL sentence so you can copy from Excel to SQL Server directly.

You have a full explanation here: http://technet.microsoft.com/en-gb/library/ms179856.aspx

Hope this helps

 




If this post is useful to you, please mark it as answer.
0
Hubble
12/19/2008 6:09:51 PM

Hi,XD40_Man

Ok U have to use Unpivot command then

Try The Below Example

I am showing U the Example Using

OPENROWSET method

I am assuming that U have ur excel File at this path

C:\Test.xls

 

In Excel

 

Loc Nbr jan feb mar apr may jun jul aug sep oct nov dec
0000000 9.86 9.85 0 0 0 10.03 9.96 9.94 9.93 9.92 0 0
0000021 9.46 9.58 9.49 9.5 9.7 9.67 9.84 9.67 9.59 0 0 0

 

 In Sql

Create table Test_Final_Excel

([Loc Nbr] varchar(20),[month] varchar(10),[Value] numeric(18,2))

GO

 

Now To Insert in Sql U have To Do This

For First Time To Enable The Import option exec this

sp_configure 'show advanced options',1

reconfigure with override

go

sp_configure 'Ad Hoc Distributed Queries',1

reconfigure with override

go

 

 

 

Go

INSERT INTO Test_Final_Excel

([Loc Nbr],[month],[Value])

SELECT [Loc Nbr], [month], [Value]

FROM

(

SELECT [Loc Nbr], [jan], [feb] ,[mar] ,[apr],[may] ,[jun] ,[jul] ,[aug] ,[sep] ,[oct] ,[nov] ,[dec]

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\Test.xls', [Sheet1$])

) as p

UNPIVOT

([Value] FOR [month] IN ([jan], [feb] ,[mar] ,[apr],[may] ,[jun] ,[jul] ,[aug] ,[sep] ,[oct] ,[nov] ,[dec] ))

AS Unpvt

 

--To Test That Rows Has Inserted

SELECT [Loc Nbr], [month], [Value] from Test_Final_Excel

--Output

Loc Nbr month Value

-------------------- ---------- ---------------------------------------

0000000 jan 9.86

0000000 feb 9.85

0000000 mar 0.00

0000000 apr 0.00

0000000 may 0.00

0000000 jun 10.03

0000000 jul 9.96

0000000 aug 9.94

0000000 sep 9.93

0000000 oct 9.92

0000000 nov 0.00

0000000 dec 0.00

0000021 jan 9.46

0000021 feb 9.58

0000021 mar 9.49

0000021 apr 9.50

0000021 may 9.70

0000021 jun 9.67

0000021 jul 9.84

0000021 aug 9.67

0000021 sep 9.59

0000021 oct 0.00

0000021 nov 0.00

0000021 dec 0.00

(24 row(s) affected)

  

 

 

 

 

 

I have given U the logic Of How to convert that data of excel

I f U want to use that to insert from front end

U can Use the same query of Unpivot and can insert from that


RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
12/20/2008 6:10:00 AM

raghav_khunger,

YOU ARE THE GREATEST!!

THX!!

0
XD40_Man
12/21/2008 12:29:05 AM

hope this link also more help full for you

http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

Regards,

0
danishjee
12/21/2008 2:20:27 PM
Reply:

Similar Artilces:

Import excel data from Microsoft excel to SQL Server usin C sharp .net
Hi Could anyone help me with the sample code in C sharp to import data from excel to SQL server 2005 using C sharp. I have a table called Accounting Calendar ACCT_DATE with 10 columns I have the excel file called example.xls with the same column name as in SQL Server.Please help me with the sample code in C sharp Thanks      Here's a simple way to do it without the need to have excel installedhttp://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx Hope this helpsJaime Don't forget to click "Mark as Answer" on the post that helped you. That wa...

How to Import data from Excel into MS SQL in .Net?
Hi, I'm building a web application in VB.NET 1.1 where users will upload Excel files at this webpage and the web application will transfer the data from the uploaded Excel file into MS SQL 2000. There are around 600-700 records and about 56 columns in the Excel file which is to be uploaded daily. There will be a few different files which I need to implement for as well.  I've sourced for and tried alot of methods. But either they can't satisfy my requirements or they just can't simply work. - Most simple and direct way I've tried is creating a oledb connection to...

Excel to SQL and SQL to Excel
I wanted to know if there is a better way to do the following.  Whenever I query a SQL Server database, I connect using Access through the ODBC because I can write the query using the query builder and export the results to Excel very easily.  Also, when I import data from an Excel spreadsheet to a SQL Server, I import to Access first, then write an append query to get the data into SQL from Access. I would like to learn how to bypass Access in both directions. Yes totally .. you can directly export and import data from excel to sql server. To do that one option is to ...

How to import excel sheet into SQL Database in SQL Server from Remote SQL Client
 Hi all, I need to import Excel sheet into SQL Database in SQL Server machine.this excel sheet can be kept in any where.it may client or server machines.but I need to import it using any SQL client(Remote Client in web) machine.I can do it using  SQL Sever Machine.but it doesn't work using SQL Client  machine.I need to do it using  SQL query.it imported using Import/Export wizard in SQL through SQL Client machine.but it can't using SQL quary.Please tel me how I do this.Following are my code.---------------------------------------------------------------------...

Importing Excel into SQL Server 2005 with Import Wizard
I have several spreadsheets given to me which are in Excel 2007 file format. So, I've saved them as Excel 2003. I run the SQL Server Import and Export Wizard in SQL Server 2005 Management Studio from my local PC againist an online 3rd party hosted database. Initially I was getting errors yet the data was imported, so I wasn't too concerned. Now, on one file, only 8 records out of 285 records are imported. Here is the latest error messages extract: Messages * Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error co...

How to Import data from excel sheet within SQL Client into SQL Server
 Hi All,   I need to  import data from excel sheet to SQL Database in SQL Server.I already done it in machine which SQL Server run.but I cant do it within SQL Client machine.while try to do it this error was occure Server: Msg 7302, Level 16, State 1, Line 2Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.Please tell me How I do itThanks,Harsha Hi harsha  Can you post your code  If this post helps you, please mark it as Answer.Gnana Prakash SSoftware EngineerCoimbatorehttp://www.sgnanaprakash.com  Hi Gnana,this is My...

importing an excel spreadsheet into a sql database using sql server 2005 express
hi, i have an excel spreadsheet with data that i want to place into a sql database.is there any easy way of doing this with sql server 2005 express?    Try this thread for many options.  Hope this helps. http://forums.asp.net/thread/1442470.aspxKind regards,Gift Peddie thanks, downloaded SP1 and the toolkit and ran the DTSwizard and got it working. FrankBollard:thanks, downloaded SP1 and the toolkit and ran the DTSwizard and got it working. I am glad I could help.Kind regards,Gift Peddie...

Import excel file using sql query without installing excel on the server
Hi, I want to import excel file using sql query. MS Excel is not installed on the server. I am using sql server 2005. I do not want to achieve this by code. I want it using sql query only.  I have used openrowset but it requires MS Excel to be installed on the server. Thanks, Ashish.    Ashish Chaugule ashish_chaugule: Hi, I want to import excel file using sql query. MS Excel is not installed on the server. I am using sql server 2005. I do not want to achieve this by code. I want it using sql query only.  I have used openrowset but it requires M...

Excel as import to vb.net?
Has anyone experimented with using excel files as import to a vb.net app? I have a user who is ... reluctant to give up his personal sheets... Thanks! MVP...

How to import all data into SQL from Excel
Hi all,I have problem importing data from excel to sql 2000.i import this excel using my asp web form.but some data not imported.hi-lighted  data did not import.Ref_No     Stage_No       Boq_No       Description                  1                                     first stagee-001                             1.1         &nbs...

Excel Import into SQL Server
Hi, I am trying to write a routine that will use a fileUpload object so the user can browse to an excel file on their machine, then hit an Import button. On the click of the Import button I want to parse through the file and do some verification and manipulation with each row and then, if everything is ok, dump it all into a new table in sql server. I am wondering if I am on the right track? Should I save the file to the webserver and then dump it into a dataset, loop through the dataset and verify it, then if everything is ok, save via a stored proc?   Here is my control code: &nb...

Importing excel to sql server....
Dear AllI am trying to import data from excel sheet to sql server database, by using method 2, it creates a table on fly but it never shows any table in database tables'  list but when i execute the code again, system throws an exception that table already exists.On the other hand method two assumes that there is an existing table in db, but after execution, it never shows data, table remains empty. Here is code, please tell me whats wrong with this code.Regards<code>Dim ExcelConnection As New System.Data.OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\a...

Import from excel to sql server
Hi all I am facing a serious problem in importing the data that is available in Microsoft Excel into Our Sql server, Its a very Important and urgent requirement can any one pls let me know Thanks in advanceRamesh S Indecomm Global Services (Sr Software Developer) Bangalore,India...

Cannot Import Excel File Into SQL
I've read about a million posts on this, but I can't seem to get this to work.  This is my production environment:Web Server (ServerA): Windows Server 2003 SP2, ASP.NET 2.0Database Server (ServerB): Windows 2003 SP2, SQL Server 2005So I'm trying to have a web form upload an excel file to a share on ServerB, and then run a stored procedure that imports the contents of the excel file into a table in my database.  I can upload the file fine, but I cannot get the stored procedure to import the data. I always get this error, whether I run the process through my web form, or ...

import data from sql to excel
How is it possible to download the excel file programatically as a TRUEEXCEL file (Not an HTML file that looks like an Excel)BTW, this is the code that downlaods the grid to excel..Response.ContentType = "application/vnd.ms-excel";Response.Charset = "";Response.AddHeader("Content-Disposition", "attachment;filename=myfile.xls"); It is possible (just) but you'd need Excel installed on the server to do it, which is unlikely. You can fairly easily create CSV files, which will open fine in Excel. how do i do that?can you please guidE? If you mean ...

Web resources about - Import From Excel - asp.net.sql-datasource

List of countries by imports - Wikipedia, the free encyclopedia
This is a list of countries by imports , based on the World Trade Organization and The World Factbook . For comparison purposes, some non-sovereign ...

Perfect Audience Performance Reporting API Allows Facebook Advertisers To Import Data
Advertisers that use Facebook Exchange can now import data on their ads, campaigns, and conversions into their own customized reporting dashboards ...

Oakland Opens the Wallet for Cuban Import
Looks like Billy Beane really is sticking to that Window. After spending the offseason trading away two of the better young starting pitchers ...

How to Import Your Blog Into Facebook
Inside Facebook has recently written about simplifying how you use Facebook with other social media tools in order to make life a little easier. ...

Export Import Business Group
Here we are, people in the business of export and import from all over the world. I invite you people also to join for more info of the business ...

#1 Cheat for Ruzzle ~ find the best words with free auto game import! on the App Store on iTunes
Get #1 Cheat for Ruzzle ~ find the best words with free auto game import! on the App Store. See screenshots and ratings, and read customer reviews. ...

Secretary Kerry Delivers Remarks at the Export-Import Bank Annual Conference - Flickr - Photo Sharing ...
U.S. Secretary of State John Kerry delivers remarks at the Export-Import Bank Annual Conference in Washington, D.C., on April 24, 2014. [State ...

Christmases come at once for Canberra Cavalry imports at Percy households
When a Canberra Cavalry player is billeted by ground announcer Matt Percy and his family, the unofficial initiation is usually a big spoonful ...

Apple adds iPhones to Lightning to USB Camera Adapter compatibility list, photo import works under iOS ...
... reading at 9to5Mac . What do you think? Discuss "Apple adds iPhones to Lightning to USB Camera Adapter compatibility list, photo import works ...

RYAN AND RUBIO: American Companies Will Go Bankrupt If We Don’t Import More Foreign Workers!
By Julia Hahn In a Tuesday morning interview with radio host Bill Bennett, Rep. Paul Ryan (R-WI) defended his omnibus bill’s controversial expansion ...

Resources last updated: 1/17/2016 3:24:42 AM