Skip to main content

Importing the Excel into the Database (SQL).

We can import the excel into the Database by using the following query.

Suppose we have a table named as Address_Temp and it has three column.

1. Name
2. State
3. Zip

Here we need to update the zip code with the new value and to update that zip code we need to check the excel.

So we can use that excel in our sql server by importing it using the following command.

Its very easy query but make sure that it won't work in following two scenarios.

1. The file need to be in local machine.
2. The file should not be password protected.

Declare @ZipTemp NVarchar(255)   

set @ZipTemp = (SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Addresses.xls;IMEX=1','SELECT top 1 ZIP FROM [Sheet1$] where ZIP = "328301"'))
Update [dbo].[Addresses_Temp]
set State = 'CA', Zip = @ZipTemp 
where Zip = '328301'`

Here IMEX stands for mixed data type.

Labels:

Comments

  1. MohitFriday, 06 September, 2013

    I didn't knew that we can't import from password protected files.

    ReplyDelete

Post a Comment

Important - Make sure to click the Notify Me check-box below the comment to be notified of follow up comments and replies.

Popular posts from this blog

Convert your datatable into generic poco object in c# using linq, ado and reflections.

Follow @harshit_parshii The most common problem that we face these days is to create a common class and method that can be used across all the projects and codes. So today I will be sharing my code where you can see how to make and create a generic function without using entity framework for ado. net. The scenario is like you have an old software that uses stored procedure to return set of entities as a data-table, you do not want to re-write the back-end code as you are creating a web API in c# which needs to be delivered asap. You need to map these data tables to models as you might be using MV* pattern. So here we will be doing one to one mapping of model to data- table, and in similar fashion insert or update can also be done. So basically we are converting a data-table to list of strongly typed object model to do CRUD operations. So we have following things before hand. A helper class is referenced as the database(dbFactory) which executes ado. ne...
Post a Comment
Read more

Run CSS specific to Internet Explorer - Browser Hack

Run CSS specific to Internet Explorer - Browser Hack Referencing to the following blog post, we are going to make CSS targeting exclusively to IE browser, to make it work first we should know what are media queries. A media query consists of a media type and at least one expression that limits the style sheets' scope by using media features, such as width, height, and color. Media queries, added in CSS3, let the presentation of content be tailored to a specific range of output devices without having to change the content itself. For Ex: < style > @media (max-width : 600px) { .facet_sidebar { display : none ; } } So below we will wrap the IE specific CSS rules in @media blocks and trick IE into rendering @media blocks that use media queries. Targeting only IE browsers Style rules defined in the following blocks will only be applied in IE, other browsers will ignore them. IE 6 and 7  @media screen\9 {     body { background: red; ...
Post a Comment
Read more

Send a Fax in windows using faxcomexlib and TAPI in VB code .Net

An application that provides sending fax from faxmodem, connected to the computer, will be explained in the following post.  We can use Telephony Application Programming Interface (TAPI) and the Fax Service Extended Component Object Model (COM) API to send fax. The fax service is a Telephony Application Programming Interface (TAPI)-compliant system service that allows users on a network to send and receive faxes from their desktop applications. The service is available on computers that are running Windows 2000 and later. The fax service provides the following features: Transmitting faxes Receiving faxes Flexible routing of inbound faxes Outbound routing Outgoing fax priorities Archiving sent and received faxes Server and device configuration management Client use of server devices for sending and receiving faxes Event logging Activity logging Delivery receipts Security permissions The following Microsoft Visual Basic code example sends a fax. Note that...
Post a Comment
Read more