Bulk Insert from Excel to sql server
-
hi,
I have my database, in my web server and i want to insert some information from Excel which contains some 700 records which contains same type of data which my database tables are designed. Can i insert these data's through DTS or is there any utility to do this and also came across BCP ???i don't know what is that.
Can anyone help me in this regard
Arshad Hussain
-
Okay, I'll bite.
In Enterprise Manager, click on Tools, Data Transformation Services, Import Data...
Follow the wizard.
For more information, search Books Online for DTS Import/Export Wizard.
--SJT
-
Be careful with Excel inport with DTS. It does not like mixed data in the same column (ie numbers and text). It will take the first row data as indication of type and if any column does not correspond it will ignore it (null).
BCP is a Bulk Copy Program which allows text file to sql table, sql table to text file transfer. You could save your excel spreadsheet as a csv and then use BCP to load it. Again be careful as Excel will enclose text columns with quotes, BCP treats quotes as data not delimiter.
Far away is close at hand in the images of elsewhere.
Anon. -
You might consider, if you have the Excel XP library at your disposal, exporting the excel to XML, and then importing it from there.
I'm not sure if this is a one time thing, or a service that needs to run. If its a one time thing, then you'll probably not going to do what I just said.
If you created a excel file, and put in row1
uid, lastname, firstname
and then in rows 2-3 put values like
123,smith,john
234,jones,mary
And then exported the file to XML (via code, or by File/Save As (xml)
..
the below xsl stylesheet will help you see how to transform it.
<< START XSL >>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >
<xsl:template match="/">
<OneWellDefinedDS>
<!--<myRootPosition><xsl:value-of select="position()"/></myRootPosition>-->
<xsl:apply-templates select="ss:Workbook"/>
</OneWellDefinedDS>
</xsl:template>
<xsl:template match="ss:Workbook">
<!--<myWorkbookPosition><xsl:value-of select="position()"/></myWorkbookPosition>-->
<xsl:apply-templates select="ss:Worksheet"/>
</xsl:template>
<xsl:template match="ss:Worksheet">
<!--<myWorksheetPosition><xsl:value-of select="position()"/></myWorksheetPosition>-->
<xsl:choose>
<xsl:when test="position()=1">
<xsl:apply-templates select="ss:Table"/>
</xsl:when>
<xsl:otherwise>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template match="ss:Table">
<!--<myTablePosition><xsl:value-of select="position()"/></myTablePosition>-->
<xsl:apply-templates select="ss:Row"/>
</xsl:template>
<xsl:template match="ss:Row">
<!--<myRowPosition><xsl:value-of select="position()"/></myRowPosition>-->
<xsl:choose>
<xsl:when test="position()=1">
</xsl:when>
<xsl:otherwise>
<customerInfo>
<!--<myPosition><xsl:value-of select="position()"/></myPosition>-->
<xsl:apply-templates select="ss:Cell"/>
</customerInfo>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template match="ss:Cell">
<!--<myCellPosition><xsl:value-of select="$varCurrentAttributePosition"/></myCellPosition>-->
<xsl:choose>
<xsl:when test="position()=1">
<xsl:call-template name="xuid" />
</xsl:when>
<xsl:when test="position()=2">
<xsl:call-template name="xlastname" />
</xsl:when>
<xsl:when test="position()=3">
<xsl:call-template name="xfirstname" />
</xsl:when>
<xsl:otherwise>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template name="xuid">
<uid><xsl:value-of select="."/></uid>
</xsl:template>
<xsl:template name="xlastname">
<lastname><xsl:value-of select="."/></lastname>
</xsl:template>
<xsl:template name="xfirstname">
<firstname><xsl:value-of select="."/></firstname>
</xsl:template>
</xsl:stylesheet>
<< END XSL >>
this will make the resultant xml easier to deal with.
I don't know. You probably wont' do this, but I'll put it out there anyway.
-
Iam not sure to use via XML but anyway i will try it later , but BCP sounds useful,can u pls specify clearly how to use BCP and from where i can get it. thanks to David and Sholliday
-
bcp databasename..tablename in textfilename /c /t "," /r "\n" /S servername /U username /P password
This will insert data from a standard comma separated text file
For further info see BOL (Books Online)
Far away is close at hand in the images of elsewhere.
Anon. -
Arshad,
Use DTS in Enterprise Manager (Data Transformation Services|Local Packages).
It will take you literally 5 minutes to set this up; it's incredibly simple. Set up a SQL destination database, an Excel Source, and then link them with a DTT (use the "Create" button to set up your staging table).
there are other more scalable solutions (like xml), but for 700 records of un-mixed data you should use DTS because it's so stinkin' easy.
Signature is NULL
-
Apologies for straying from the topic - but is it possible to output to an excel spreadsheet? I only ask because I have created a lovely DTS package which imports data processes it and then outputs it (with variable file names) to text files, which I then have to manually convert to .xls.
Helen
Helen -
Yes, use an Excel connection. When you apply the transformation sql will know whether yopu want to input or output. Several things to watch out for. The worksheet must exist (you can create it as part of the setup) and DTS will allways append to the worksheet. However you can add additional tasks to drop and create the worksheet (table) before the transformation.
Far away is close at hand in the images of elsewhere.
Anon. -
/*********** insert into Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-
EEP! Well colour me embarrassed! I kept getting thrown by the fact that when I selected the Excell Connection Icon it had 'Data Source' hard coded next to the connection properties, and I had my head set on a 'Data Destination'.
Your suggestions worked fine - sorry for being a time waster!!
Helen -
thanks all for all your, suggestions and all the help u people gave me. Arshad.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply