How to read pipe delimeted file and export the data to Oracle?
- Thread starter Nisha03
- Start date
Use a TextFieldParser to read the file and populate a DataTable. The MSDN documentation for the class has code examples. You can then use a DataAdapter to save the data to the database.
Someone already has helped.
Have you looked, for that part at least? Make what efforts you can rather than wait for something you can just copy and paste.
So what have you got so far? If we see that then we can help you extend it. For myself, I'm not keen on just providing code from the outset but, if I see effort, I'm more than happy to provide assistance, whether that be guidance, code or both.
I am trying with below code, and now I am trying to fetch the value and trying for inserting it in Oracle database. Thanks for the help.
Using myReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\TEST\test.txt")
myReader.TextFieldType = FileIO.FieldType.Delimited
myReader.SetDelimiters("|")
Dim currentRow As String()
While Not myReader.EndOfData
Try
currentRow = myReader.ReadFields()
Dim currentField As String
For Each currentField In currentRow
MsgBox(currentField)
Next
MsgBox("next line")
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
End Try
End While
End Using
Using myReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\TEST\test.txt")
myReader.TextFieldType = FileIO.FieldType.Delimited
myReader.SetDelimiters("|")
Dim currentRow As String()
While Not myReader.EndOfData
Try
currentRow = myReader.ReadFields()
Dim currentField As String
For Each currentField In currentRow
MsgBox(currentField)
Next
MsgBox("next line")
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
End Try
End While
End Using
Sweet. Now, you would want to use a DataTable and an OracleDataAdapter. The first step would be to download the ADO.NET provider for Oracle from the Oracle web site. Download and install ODP.NET and then add the appropriate reference to your project. You can now use the Oracle provider just as you would the providers built into the Framework, e.g. OleDb and SqlClient.
I would create an OracleDataAdapter. Using the appropriate query, you can call FillSchema on it to build the appropriate schema in a DataTable. You can then populate that DataTable with the data from the file. Once you're done, call Update on the adapter to insert the records into the database. You might like to take a look at this to get a feel for ADO.NET:
Retrieving and Saving Data in Databases
Post #3 is most relevant to you, but note that you will be adding the rows in your loop.
I would create an OracleDataAdapter. Using the appropriate query, you can call FillSchema on it to build the appropriate schema in a DataTable. You can then populate that DataTable with the data from the file. Once you're done, call Update on the adapter to insert the records into the database. You might like to take a look at this to get a feel for ADO.NET:
Retrieving and Saving Data in Databases
Post #3 is most relevant to you, but note that you will be adding the rows in your loop.
