Insert CSV file data into the pl/sql Database using VB.NET

Question

I am reading a csv file by a Console Application, and up to this point is returning me correctly.

Now I’m having trouble understanding how to include each field in a Bank column.

This is the code I have so far.

 Public Function LerCsvConfrec()
        Dim sb As New StringBuilder
        Dim ds As New DataSet
        Dim dtConfrec As New DataTable

        Dim maquinaProducao As String = "PRODUCAO"

        Dim pastaOrigem As String = ""
        Dim pastaDestino As String = ""

        If Environment.MachineName = "PRODUCAO" Then
            'PRODUÇÃO
            pastaOrigem = "C:\KAWASAKI\FTP\SUPPORTE-UBERLANDIA\INBOUND\CONFREC\"
            pastaDestino = "C:\KAWASAKI\FTP\SUPPORTE-UBERLANDIA\INBOUND\CONFREC\Lidos\"
        Else
            'TESTE
            pastaOrigem = "C:\temp\Confrec\"
            pastaDestino = "C:\temp\Confrec\Lidos\"
        End If

        'Busca arquivos .csv na Pasta de Origem
        Dim dirCsv As String() = Directory.GetFiles(pastaOrigem, "*.csv")

        Try

            For Each csvFile In dirCsv
                'Lê o conteúdo de cada arquivo
                Dim arqCsv As String = File.ReadAllText(csvFile)
                For Each linha As String In arqCsv.Split(vbCrLf)

                    'Verifica se não está vazio
                    If Not String.IsNullOrEmpty(linha) Then

                        'Delimitador de separação usado (;)
                        For Each coluna As String In linha.Split(";")

                        Next
                    End If

                Next

            Next

        Catch ex As Exception

        End Try

    End Function

I checked the information that returns and every time it passes by

For Each coluna As String In linha.Split(";")

Next

it returns the right information, but I was unable to enter it in the database. How can I make each column of the CSV enter the correct column of the Database ?

Answer 1

I was able to record the CSV in the database using a String Array to store each information in an array position.

Stayed that way:

 Public Function LerCsvConfrec(ByVal conexao As OracleClient.OracleConnection)
            Dim sb As New StringBuilder
            Dim ds As New DataSet
            Dim dtConfrec As New DataTable

            Dim maquinaProducao As String = "KAWASAKIBRASIL"

            Dim pastaOrigem As String = ""
            Dim pastaDestino As String = ""

            Dim modelo As String = ""
            Dim cor As String = ""

            Dim fileName As String = ""

            If Environment.MachineName = maquinaProducao Then
                'PRODUÇÃO
                pastaOrigem = "C:\KAWASAKI\FTP\SUPPORTE-UBERLANDIA\INBOUND\CONFREC\"
                pastaDestino = "C:\KAWASAKI\FTP\SUPPORTE-UBERLANDIA\INBOUND\CONFREC\Lidos\"
            Else
                'TESTE
                pastaOrigem = "C:\temp\Confrec\"
                pastaDestino = "C:\temp\Confrec\Lidos\"
            End If

            Dim dirCsv As String() = Directory.GetFiles(pastaOrigem, "*.csv")

            Try

                For Each csvFile In dirCsv
                    'Retorna nome do arquivo
                    fileName = Path.GetFileName(csvFile)

                    Dim arqCsv As String = File.ReadAllText(csvFile)

                    For Each linha As String In arqCsv.Split(vbCrLf)

                        'Verifica se arquivo está vazio.
                        If linha <> vbLf AndAlso Not String.IsNullOrEmpty(linha) Then

                            'Preenche lista com itens da linha
                            Dim listCsv(8) As String

                            'Contador
                            Dim i As Integer = 0

                            'Para cada coluna busca a informação da coluna a ser utilizada
                            For Each coluna As String In linha.Split(";")
                                listCsv(i) = coluna
                                i = i + 1
                            Next

                            modelo = Mid(listCsv(0), 1, InStr(listCsv(0), " ")).Trim
                            cor = Mid(listCsv(0), InStr(listCsv(0), " ")).Trim
                            'INSERE CSV CONFREC NA TABELA
                            sb = New StringBuilder
                            sb.Append(" INSERT INTO ")
                            sb.Append(" CONFREC ")
                            sb.Append(" ( ")
                            sb.Append(" MODELO, ")
                            sb.Append(" COR, ")
                            sb.Append(" ANO_MODELO, ")
                            sb.Append(" ANO_FAB, ")
                            sb.Append(" CHASSIS, ")
                            sb.Append(" STATUS, ")
                            sb.Append(" DT_REMESSA, ")
                            sb.Append(" NF_REMESSA, ")
                            sb.Append(" DT_ENTRADA_ESTOQUE ")
                            sb.Append(" ) ")
                            sb.Append(" values ( ")
                            sb.Append("'" & modelo & "',")                                                                                   'MODELO
                            sb.Append("'" & cor & "',")                                                                                      'COR
                            sb.Append(listCsv(1).ToString.Trim & ",")                                                                        'ANO_MODELO
                            sb.Append(listCsv(2.ToString.Trim) & ",")                                                                        'ANO_FAB
                            sb.Append("'" & listCsv(3).ToString.Trim & "',")                                                                 'CHASSIS
                            sb.Append(listCsv(4).ToString.Trim & ",")                                                                        'STATUS
                            sb.Append("to_date( '" & listCsv(5).ToString.Trim & "', 'dd/MM/yyyy'), ")                                        'DT_REMESSA
                            sb.Append(listCsv(6).ToString.Trim & ",")                                                                        'NF_REMESSA
                            sb.Append("to_date('" & listCsv(7).ToString.Trim & " " & listCsv(8).ToString.Trim & "', 'dd/MM/yyyy HH24:mi') ") 'DT_ENTRADA_ESTOQUE
                            sb.Append(" ) ")
                            ExecuteNonQuery(sb.ToString, conexao)
 End If

                Next

                'Move Arquivo para pasta de Destino
                File.Move(csvFile, pastaDestino + fileName)
            Next

        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Console.WriteLine(ex.StackTrace)
        End Try

    End Function

Answer 2

Try to use this code:

Dim conn As OdbcConnection
Dim cmd As OdbcCommand
Dim da As OdbcDataAdapter
Dim dt As DataTable

Dim connectionStringExcel As String = "Driver={0};DriverId=790;Dbq={1};DefaultDir={2};Extended Properties=""HDR=yes"";"
Dim driverExcel As String = "{Microsoft Excel Driver (*.csv)}"
conn = New OdbcConnection(String.Format(connectionStringExcel, driverExcel, odb.FileName, Path.GetDirectoryName(odb.FileName)))
cmd = New OdbcCommand("select * from [Plan1$]", conn)
da = New OdbcDataAdapter(cmd)
dt = New DataTable
da.Fill(dt)

    For Each dr As DataRow In dt.Rows

        'Aqui você vai ter o dr das colunas que pode gravar no banco.

    Next