how to code VB.net2008 Import excel to oracle database

how to code vb.net2008 Import excel sheet to oracle database. pls send example code to me thank you very much
ANDYCHANAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DanielSVCommented:
You could export your Excel worksheet as a csv-document (because it's a really simple format, easy to work with), then load it into your application using e.g. TextReader.

Parse it, and save it to the DB. Peanuts.

Example-code for getting VB.NET to talk to Oracle: http://msdn.microsoft.com/en-us/library/xexk8kx3(VS.71).aspx
0
ANDYCHANAuthor Commented:
Thank you very much for reply me. I can connect oracle database already. but can't import excel sheet to temp table in oracle. can see my code as below. pls ,help me code at point "????????????????"  thank you very much.
       
--------------------------------------vb code-----------------------------------------------------------
connectdb                                                'call database connection module
          try
            Dim tmpsql As String = " ??????????????????????????????????????"
            cmd = New OracleCommand(tmpsql, conn)
            dr = cmd.ExecuteReader
            dr.Read()

            cmd = New OracleCommand("SELECT * FROM CUST_ORDER_IMPORT_FORECAST_TAB")
            dr = cmd.ExecuteReader
            dr.Read()
                 messagebox.show ("test ")
            Dim dt As New DataTable
            dt.Load(dr)
            DataGridView1.DataSource = dt
            DataGridView1.Refresh()
            Label2.Text = "Total Record   " & DataGridView1.Rows.Count & "  Record"

        Catch ex As Exception
            MessageBox.Show("Can't Import Data" & vbCrLf & vbCrLf & ex.Message)
        End Try
0
DanielSVCommented:
How is the format of the Excel-document?
(What kind of data is stored in what column/row in the document?)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ANDYCHANAuthor Commented:
CSV format can see file as attechment thank you
book11.csv
0
DanielSVCommented:
As I don't have VB.NET installed on this computer, I instead wrote it in C#.

This code should do the trick, if I understood the task correctly.
string DBFields;
            List<string> UpdateList = new List<string>();            

            using (TextReader CSVDoc = new StreamReader(@"CSV path"))
            {
                DBFields = CSVDoc.ReadLine();

                string TMPString = CSVDoc.ReadLine();

                while (TMPString != null)
                {
                    string[] TMPArray = TMPString.Split(',');
                    string InsertString = "";

                    for (int i = 0; i < TMPArray.Length; i++ )
                        InsertString += "'" + TMPArray[i] + ((i + 1 == TMPArray.Length) ? ("'") : ("', "));

                    UpdateList.Add("INSERT INTO *tablename* (" + DBFields + ") VALUES (" + InsertString + ");");
                    
                    TMPString = CSVDoc.ReadLine();
                }
            }

Open in new window

0
ANDYCHANAuthor Commented:
thank u very much. but C# I can't can tell me discription your code c# please.  thank u.
0
ANDYCHANAuthor Commented:
I trying to edit and run vb.net this code but  can't success .help me pls. thank u verymuch.

INSERT INTO CUST_ORDER_IMPORT_FORECAST_TAB  
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',  'Data Source="C:\Documents and Settings\Administrator\Desktop\book11.xls".[Sheet1$];Extended Properties=Excel 8.0')
0
DanielSVCommented:
The C# code generates the inserts you need to insert the data into a table formed like the one in the xls document.

You feed it the csv file, and it generates the queries to fill a table with the same columns as the document got.

I can't explain better, or convert the code to VB.NET right now, as I'm on my non-IT work atm I can't help furtherer before I get home in a few hours. I'll have a look at it then, if not somebody else solved your problem by then.
0
DanielSVCommented:
Modify InsertIntoDB to your own needs. I haven't tested it, and I didn't use the Oracle data connector.

GetInsertQueriesFromCSV loads the CSV-file specified in iFile, parses it, and generates a list of queries. The you pass that list to InsertIntoDB. InsertIntoDB is supposed to run the queries, and insert the data into a table with those columns:

TEMP_REC_NO,RCV_ORDER_NO,RCV_ORDER_DETAIL_NO,CUSTOMER_CODE,CUSTOMER_ORDER_NO,CUST_DELIV_DATE,DELIVERY_DATE,DELIV_CUSTOMER_CODE,DELIV_CUSTOMER_NAME,DELIV_CUSTOMER_CHARGE,ENDUSER_CUSTOMER_CODE,ENDUSER_CUSTOMER_NAME,RECEIVE_ORDER_DATE,PART_NO,CUST_PART_NO,CUST_ORDER_QTY,SALES_PRICE,SALES_AMOUNT,NOTE_TEXT,CUST_ORDER_TYPE,LAST_UPD_DATE,LOAD_FLAG,ERROR_TEXT,TE_INHERIT_CODE,TE_RECEIPT_SECTION,TE_DELIVERY_LOCATION_CODE,TE_TEC_DWG_NO,BUY_UNIT_MEAS,CURRENCY_CODE,TE_PLANNED_SHIP_NO,TE_PLANNED_SAIL_IN_DATE,TE_CHANGED_PART_NO,PART_COLOR_CODE,PLAN_CODE,DELIVERY_TIME,CHAR_EXTRA_1,CHAR_EXTRA_2,CHAR_EXTRA_3,NUM_EXTRA_1,NUM_EXTRA_2,NUM_EXTRA_3
Private Function GetInsertQueriesFromCSV(ByVal iFile As String) As List(Of String)
        Dim UpdateList As New List(Of String)
        Dim DBFields As String = ""


        Try
            Using CSVDoc As TextReader = New StreamReader(iFile)
                DBFields = CSVDoc.ReadLine()
                Dim TMPString As String = CSVDoc.ReadLine()

                While TMPString IsNot Nothing
                    Dim TMPArray() As String = TMPString.Split(",")
                    Dim InsertString As String = ""

                    For i = 0 To TMPArray.Length - 1 Step 1
                        InsertString += "'" + TMPArray(i) + If(i + 1 = TMPArray.Length, "'", "', ")
                    Next

                    UpdateList.Add("INSERT INTO *tablename* (" + DBFields + ") VALUES (" + InsertString + ");")

                    TMPString = CSVDoc.ReadLine()
                End While
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        Return UpdateList
    End Function

    Private Sub InsertIntoDB(ByVal iList)
        Using SqlConn As New OdbcConnection("ConnectionString")
            Dim SqlComm As New OdbcCommand()

            SqlComm.Connection = SqlConn

            Try
                SqlConn.Open()

                If SqlConn.State <> ConnectionState.Open Then
                    Throw New Exception("The connection is not open :S")
                End If

                For Each Query As String In iList
                    SqlComm.CommandText = Query
                    If (SqlComm.ExecuteNonQuery() = 0) Then
                        MessageBox.Show(Query, "Malformed Query")
                    End If
                Next


            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

        End Using
    End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ANDYCHANAuthor Commented:
i try run follow your code but can't, you can give me example code to me ? thank you very much
0
DanielSVCommented:
What do you mean?

I just gave you the code you need, all you need to do is to fix the function inserting it into the DB, according to your needs. Also, you need a table in your db, with the layout I described above.
0
ANDYCHANAuthor Commented:
thank you very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.