Need advice - best way to query access data (or read in .csv?)

[vbfbryce]

Here is my scenario. I have a data extract that writes a .csv file daily. My desired end result is that I can look back at the history for a specific "customer order," ie. what information existed for order XYZ on April 15? What info existed for that same order on April 16, 17, etc.?

What I'm doing currently (in VB2010) is importing the .csv files into an Access db, then using an OLEDB connection and data reader, bringing a specific day's "table" into a VB data table, which becomes the data source for a datagridview. Putting it into a datagridview is basically not necessary, because I don't view the data there. I search the data table to find the order info I'm looking for, then populate a listview to display it. I enter an order number into a text box then search the data table for that order number. I have a list of all the tables in my access db, so I select a table first, and then supply the order number I'm searching for, etc.

I'm looking for the most efficient way to do this. I don't really want to populate a datagridview, but am not sure how to bypass that step when creating the table, ie.:

DataGridView1.DataSource = ReaderToTable(datareader)

Is there another way to call this function?

Should I skip the Access piece altogether and just read in the data from the .csv files? Each file has about 40k records.

Any thoughts would be greatly appreciated!

vBryce

[Jenner]

Are you cataloging multiple days worth of CSV file in your database table? If so, I'd keep the import of the daily CSV into Access since this way, your data table contains a history longer than a day. Otherwise, if you know your order number is in a selected CSV, then it's far more efficient and easy to pull the data in directly. I guess the question is, do these daily CSV's contain a full data-dump of all your data or just the last day's worth?

Yes, the DataGridView isn't needed at all, with a little bit of ADO.NET magic, it's easy to pull the records you're looking for from Access. If you want to import the CSV directly into your program, it's even easier. Give me some details on what your CSV looks like and I'll show you some examples you can use.

[Shaggy Hiker]

I'd want the CSV in the database anyways, and not in a bunch of different tables, either. A series of different CSVs is going to be harder to search across than querying a single database table. You may not need a search that does more than just works with individual CSVs at the moment, but functionality has a way of snowballing over time, and the database table will become increasingly valuable if it does that.

I'm not sure why you felt that the datagridview was necessary in the first place. Only the datatable is necessary, which is what ReaderToTable is about, right? The datatable becomes the datasource for the DGV, but it is the table you actually want. The DGV is just a control that can display data in a tabular format. As such, it doesn't care where the data comes from. You can use lots of different datasources, or none at all. The DGV works either way. It's generally easier and more functional than a Listview, too, so I'm not sure why you favor a Listview in this case. The datatable is a data construct in memory which can be used as the datasource for a DGV and for other controls, or not, as needed. It's existence is in no way dependent on how it is to be displayed.

[vbfbryce]

Thanks for responding, guys.

First, in response to Jenner:

I have one .csv file for each day. From the first day an order "appears" until the day it is shipped/invoiced, it will continue to appear in each .csv.

The data structure is pretty simple, 10 or 11 columns, with Order #, Date Received, Expected Ship Date, Order Lead Time, etc.

What I need to be able to do is "trace the order back" to see what (if any) changes have taken place, for example if the expected ship date changed a week before we had originally planned to ship it, that would explain why it was "built early."

Shaggy:

I didn't want a DGV, but the example code I borrowed did it that way, and I was trying to tweak it to meet my needs.

I think what I'm hearing suggested, for starters, is to accumulate the daily .csv files into one table in Access, then do something like what I'm doing (but without the DGV). Is that close?

Thanks!

[Shaggy Hiker]

Yes, I think that sums it up pretty well. I wouldn't be so quick to throw out a DGV as a good way to display a resulting table, but that's because I tend to find the ListView to be rather annoying for grid display.

[kareninstructor]

Hello,

- What is shown below is conceptual, done in VS2010, VB.NET, Option Strict On.
- For the OleDb the file needs to be in ANSI format, does not matter for the second example.
- Fields in the example file do not match yours, the file was kept simple for learning.

The following (figure 2) shows how to read delimited files without the first row being column names using OleDb data provider. When using this method each column is named using Fn, where if you had three columns your resulting DataTable columns would be F1,F2,F3 and can be aliased as shown in the select statement. The variable RootFolder must end with a directory separator i.e. "\". So if the user selected the file via an OpenDialog we would use the code in figure 1. Note there are two parameters in the SELECT statement which are hard coded to keep things simple.

Figure 1

Dim RootFolder As String = IO.Path.GetDirectoryName(OpenFileDialog1.FileName) & IO.Path.DirectorySeparatorChar
Dim FileName As String = IO.Path.GetFileName(OpenFileDialog1.FileName)

Figure 2
Note If the file has column names in the first row then change HDR=No to HDR=Yes in the connection string.

Dim dt As New DataTable With {.TableName = "Mytable"}

Dim RootFolder As String = "C:\FolderNameContainingYourFile\"
Dim FileName As String = "YourFilenameGoesHere"
Dim Builder As New OleDbConnectionStringBuilder With
{
    .Provider = "Microsoft.Jet.OLEDB.4.0",
    .DataSource = RootFolder
}

Builder.Add("Extended Properties", "text;HDR=No;FMT=Delimited")

' Hard coded for demo
Dim FindDate As Date = #4/1/2013#

Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
    Using cmd As New OleDbCommand With
        {
            .Connection = cn,
            .CommandText =
            <SQL>
                SELECT 
                    F1 As ID, 
                    F2 As CustomerName, 
                    F4 As PurchaseOrder 
                FROM 
                    <%= FileName %>
                WHERE 
                    F2=@CustomerName 
                    AND 
                    F3=@FindDate
            </SQL>.Value
        }
        cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@CustomerName", .OleDbType = OleDb.OleDbType.VarWChar})
        cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@FindDate", .OleDbType = OleDb.OleDbType.Date})
        cmd.Parameters(0).Value = "Customer1" ' --- Hard coded for demo
        cmd.Parameters(1).Value = FindDate

        cn.Open()

        dt.Load(cmd.ExecuteReader)
        For Each row As DataRow In dt.Rows
            Console.WriteLine("[{0}] [{1}]", row.Field(Of Int32)("ID"), row.Field(Of String)("PurchaseOrder"))
        Next
    End Using
End Using

Another method is to use TextFieldParser class.

Dim dt As New DataTable With {.TableName = "Mytable"}
dt.Columns.AddRange(New DataColumn() _
    {
        New DataColumn("ID", GetType(Int32)),
        New DataColumn("CustomerName", GetType(String)),
        New DataColumn("Ordered", GetType(Date)),
        New DataColumn("PurchaseOrder", GetType(String))
    }
)

Dim FileName As String = "C:\FolderNameContainingYourFile\YourFileNameGoesHere"
Dim FindDate As Date = #4/1/2013#
Using Reader As New Microsoft.VisualBasic.FileIO.TextFieldParser(FileName)
    Reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
    Reader.Delimiters = New String() {","}
    Dim Line As String()
    While Not Reader.EndOfData
        Try
            Line = Reader.ReadFields()
            dt.Rows.Add(New Object() {CInt(Line(0)), Line(1), CDate(Line(2)), Line(3)})
        Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
            Console.WriteLine(ex.Message)
        End Try
    End While
End Using
Dim dv As DataView = dt.DefaultView
dv.RowFilter = "CustomerName='Customer1' and Ordered='" & FindDate & "'"
For Each DvRow As DataRowView In dv
    Console.WriteLine(String.Join(",", DvRow.Row.ItemArray))
Next

File used for above

1,Customer1,4/1/2013
2,Customer2,4/1/2013
3,Cusomter3,4/2/2013

[vbfbryce]

Thanks for taking the time to put that together, I'll check it out!

[kareninstructor]

Seems in my hast I provided the wrong file contents, should had been

1,Customer1,4/1/2013,OEF12
2,Customer2,4/1/2013,OEF11
3,Cusomter3,4/2/2013,AAF23
4,Customer1,4/1/2013,B12BX

[Shaggy Hiker]

Seems in my hast I provided the wrong file contents, should had been

Haste makes wast

[kareninstructor]

Haste makes wast

Hey you are using the same spellllkecker as me

[vbfbryce]

Ok, guys, one last question on this for me...

I am going to import the .csv daily files into Access and append them into one (master) table. I will then want to query that master table, providing the order number as criteria, and return the records associated with that order number for however many days they appear.

I get a bit stuck when I try to call an Access query with a parameter. Thoughts on the best way to do that?

Thanks again!

vBryce

[Shaggy Hiker]

Show us the SQL you are using currently. That's a fairly simple thing to do, but it can have some interesting little variations that can trip you up. One of the key questions is how you are getting the order number. If it is something that the user will enter, then using a parameterized query is definitely the right thing to do, but Access handles parameterized queries in a kind of awkward fashion. You are probably pretty close, though.