?
Solved

How to Import an Excel File Into SQL Server 2000 Using .NET?

Posted on 2008-10-01
14
Medium Priority
?
965 Views
Last Modified: 2013-12-17
Good Day:

I am creating a WinForms application using VB.NET 2005 with SQL Sever 2000.  One of the Experts from Experts Exchange gave me the following code to create a DataSource from an Excel file. My question is how do I perform a batch insert into my SQL Server table called Quote?  Or is there a better way of importing an Excel file into SQL Sever 2000 using .NET?  Or should I put the Excel file in a grid and try to iterate through the rows to insert the Excel data into SQL Server?

Thanks,
Denise

Private Function GetdatafromExcel(ByVal pFilename As String, ByVal pRangeName As String) As System.Data.DataSet
       
        'Returns dataset from named range
        Dim l_strConn As String = String.Format("{0}{1}{2}{3}", _
                                                "Provider=Microsoft.Jet.OLEDB.4.0;", _
                                                "Data Source=", _
                                                pFilename, _
                                                ";Extended Properties=Excel 8.0;")

        Dim l_Conn As New OleDbConnection(l_strConn)
        l_Conn.Open()

        'Create Objects and grab data
        Dim l_Cmd As New OleDbCommand("SELECT * FROM " & pRangeName, l_Conn)

        Dim l_DA As New OleDbDataAdapter()
        l_DA.SelectCommand = l_Cmd

        'Fill Dataset
        Dim l_DS As New DataSet()
        l_DA.Fill(l_DS)

        'Clean Up and Return
        l_Conn.Close()
        Return l_DS

    End Function
0
Comment
Question by:DeniseGoodheart
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 2

Assisted Solution

by:koolnurd
koolnurd earned 60 total points
ID: 22617648
Create a new SSIS package.  Make the Excel file the source document and the the new table the destination tablename.
0
 

Author Comment

by:DeniseGoodheart
ID: 22617777
Thanks for your suggestion but I do not think I can use the DTS Package because many users will be importing different file names from their computer.  I believe the DTS works when it has one specific file name at one specific directory location.  
0
 
LVL 3

Accepted Solution

by:
FMabey earned 720 total points
ID: 22625095
I have used DTS packages like you are trying to by copying the file for import and renaming it placing it in a folder on the users computer. This way it is always called the same and always in the same place.

Here's my code:

' Copy the file that the user wishes to import into the required location
        Dim STR_file1 As String

        If OFD_import.ShowDialog() = DialogResult.OK Then

            Dim STR_FilePathAndName As String = OFD_import.FileName

            STR_file1 = "C:\Import\Import.txt"

            ' Remove the file from the import folder - this file cannot exist a new one is copied over
            If System.IO.File.Exists(STR_file1) = True Then

                System.IO.File.Delete(STR_file1)

            End If

            ' Copy the file to the import folder
            If System.IO.File.Exists(STR_FilePathAndName) = True Then

                System.IO.File.Copy(STR_FilePathAndName, STR_file1)

            End If

            '///////////////////////////////////////////////////////////////////////////////////////////////

            ' Run the DTS package
            Dim oPackage As DTS.Package = New DTS.Package()
            oPackage.LoadFromSQLServer( _
                ServerName:="SQLSVR1", _
                Flags:=DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, _
                PackageName:="Part Manager Import")
            oPackage.Execute()
            oPackage.UnInitialize()
            oPackage = Nothing

Any queries please ask.

Cheers
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DeniseGoodheart
ID: 22625123
Perhaps I need to start a new thread and ask about how to do a bulk insert using VB.NET; since it has the System.Data.SqlClient namespace called SqlBulkCopy.  
0
 
LVL 3

Expert Comment

by:FMabey
ID: 22625152
Have you tried my suggestion? It works fine for me and I think it will do what you want.
0
 

Author Comment

by:DeniseGoodheart
ID: 22625582
What happens when 2 users are running the DTS at the same time?
0
 
LVL 26

Assisted Solution

by:Anurag Thakur
Anurag Thakur earned 720 total points
ID: 22626967
simplest way out for me will be to copy the file somewhere on the network (shared folder which is accessible from the sql server machine) and the use the follwoing qyery (dynamically by changing the filename and sheet name if its different) to import the data
select *
 from openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\report\data.xls;HDR=YES', 'select * from [Sheet1$]')
0
 

Author Comment

by:DeniseGoodheart
ID: 22627603
ragi007:

Running DTS on VB.NET requires a DTS Package name.  The DTS Package imports a specific fille name from a specific location.  By changing the names of the file; DTS will not be able to import it.
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22628017
this is not a DTS
its a openrowset which you can call from a stored procedure
0
 
LVL 3

Expert Comment

by:FMabey
ID: 22631655
The code I've supplied you will change the name of the file so that it will always be the same. Therefore, you will not have an issue with file names being different as you will always change the filename to be the same.
0
 
LVL 3

Expert Comment

by:FMabey
ID: 22634644
2 users at the same time? That I'm not to sure about.
You could, as you have mentioned, use bulk copy.

Here is a sample of the sort of code you'd require:

CREATE PROCEDURE [dbo].[sp_TXT_WORKORD] AS
BEGIN
DECLARE @bcpCommand VARCHAR(8000)

SET @bcpCommand = 'bcp partman.dbo.vw_export_workord out c:\PartManExport\WorkOrders.txt -S SQLSVR1 -T  -c'
EXEC master..xp_cmdshell @bcpCommand
            
END
GO

This copies info from a sql server table and into a text file. You simply need to reverse this.
0
 

Author Comment

by:DeniseGoodheart
ID: 22635110
ragi0017:

Perhaps you can show me your entire code so I understand more.

Thanks
0
 

Author Comment

by:DeniseGoodheart
ID: 22635157
FMabey:

Thanks for your code.  I will have to do some experimenting.
0
 
LVL 26

Expert Comment

by:Anurag Thakur
ID: 22638502
sorry for the delayed response

what you can do is when the user uploads the excel file for data import you can copy/move the file to a particular directory from where the sql server machine has read access to that folder and save the full path of the XLS file somewhere

you can then create a stored procedure and pass in the full path name of the xls file and what ever other required parameters to the stored procedure

in the stored procedure you can create a dynamic sql statement and execute it as shown below
if you want to directly insert into the required table then you can modify the sql statement directly

set quoted_identifier off
declare @SQL VarChar(1000)
Select @SQL = "select * from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=" + @Path +";HDR=YES', 'select * from [Sheet1$]')"
execute (@sql)

i will be glad to help if there are anything else
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question