VB.NET: How To Copy Data From Excel Workbook To Specific Table In SQL Server Database

[SteveVB]

Hi All,

I need help with an example to achieve the following task.

I have an excel workbook which contains 4-5 columns, No Headers and 40,000 rows of data. I would like to use a form which would allow the user to use a ShowFiledialog to locate the specific file and copy the contents of this sheet to a SQL Server Database Table. The data from the spreadsheet columns should however get copied to the correct columns in the SQL DB Table.

I also need to concatenate the values from each cell on each row so that duplicate entries do not get added to the table. If an updated spreadsheet is received the next day, the new data should be appended to the existing data in the same table.

If this requires the use of a dataset or datatable, I need resources to study and be familiar on using/manipulating data stored in these type of variables.

I would really appreciate if someone could help me achieve this.

Thanks,

Steve

[GBeats]

I was looking into opening excel files using the ole db and inserting into a new excel file and i cam across this function.

have a quick look through it and maybe tinker around with some variables, you should learn the basics on adding data to a dataset
its really just like excel.

NOTE: you need to create the rows/columns before you can put data there, and i didnt find anything else doing it any other way but there maybe.

funnily enough i just checked and its in this forum
Link

[SteveVB]

Hi Gbeats,

I however need a more detailed example to know who to intialize a datatable, how to copy the range from an excel sheet which has no column names into this datatable and how to move this data from the datatable to a SQL database which has field/column names

I would appreciate if anyone can help me, so far many have viewed but not many replies

[dunfiddlin]

'To extract an Excel sheet as datatable
' This is for the newer xlsx format, older format requires Jet4 connection string
    Dim c As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xlsx; Extended Properties=""Excel 12.0; HDR=No; IMEX=1;""")
    Dim dt As New DataTable
 
        Dim cmd As String = "SELECT * FROM [Sheet1$]" ' this uses full sheet but any range can be defined
        Dim da As New OleDbDataAdapter(cmd, c)
 
        c.Open()
        da.Fill(dt)
        c.Close()

For the next step look at the documentation for the .Merge method.

[szlamany]

40,000 rows is in the realm of requiring bulk-insert.

The need to pre-process and remove duplicates is best done on the server...

Load the excel data into a staging table - each row is represented that way.

Remove duplicates from this staging table - easiest is by inserting DISTINCT rows into a second staging table.

Now you have a clean set of data and can perform the insert into the production table in a single T-SQL statement.

This is the way you are supposed to manage 40,000 rows with a SQL database.

[GBeats]

Here is what i was practicing with

Imports System.IO
Imports Microsoft.Office.Interop
Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim TestFile As String = My.Computer.FileSystem.SpecialDirectories.Desktop & "test.xlsx"
Dim ConnectionString As String = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Main Admin\\Desktop\\Test.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=No"";"
Try
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

MyConnection = New System.Data.OleDb.OleDbConnection(ConnectionString)
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [test$]", MyConnection)
MyCommand.TableMappings.Add("Table", "TestTable")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)
MyConnection.Close()

Dim xlApp As New Excel.Application
Dim xlSheet As Excel.Worksheet
xlApp.Visible = True
xlApp.Workbooks.Add()
xlSheet = xlApp.ActiveSheet


xlSheet.Range("C3").CopyFromRecordset(DatagridviewToDataset(DataGridView1).Tables(0).DataSet)

Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub

-Red Highlight: Connection Strings. this needs to be perfectly written and will change for different versions of office, theres also arguments for table headers etc, check online "excel connection strings"
-Gold Highlight: this is the code to get the data from excel and put it into a dataset then in turn i put it into a datagridview on my form so i could see it.

-The rest: i was trying to bulk transfer from a dataset to a blank excel sheet, but it wasnt working, row by row is not effective for large documents, maybedoingit with another db connection would work row by row since the excel app is very very slow.

DatagridviewToDataset is the method thats posted above that i showed you earlier

[GBeats]

i cant explain everything in there since im learning myself, but the connection part works fine.

[kareninstructor]

I agree with slamany, if the part to move to a staging area (never a good idea to move a large amount of data into production) just be done via code you could tinker with the following

Create a connection to the Excel files

Example

Dim ConnectionNoHeader As String =
    "provider= Microsoft.ACE.OLEDB.12.0; data source='YourExcelFileNameGoesHEre';Extended Properties=""Excel 12.0; HDR=No;"""

VS2010 syntax VB.NET for importing data from a sheet (needs changes like which sheet, what is the table name and database name)

Dim ExcelCommand As New OleDb.OleDbCommand With
    {
        .CommandText =
        <SQL>
            SELECT * INTO [YourTableToInsertInto] FROM [Sheet1$] IN '' 
            [ODBC;Driver={SQL Server};Server=(local);Database=YourDatabase;Trusted_Connection=yes];                                   
        </SQL>.Value,
        .Connection = ExcelConnection
    }

[SteveVB]

Hi All,

Thank you for your inputs. I will use these ideas to implement the solution.

I have a small correction to make, the data comes in .csv files, how would the connection string change in this case?

szlamany, ur advice looks like what I need to set me on track, however can you provide a sample code illustrating the end to end activity? What is confusing me is how I can add to a SQL table which has headers and ensure that the data which is in the .csv (which has no headers) are inserted into the right places or under the right field/column in the SQL table. My expertise with stagingtables, datatables etc is that of a novice. I read about SQLBulkcopy and that seems to be exactly what I want.

Similarly, once the data from the csv is in a datatable how do i manipulate it? If it were in excel i cld add a new column and create a primary key which would be the concatenation of each cell value in a row. How do i loop in a datatable to check for a condition and call a different sub routine etc

[kareninstructor]

Reading CSV files try using TextFieldParser class.

Read Text file into a DataTable

Dim FileName As String = "People.txt"
Private Sub DemoReadTxt_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.Jet.OLEDB.4.0",
            .DataSource = Application.StartupPath & IO.Path.DirectorySeparatorChar
        }

    Builder.Add("Extended Properties", "text;HDR=Yes;FMT=Delimited(,)")

    Using cn As New OleDbConnection With
        {
            .ConnectionString = Builder.ConnectionString
        }

        Using cmd As New OleDbCommand With
            {
                .Connection = cn,
                .CommandText =
                <SQL>
                    SELECT * 
                    FROM <%= FileName %>
                </SQL>.Value
            }

            Dim dt As New DataTable

            cn.Open()
            dt.Load(cmd.ExecuteReader)
   
        End Using
    End Using
End Sub

Add a primary key

Dim dt As New DataTable

' Load data

dt.Columns.Add(
    New DataColumn With
    {
        .ColumnName = "ID",
        .DataType = GetType(Int32),
        .AutoIncrement = True,
        .AutoIncrementSeed = 1,
        .ReadOnly = True
    }
)

To concatenate two fields example (assumes we have FirstName and LastName columns)

If we have headers

dt.Columns.Add(
    New DataColumn With
    {
        .ColumnName = "FullName",
        .DataType = GetType(String),
        .Expression = "FirstName + ' ' + LastName"
    }
)

No headers

dt.Columns.Add(
    New DataColumn With
    {
        .ColumnName = "FullName",
        .DataType = GetType(String),
        .Expression = "F1 + ' ' + F2"
    }
)

[SteveVB]

Hi All,

I am having another problem which I am hoping you guys may be able to throw some light on.

I created a database in SQL Server Express 2008 R2 and then I copied the database onto my local Desktop. What I would like to do is use this Database, just like how you would use an Access Database to add my data into. The problem is when I try to link this DB to my Data Grid View to obtain the connection string it throws an error stating that it is not supported. The reason I need to do it this way is because I may have to shift the location of the DB from one place to another and owing to the fact that the company will not allow me to host the DB on a production server until the POC is finalized.

Am I doing something wrong? My assumption is that this is possible...

Also, If I create a DB using SQL Server Standard, can I use SQL Management Express to view the contents of the database?

My company will only let me install a free version in this case it is SQL Express but I want to be able to showcase this idea and eventually migrate the design to a live server.

[szlamany]

You can create a DB with SQL express and migrate it to a STANDARD edition.

You cannot move a DB from SQL 2008 to 2005 though - so be careful to use an EXPRESS edition that can migrate to the SAME LEVEL or a HIGHER LEVEL on the PRODUCTION server.

Yes - you can use SSMS Express to work with a DB regardless of whether it is an EXPRESS service running or a STANDARD service running.

To move a DB use BACKUP and then RESTORE.

Search the forum for the following and you will get lots of threads on how to do backup.

szlamany backup

Never ever ever COPY a MS SQL DB - it's attached to a running SERVICE and the "state" of the MDB (data) and LDB (log) are only "known" to the SERVICE - thus BACKUP and RESTORE