Import .csv file and process

snufse · October 23rd, 2009, 08:31 AM

I need to write a windows app (vb 2005) that does following:
1. Imports a .csv file
2. Selects certain rows from the .csv file
3. Splits the .csv file into fields based on delimiter
4. Populate a db2/400 table with only some of the fields.

Example of rows:

Code:

 
061000104,6607607911512,Pavex Corporation,20091005,100,33824.59,,,Total Credits
061000104,6607607911512,Pavex Corporation,20091005,400,33824.59,,,Total Debits
061000104,6607607911512,Pavex Corporation,20091005,478,33824.59,,,Total Cpr Paid Checks
061000104,6607607911512,Pavex Corporation,20091005,275,33824.59,ZBC0627008000093, ,ZBA Credit
061000104,6607607911512,Pavex Corporation,20091005,472,33824.59,SUDR000000000000, ,Debit For List Post Summary
061000104,6607607911512,Pavex Corporation,20091005,479,19935.00,CPRP0097280285I,53544,CPR Paid Check
061000104,6607607911512,Pavex Corporation,20091005,479,37.83,CPRP0095037189I,53550,CPR Paid Check
061000104,6607607911512,Pavex Corporation,20091005,479,575.00,CPRP0096056850I,53561,CPR Paid Check

Need to select only rows with "Paid Check". Field3, field4, field5, field6 and field7 only will be inserted into the db2/400 table.
Does anyone have code sample I could use? Thank you.

gbianchi · October 23rd, 2009, 08:45 AM

Hello...

Google is your friend (and usually faster that us)...

Try: import cvs file and parse vb.net

Lot's of links (code, libraries and others!!) If you have any question about what you found, ask again.

snufse · October 23rd, 2009, 09:47 AM

I have the code in place to fill my data set, it works fine.
Question: In my "Select..." clause is it possible to to a "%Like" and get only rows that have the phrase "Paid Check"?

Code:

 
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim myConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=Text;"
Dim myConnection As New OleDbConnection(myConnectionString)
myConnection.Open()
Dim myCmdSelect As New OleDbCommand("SELECT * FROM SunTrust.csv", myConnection)
Dim myAdapter As New OleDbDataAdapter()
myAdapter.SelectCommand = myCmdSelect
Dim myDataset As New DataSet()
myAdapter.Fill(myDataset, "Suntrust")
myConnection.Close()
End Sub

gbianchi · October 23rd, 2009, 10:51 AM

mmm.. not sure, you don't have fields definitions..

but what about filter the dataset after you loaded it??