March 29, 2016April 24, 2016 Ashish Patel

Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server

To, Import/Export data To/From Excel (.xls) need Microsoft.Jet.OLEDB.4.0 and for Excel 2007 (.xlsx) need 2007 Office System Driver: Data Connectivity Components. You can download from here.

Export Data to Excel file
Create an Excel file named testing having the headers same as that of SQLServerTable columns
Here is Query:
Excel 2003 (.Xls) file:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;', 'SELECT * FROM [Sheet1$]') select * from SQLServerTable

Excel 2007 (.Xlsx) file:

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;', 'SELECT * FROM [Sheet1$]') select * from SQLServerTable

Import data from Excel to new SQL Server table
Excel 2003 (.Xls) file:

select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

Excel 2007 (.Xlsx) file:

Select * into SQLServerTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

Import data from Excel to existing SQL Server table
Excel 2003 (.Xls) file:

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

Excel 2007 (.Xlsx) file:

INSERT INTO SQLServerTable select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

Import data from Excel to new SQL Server table with dynamic table name (ex. SQLTABLE_200110413)

DECLARE @table varchar(500)
DECLARE @Q varchar(2000 )
SET @table='SQLTABLE_' +(CONVERT(VARCHAR(8),GETDATE(),112))
SET @Q= 'select * into '+  @table + ' FROM OPENROWSET("Microsoft.ACE.OLEDB.12.0", "Excel 12.0;Database=D:\testing.xlsx;HDR=YES", "SELECT * FROM [Sheet1$]")'
Exec(@Q)

May be you find error like below while Import/Export data To/From Excel
Error 1:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Here is solution might work for you..

USE [master] 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
GO

Error 2:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

here is solution might work for you

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

If above Query showing following error…

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.

then user RECONFIGURE WITH OVERRIDE instead of RECONFIGURE..

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE

If above solution is not working, ere is solution link might work for you..
http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx

Thanks.

February 5, 2016 Ashish Patel

Import XML file to Database Table

In this article, I talked about how to parse XML or import XML to SQL Server Database Table.
XML File:

<br />
<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders><br />

To Query XML File we need to store in to xml variable @MyXML:

<br />
DECLARE @MyXML XML<br />
SET @MyXML = '<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders>'<br />

Now we use SQL query to get Order header from @MyXML:

<br />
SELECT Col.value('@OrderNumber', 'int') AS 'Order',<br />
Col.value('@OrderDate', 'date') AS 'OrderDate',<br />
Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',<br />
Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',<br />
Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',<br />
Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',<br />
Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',<br />
Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',<br />
Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',<br />
Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',<br />
Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',<br />
Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',<br />
Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',<br />
Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',<br />
Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'<br />
FROM @MyXML.nodes('/Orders/Order') AS T(Col)<br />

Now we use SQL query to get Order Items from @MyXML:

<br />
SELECT<br />
Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',<br />
Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber' ,<br />
Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,<br />
Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',<br />
Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',<br />
Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',<br />
Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'<br />
FROM @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)<br />

Full Code:

<br />
DECLARE @MyXML XML<br />
SET @MyXML = '<?xml version="1.0"?><br />
<Orders><br />
<Order OrderNumber="99503" OrderDate="2013-10-20"><br />
  <Address Type="Shipping"><br />
    <Name>Ellen Adams</Name><br />
    <Street>123 Maple Street</Street><br />
    <City>Mill Valley</City><br />
    <State>CA</State><br />
    <Zip>10999</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <Address Type="Billing"><br />
    <Name>Tai Yee</Name><br />
    <Street>8 Oak Avenue</Street><br />
    <City>Old Town</City><br />
    <State>PA</State><br />
    <Zip>95819</Zip><br />
    <Country>USA</Country><br />
  </Address><br />
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes><br />
  <Items><br />
    <Item PartNumber="872-AA"><br />
      <ProductName>Lawnmower</ProductName><br />
      <Quantity>1</Quantity><br />
      <USPrice>148.95</USPrice><br />
      <Comment>Confirm this is electric</Comment><br />
    </Item><br />
    <Item PartNumber="926-AA"><br />
      <ProductName>Baby Monitor</ProductName><br />
      <Quantity>2</Quantity><br />
      <USPrice>39.98</USPrice><br />
      <ShipDate>2013-05-21</ShipDate><br />
    </Item><br />
  </Items><br />
</Order><br />
</Orders>'<br />
   SELECT<br />
    Col.value('@OrderNumber', 'int') AS 'Order',<br />
    Col.value('@OrderDate', 'date') AS 'OrderDate',<br />
    Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',<br />
    Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',<br />
    Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',<br />
    Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',<br />
    Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',<br />
    Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',<br />
    Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',<br />
    Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',<br />
    Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',<br />
    Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',<br />
    Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',<br />
    Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',<br />
    Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'<br />
  FROM  @MyXML.nodes('/Orders/Order') AS T(Col)</p>
<p>   SELECT<br />
    Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',<br />
    Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber'  ,<br />
    Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,<br />
    Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',<br />
    Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',<br />
    Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',<br />
    Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'<br />
  FROM  @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)<br />

XML_To_DB

Thanks

February 1, 2016 Ashish Patel

Create Multi-Layer XML file from SQL Database Table

In this article, I talked about how to create multilayer xml from SQL Server Database table.
Following is the XML which we want to create:
[ad#post]

<Orders>
  <Order OrderNumber="99503" OrderDate="2013-10-20">
    <Address Type="Shipping">
      <Name>Ellen Adams</Name>
      <Street>123 Maple Street</Street>
      <City>Mill Valley</City>
      <State>CA</State>
      <Zip>CA</Zip>
      <Country>USA</Country>
    </Address>
    <Address Type="Billing">
      <Name>Tai Yee</Name>
      <Street>8 Oak Avenue</Street>
      <City>Old Town</City>
      <State>PA</State>
      <Zip>PA</Zip>
      <Country>USA</Country>
    </Address>
    <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
    <Items>
      <Item PartNumber="872-AA">
        <ProductName>Lawnmower</ProductName>
        <Quantity>1</Quantity>
        <USPrice>148.95</USPrice>
        <Comment>Confirm this is electric</Comment>
      </Item>
      <Item PartNumber="926-AA">
        <ProductName>Baby Monitor</ProductName>
        <Quantity>2</Quantity>
        <USPrice>39.98</USPrice>
        <ShipDate>2013-05-21</ShipDate>
      </Item>
    </Items>
  </Order>
</Orders>

We have two table called Orders and Order_Products

XML_To_DB

SQL Script to generate above XML:

SELECT [Order] as '@OrderNumber' , OrderDate as '@OrderDate',
(
     SELECT 'Shipping' as '@Type' 
     , Shipping_Name as 'Name'
     ,Shipping_Street as 'Street'
     ,Shipping_City as 'City'
     ,Shipping_State as 'State'
     ,Shipping_State as 'Zip'
     ,Shipping_Country as 'Country'
     FOR XML PATH('Address'),Type
  ),
  (
     SELECT 'Billing' as '@Type' 
     , Billing_Name as 'Name'
     ,Billing_Street as 'Street'
     ,Billing_City as 'City'
     ,Billing_State as 'State'
     ,Billing_State as 'Zip'
     ,Billing_Country as 'Country'
     FOR XML PATH('Address'),Type
  ),DeliveryNotes,
  (
    SELECT PartNumber as '@PartNumber', ProductName, Quantity,convert(numeric(18,2), USPrice)as USPrice,Comment,ShipDate 			
		From [temp].[dbo].Order_Products P where 	P.[Order] =Ord.[Order]
		FOR XML PATH('Item'),ROOT('Items'),Type		
   )   
FROM [Orders] Ord
FOR XML PATH('Order'),ROOT('Orders')

Thanks

September 9, 2015 Ashish Patel

Import Excel 2007(.xlsx) Sheet into SQL Server 2008 without Query

In this Article, I perform following steps to Import Excel 2007(.xlsx) Sheet into SQL Server 2008.

First: To Import Excel (.xls) to database you need to install Microsoft.Jet.OLEDB.4.0 and for Excel 2007 (.xlsx) need 2007 Office System Driver: Data Connectivity Components. You can download from here.

Step 1: Select Destination Database where you would like to Import Excel 2007 Sheet.
Right click database > Tasks > Select Import data… as shown in following figure.

Step 2: Select “Microsoft Office 12.0 Access Database Engine OLE DB Provider” from data source selection drop down. Click on “Properties…” button as shown in figure.

Step 3: Properties dialog box appears.
Enter Excel 2007 file path in Data source text box. Then Click on “All Tab”.

Step 4: Double Click on “Extended Properties” appears Edit value dialog box.

Step 5: Enter “Excel 12.0” in Property value textbox and click OK button

Step 6: Press Test Connection button to make sure everything is working good.
Finally press OK button.

After that you need to follow usual procedure to import excel sheet data into sql server.

here is my another article Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server Using SQL Query

Thanks.

September 2, 2015 Ashish Patel

How to Resolve the collation conflict and check Collate in SQL Server

In This Article, I’ll show you How to solve Resolve the collation conflict in SQL Server.
For Example I have SQL Query:
Query:

SELECT     *
FROM         categories INNER JOIN
                      search ON categories.cid = search.cat_id

Above SQL Query Giving me a below error.
Error:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Why:
Because Category.cid column has different collate than search.cat_id. So, we cannot use = operation between them.

Here categories.cid hase collate SQL_Latin1_General_CP1_CI_AS
And search.cat_id hase collate Latin1_General_CI_AS

Solution:

1. Have to make both column with same Collate so we can compare.
1a. here we change collate of search.cat_id to collate SQL_Latin1_General_CP1_CI_AS from Latin1_General_CI_AS

SELECT     search.*
FROM         categories INNER JOIN search 
ON categories.cid  = search.cat_id collate SQL_Latin1_General_CP1_CI_AS

OR
1b. here we change collate of categories.cid to Latin1_General_CI_AS from collate SQL_Latin1_General_CP1_CI_AS

SELECT     search.*
FROM         categories INNER JOIN search 
ON categories.cid collate Latin1_General_CI_AS = search.cat_id

2. Use COLLATE DATABASE_DEFAULT

   SELECT     search.*
   FROM         categories INNER JOIN search 
ON categories.cid COLLATE DATABASE_DEFAULT = search.cat_id COLLATE DATABASE_DEFAULT

How to check Collate:
Go to SQL Server Object Explorer then go to your database table. Expand table & expand Column of table then right click on column which you want to check Collate.

Now Click on Property and you will see following image

Ashish's Blog

Web Developer Tutorial

SQL Server

Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server

Import XML file to Database Table

Create Multi-Layer XML file from SQL Database Table

Import Excel 2007(.xlsx) Sheet into SQL Server 2008 without Query

here is my another article Import/Export Excel (.Xlsx) or (.Xls) File into SQL Server Using SQL Query

How to Resolve the collation conflict and check Collate in SQL Server