Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Leveraging XP Excel , XML, and OPENXML for Data Imports

By Sloan Holliday,

Leveraging XP Excel , XML, and OPENXML for Data Imports

 

If you deal with clients, then you've probably at some point or another dealt with importing "their" data. A lot of times, this data will come as a Microsoft Excel document. I've had my tricks in the past.  Opening Excel in one window, and opening an Access database in another window (linked to SQL Server), I could copy and paste.

But this was very, very manual.  With the release of Excel XP, I have a new trick. Excel XP comes with the ability to save to XML.  Thus, I decided to match this new functionality with the wonderful ability of SQL Server 2000's ability to use XML data, as relational data, with the OPENXML statement.

Below is some sample code to get you started.  The C# code will save an Excel file to XML.  I then have a usp (SQL Server User Stored Procedure) which can import that data. Getting the namespace syntax inside the stored procedure was the only thing that took a little time to wade through.  But now that I have it, importing Excel data to SQL Server 2000 as quick as editing some column names in the stored procedure.

// Start ExcelXMLConverter.cs file 
using System;
namespace GranadaCoder.ImportExportLib.ExcelLib
{
/// <summary>
/// Summary description for ExcelXMLConverter.
/// This class is used as a wrapper to take an existing excel spreadsheet
/// and to save it as a XML file using the 
/// Microsoft Excel XP ability (aka, "Save As XML" is a MS Excel XP new functionality)
/// 
/// Microsoft URL: for reference
/// http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrtskhowtosaveworkbooks.asp
/// and
/// http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_vsto2003_ta/html/excelobj.asp
/// 
/// 
/// You'll need to add a reference to the (COM object) Microsoft Excel library. 
/// This will NOT work with Excel 2000 or Excel 97.
/// 
/// 
/// </summary>
public class ExcelXMLConverter
{
//Excel variables, Worksheet is not needed for a simple "Save As XML" function
Excel.Application xlApp = null;
Excel.Workbook xlBook = null;
//Excel.Worksheet xlSheet =null;
string m_inputExcelFileName = null;
string m_outputXMLFileName = null;
private bool m_overwriteExistingFile = true;
string debugMsg = null;
private void checkParameters()
{
string error_msg=string.Empty;
bool return_val = true; //default is actually true...
//there are at least 8 chars in the smallest of filenames //ex: (c:\a.ext) has 8 chars
if (this.m_outputXMLFileName.Length < 8)
{
error_msg +="Output File '" + this.m_outputXMLFileName + "' does\n.not appear to be a valid filename.";
return_val= false;

}
System.IO.FileInfo fi = new System.IO.FileInfo(this.m_inputExcelFileName );
if (!fi.Exists)
{
// the input file (excel) does not exist
error_msg +="Input File '" + this.m_inputExcelFileName + "' does not exist\n.";
return_val= false;

}

if (!this.m_overwriteExistingFile)
{
// the bool check is on the "outerloop" to prevent
// a new fi object, if the check is not needed
// this says "the user said 'do not overwrite', yet the file does exist
fi = new System.IO.FileInfo(this.m_outputXMLFileName );
if (fi.Exists)
{
error_msg +="Output File '" + this.m_outputXMLFileName + "' exists and\n'OverWriteExisting' flag set to false.";
return_val= false;

}
}
 
if (return_val == false)
{
throw new ArgumentException(error_msg);
}
return;
}
 
public void ConvertExcelToXml() 
{
checkParameters(); // make sure the file names passed in are good
try
{
xlApp = null;
//xlSheet = null;
xlApp = new Excel.Application();
//this turns OFF message box alerts, especially in case of existing files.
xlApp.DisplayAlerts=false;
Console.Out.WriteLine (xlApp.Name);
Console.Out.WriteLine(xlApp.Workbooks.Count);
 
xlBook = xlApp.Workbooks.Open(@m_inputExcelFileName,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
 
//Notice the Second argument, its the magic flag for XML
xlApp.ActiveWorkbook.SaveAs(this.m_outputXMLFileName,
Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
xlBook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
xlApp = null;
xlBook = null;
//xlSheet = null;
}
catch (System.Runtime.InteropServices.COMException)
{
// ignore this error, user probably cancelled a file overwrithe
}
catch(Exception ex)
{
// better error handling and log event needed here
debugMsg = ex.Message;
throw ex;
}
}
public ExcelXMLConverter(string inputExcelFileName , string outputXMLFileName , bool overwriteExistingFile)
{
this.m_inputExcelFileName = inputExcelFileName;
this.m_outputXMLFileName = outputXMLFileName;
this.m_overwriteExistingFile = overwriteExistingFile;
}
}
}
 
 
// End ExcelXMLConverter.cs file 

--//Start tsql (.sql) Code.  There is a sample at the beginning, and then a CREATE PROCEDURE after the sample.

--This sample doc (@doc) is a trimmed up version
--of a "Save as XML" excel spreadsheet (Excel XP and up)
--But the core XML information is in present
--(it was trimmed because of the 8000 character limit
--normally, @doc would be the input parameter of a
--stored procedure and would be of type "text") --State another way, if you take an ordinary Excel file, open it up on Excel XP, and do a "save as XML", and then open the file in notepad, you'll see something like the XML document below
--See the bottom of this file to see what the data sample would look like in Excel (the program) declare @doc varchar (8000)
select @doc =
'
<?XML version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>sholliday</Author>
  <LastAuthor>Administrator</LastAuthor>
  <Created>2003-12-22T18:58:08Z</Created>
  <LastSaved>2003-12-23T15:51:38Z</LastSaved>
  <Company>organization</Company>
  <Version>10.4219</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <DownloadComponents/>
  <LocationOfComponents HRef="file:///\\"/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8835</WindowHeight>
  <WindowWidth>11340</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="8" ss:ExpandedRowCount="4" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:Index="2" ss:AutoFitWidth="0" ss:Width="78"/>
   <Column ss:AutoFitWidth="0" ss:Width="71.25"/>
   <Column ss:AutoFitWidth="0" ss:Width="72"/>
   <Column ss:Index="6" ss:AutoFitWidth="0" ss:Width="54.75"/>
   <Column ss:AutoFitWidth="0" ss:Width="60.75"/>
   <Row>
    <Cell><Data ss:Type="String">myuid</Data></Cell>
    <Cell><Data ss:Type="String">mylastname</Data></Cell>
    <Cell><Data ss:Type="String">myfirstname</Data></Cell>
    <Cell><Data ss:Type="String">myaddress1</Data></Cell>
    <Cell><Data ss:Type="String">mycity</Data></Cell>
    <Cell><Data ss:Type="String">mystate</Data></Cell>
    <Cell><Data ss:Type="String">myzip</Data></Cell>
    <Cell><Data ss:Type="String">mygender</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="String">smith</Data></Cell>
    <Cell><Data ss:Type="String">john</Data></Cell>
    <Cell><Data ss:Type="String">123 main</Data></Cell>
    <Cell><Data ss:Type="String">charlotte</Data></Cell>
    <Cell><Data ss:Type="String">nc</Data></Cell>
    <Cell><Data ss:Type="Number">55555</Data></Cell>
    <Cell><Data ss:Type="String">M</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">234</Data></Cell>
    <Cell><Data ss:Type="String">jones</Data></Cell>
    <Cell><Data ss:Type="String">mary</Data></Cell>
    <Cell><Data ss:Type="String">543 hickory</Data></Cell>
    <Cell><Data ss:Type="String">statesville</Data></Cell>
    <Cell><Data ss:Type="String">nc</Data></Cell>
    <Cell><Data ss:Type="Number">44444</Data></Cell>
    <Cell><Data ss:Type="String">F</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">345</Data></Cell>
    <Cell><Data ss:Type="String">moore</Data></Cell>
    <Cell><Data ss:Type="String">pat</Data></Cell>
    <Cell><Data ss:Type="String">342 sycamore</Data></Cell>
    <Cell><Data ss:Type="String">asheville</Data></Cell>
    <Cell><Data ss:Type="String">nc</Data></Cell>
    <Cell><Data ss:Type="Number">33333</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveCol>1</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>
'
--print @doc
declare @idoc int
--Create an internal representation of the XML document.
exec sp_XML_preparedocument @idoc OUTPUT, @doc , '<root xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"/>'
--Ok, the data above (in XML format) represents (from this example) (which would be originally in an excel file)
declare @holder table (UniqueID varchar(32), LastName varchar(32) , FirstName varchar(32) ,Street varchar(32) , City  varchar(32), State varchar(2) , Zip varchar(12) , Gender  char(1)  ) Insert into @holder  -- This will put the results of the Select FROM OPENXML into the table variable (@holder)
-- SELECT statement using OPENXML rowset provider
SELECT *
FROM   OPENXML (@idoc, '/ss:Workbook/ss:Worksheet[position()=1]/ss:Table/ss:Row[position()!=1]',2) --//Remove [position()!=1] if there is no header row
         WITH (
  UniqueID varchar(32) './ss:Cell[position()=1]/ss:Data',
                LastName  varchar(32) './ss:Cell[position()=2]/ss:Data' ,
  FirstName  varchar(32) './ss:Cell[position()=3]/ss:Data' ,
  Street  varchar(32) './ss:Cell[position()=4]/ss:Data' ,
  City  varchar(32) './ss:Cell[position()=5]/ss:Data' ,
  State  varchar(2) './ss:Cell[position()=6]/ss:Data' ,
  Zip  varchar(12) './ss:Cell[position()=7]/ss:Data' ,
  Gender  char(1) './ss:Cell[position()=8]/ss:Data'
  
  ) --The keys above are:
--1.  The namespace issue, notice the "ss" prefix(es)... and also the third argument of the sp_XML_preparedocument procedure above
--2.  The "position()=1 and position()=2 are for distinguishing "ColumnA" from "ColumnB" in the excel spreadsheet
--    The "Cell position() is the best way I've determined to distinguish the two columns of data
--3.  Side note, the Worksheet[position()=1] is in there because a default excel
--    spreadsheet usually has 3 Worksheets ("Sheet1, Sheet2, Sheet3" on the tabs at the bottom left of Excel)
--4.  The Row[position()!=1] lets you decide upon a header(label) row or not --Other Notes:
--The sample uses @varchar(8000).  If you create a stored procedure (dbo.uspImportExcelData (@doc text), you can get past the 8000 character limit)
--I think at about 20meg, it becomes a little too unpredictable.  I have done stuff <4 meg all the time.   --kill off the object
EXEC sp_XML_removedocument @idoc
Select * from @holder   --Finally, this will help you setup the Excel file if you want to duplicate
--The whole situation
--How the Excel Data would look (in the actual program Microsoft Excel)
--   [A]                              [B]                              [C]                              [D]                              [E]                              [F]  [G]          [H] 
--1  myuid                            mylastname                       myfirstname                      myaddress1                       mycity                           my   myzip        m
--2  123                              smith                            john                             123 main                         charlotte                        nc   55555        M
--3  234                              jones                            mary                             543 hickory                      statesville                      nc   44444        F
--4  345                              moore                            pat                              342 sycamore                     asheville                        nc   33333        U   --If you took this simple data and "Save(d) as XML" in Excel XP,
--you would get something similar to the @doc contents above     ---------------------------------------------------------
if exists (select sysstat & 0xf ,  * from sysobjects
 where id = object_id('dbo.uspExcelImportExample') and sysstat & 0xf = 4)
 drop procedure dbo.uspExcelImportExample
GO
CREATE Procedure dbo.uspExcelImportExample ( @xmlDoc text ) AS SET NOCOUNT ON --print @doc
declare @idoc int
--Create an internal representation of the XML document.
exec sp_XML_preparedocument @idoc OUTPUT, @xmlDoc , '<root xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"/>' print 'Number of characters in the @xmlDoc'
print DataLength(@xmlDoc)
print ''
declare @holder table (UniqueID varchar(32), LastName varchar(32) , FirstName varchar(32) ,Street varchar(32) , City  varchar(32), State varchar(2) , Zip varchar(12) , Gender  char(1)) Insert into @holder  -- This will put the results of the Select FROM OPENXML into the table variable (@holder)
-- SELECT statement using OPENXML rowset provider
SELECT *
FROM   OPENXML (@idoc, '/ss:Workbook/ss:Worksheet[position()=1]/ss:Table/ss:Row[position()!=1]',2) --//Remove [position()!=1] if there is no header row
         WITH (
  UniqueID varchar(32) './ss:Cell[position()=1]/ss:Data',
                LastName  varchar(32) './ss:Cell[position()=2]/ss:Data' ,
  FirstName  varchar(32) './ss:Cell[position()=3]/ss:Data' ,
  Street  varchar(32) './ss:Cell[position()=4]/ss:Data' ,
  City  varchar(32) './ss:Cell[position()=5]/ss:Data' ,
  State  varchar(2) './ss:Cell[position()=6]/ss:Data' ,
  Zip  varchar(12) './ss:Cell[position()=7]/ss:Data' ,
  Gender  char(1) './ss:Cell[position()=8]/ss:Data'
  
  )   --kill off the object
EXEC sp_XML_removedocument @idoc     --The keys above are:
--1.  The namespace issue, notice the "ss" prefix(es)... and also the third argument of the sp_XML_preparedocument procedure above
--2.  The "position()=1 and position()=2 are for distinquishing "ColumnA" from "ColumnB" in the excel spreadsheet
--    The "Cell position() is the best way I've determined to distinguish the two columns of data
--3.  Side note, the Worksheet[position()=1] is in there because a default excel
--    spreadsheet usually has 3 Worksheets ("Sheet1, Sheet2, Sheet3" on the tabs at the bottom left of Excel)
--4.  The Row[position()!=1] lets you decide upon a header(label) row or not --Other Notes:
--The sample uses @varchar(8000).  If you create a stored procedure (dbo.uspImportExcelData (@doc text), you can get past the 8000 character limit)
--I think at about 20meg, it becomes a little too unpredictable.  I have done stuff <4 meg all the time.
--If your data gets really, really big, consider switching to a #temp table.  That's a discussion for another time.
Select * from @holder --Of course, you gotta do something with the data in the @holder variable table
--Something like
--Insert into dbo.User (UniqueID,LastName,FirstName,Street,City,State,Zip,Gender)
--Select UniqueID,LastName,FirstName,Street,City,State,Zip,Gender from @holder
--where dbo.User is a "real" table in the database.
--You can go straight from the OPENXML to the "real" table, using the @holder makes debugging a little easier.     SET NOCOUNT OFF
GO       --//Let's call the stored procedure we just created, using more than 8000 characters of text
--//Notice below I have redundant data, but it shows you can put a lot more rows..when using the @xmlDoc (text datatype) as an input parameter to a stored procedure dbo.uspExcelImportExample
'
<?XML version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>sholliday</Author>
  <LastAuthor>Administrator</LastAuthor>
  <Created>2003-12-22T18:58:08Z</Created>
  <LastSaved>2003-12-23T15:51:38Z</LastSaved>
  <Company>organization</Company>
  <Version>10.4219</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <DownloadComponents/>
  <LocationOfComponents HRef="file:///\\"/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8835</WindowHeight>
  <WindowWidth>11340</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="8" ss:ExpandedRowCount="4" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:Index="2" ss:AutoFitWidth="0" ss:Width="78"/>
   <Column ss:AutoFitWidth="0" ss:Width="71.25"/>
   <Column ss:AutoFitWidth="0" ss:Width="72"/>
   <Column ss:Index="6" ss:AutoFitWidth="0" ss:Width="54.75"/>
   <Column ss:AutoFitWidth="0" ss:Width="60.75"/>
   <Row>
    <Cell><Data ss:Type="String">myuid</Data></Cell>
    <Cell><Data ss:Type="String">mylastname</Data></Cell>
    <Cell><Data ss:Type="String">myfirstname</Data></Cell>
    <Cell><Data ss:Type="String">myaddress1</Data></Cell>
    <Cell><Data ss:Type="String">mycity</Data></Cell>
    <Cell><Data ss:Type="String">mystate</Data></Cell>
    <Cell><Data ss:Type="String">myzip</Data></Cell>
    <Cell><Data ss:Type="String">mygender</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">123</Data></Cell>
    <Cell><Data ss:Type="String">smith</Data></Cell>
    <Cell><Data ss:Type="String">john</Data></Cell>
    <Cell><Data ss:Type="String">123 main</Data></Cell>
    <Cell><Data ss:Type="String">charlotte</Data></Cell>
    <Cell><Data ss:Type="String">nc</Data></Cell>
    <Cell><Data ss:Type="Number">55555</Data></Cell>
    <Cell><Data ss:Type="String">M</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">234</Data></Cell>
    <Cell><Data ss:Type="String">jones</Data></Cell>
    <Cell><Data ss:Type="String">mary</Data></Cell>
    <Cell><Data ss:Type="String">543 hickory</Data></Cell>
    <Cell><Data ss:Type="String">statesville</Data></Cell>
    <Cell><Data ss:Type="String">nc</Data></Cell>
    <Cell><Data ss:Type="Number">44444</Data></Cell>
    <Cell><Data ss:Type="String">F</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">345</Data></Cell>
    <Cell><Data ss:Type="String">moore</Data></Cell>
    <Cell><Data ss:Type="String">pat</Data></Cell>
    <Cell><Data ss:Type="String">342 sycamore</Data></Cell>
    <Cell><Data ss:Type="String">asheville</Data></Cell>
    <Cell><Data ss:Type="String">nc</Data></Cell>
    <Cell><Data ss:Type="Number">33333</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">000</Data></Cell>
    <Cell><Data ss:Type="String">Redundant</Data></Cell>
    <Cell><Data ss:Type="String">Ralph</Data></Cell>
    <Cell><Data ss:Type="String">000 Main St</Data></Cell>
    <Cell><Data ss:Type="String">Raleigh</Data></Cell>
    <Cell><Data ss:Type="String">NC</Data></Cell>
    <Cell><Data ss:Type="Number">00000</Data></Cell>
    <Cell><Data ss:Type="String">U</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveCol>1</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>
' --//End tsql (.sql) Code.
Total article views: 10633 | Views in the last 30 days: 7
 
Related Articles
FORUM

allow varchar value to string and decimal parts

allow string and decimal from a varchar string

FORUM

convert the string value into varchar

convert the string value into varchar

FORUM

Getting the VARBINARY data into a VARCHAR field

CONVERT, VARBINARY, VARCHAR, STRING

FORUM

Pipe delimited VARCHAR column

Need to expand a pipe delimited string stored in a VARCHAR(4096)

SCRIPT

Retrieve numbers from a string

This function will take a string parameter and find all numbers in it.

Tags
dts    
miscellaneous    
programming    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones