CSV to Database Help!!

[heeroyu16]

Hi Guys, I'm having troubling with my application to get data from an excel file, save it to a CSV, and export it over to a DB.

First Problem, Excel doesn't seems to close after i convert xls to csv.

Second problem i need to concat the last two columns of the CSV file to obtain a timestamp of the data as of the xls.

This is my coding so far

Private Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click
 
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim xlformat As Excel.XlFileFormat = Excel.XlFileFormat.xlCSV
        Dim c As New CSV_TO_DATA_TABLE.CSV_CONVERT.CSV_TO_DATA_TABLE
        Dim dt As DataTable
 
        xlApp = CreateObject("Excel.Application")
        xlBook = xlApp.Workbooks.Open("C:\Program Files\STOCK TAKE SYSTEM\STS.XLS")
        xlBook.SaveAs("C:\Program Files\STOCK TAKE SYSTEM\STS.CSV", FileFormat:=xlformat)
        xlBook.Close()
        xlBook.Saved = True
        xlApp.Quit()
 
        Dim myDriver As New OleDb.OleDbConnection("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\Program Files\STOCK TAKE SYSTEM;Extensions=asc,csv,tab,txt;")
        Dim myConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\STOCK TAKE SYSTEM;Extended Properties=""text;HDR=Yes;FMT=Delimited""")
        Dim insertData As New DataSet
        Dim mydatatable As DataTable
        Dim ds As DataSet
        Dim sql As String
 
        connectDB = New connection("SAMPLE", "", "")
        Dim myDataAdapter As New OleDb.OleDbDataAdapter("SELECT * FROM [STS.CSV]", myConnection)
        ' ds = connectdb.updateRecord("insert into Sample.SRST_TEST ("
 
        Try
            myDataAdapter.Fill(insertData)
            DataGrid1.DataSource = insertData
 
        Catch Ex As Exception
            MessageBox.Show(Ex.Message)
        End Try
        'closes connection upon complete
        myConnection.Close()    
 
    End Sub

I'm suppose to make an application, non web based

Thanks in advance

[heeroyu16]

so nobody can help me... ?

[jmcilhinney]

You don't need all those steps. You can use ADO.NET to retrieve the data from the XLS directly into a DataTable, which you can then insert into any database. See www.connectionstrings.com for details of connecting to Excel. It's virtually the same as connecting to a CSV file. I'd be inclined to use OLEDB over ODBC.

[heeroyu16]

do you mean by using this??

Private Function InsertSql()
        Dim myDriver As New OleDb.OleDbConnection("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\Program Files\STOCK TAKE SYSTEM;Extensions=asc,csv,tab,txt;")
        Dim myConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\STOCK TAKE SYSTEM;Extended Properties=""text;HDR=Yes;FMT=Delimited""")
        Dim insertData As New DataSet
        Dim mydatatable As DataTable
        Dim ds As DataSet
        Dim sql As String
 
      Dim myDataAdapter As New OleDb.OleDbDataAdapter("SELECT * FROM [STS.CSV]", myConnection)
        ' ds = connectdb.updateRecord("insert into Sample.SRST_TEST ("
 
        Try
            myDataAdapter.Fill(insertData)
            dg.DataSource = insertData
        Catch Ex As Exception
            MessageBox.Show(Ex.Message)
        End Try
        'closes connection upon complete
        myConnection.Close()
    End Function

Or directly read the xls file
but inserting into my database is abit more troublesome as some columns are fixed by my predessor. so i have to extract out the individual columns, check if there are any duplicate data entry, by timestamp and a column with serial numbers.

[jmcilhinney]

None of that matters. You don't need a CSV at all. You simply read the data directly from the XLS into a DataTable using ADO.NET. You can then either manipulate that DataTable as needed or create a new DataTable with the appropriate schema for the target database and transfer the data from the existing DataTable to the new DataTable as needed. You can then simply save the new DataTable to the database.

[heeroyu16]

hmm, okie okie i'll try tat thanks alot
JM