[2008] CSV file into Dataset...

[nebeno]

...Or at least I think thats what I want to do. I'm new to VB, and haven't done much programming in general.

I'm trying to read a CSV file of stock market data like below:

Date,Open,High,Low,Close,Volume
6/6/2008,26.50,27.08,26.03,26.44,37746600
6/5/2008,26.46,26.64,25.97,26.36,30152200
6/4/2008,26.25,27.05,26.16,26.85,29973600
6/3/2008,26.36,26.60,25.78,26.15,25586000
6/2/2008,26.80,26.81,26.03,26.40,26366800
5/30/2008,27.07,27.10,26.63,26.76,17754100
5/29/2008,27.34,27.36,27.00,27.07,17905300

I want to be able to store this data somehow so that I can then scan through it for patterns. I also want to be able to scan through dozens of files (for different stocks) and look for similar patterns. So it seems like I would want to put the data from each stock into datasets, and then I can scan through it from there.

But as I said, I'm new, and I'm not really sure this is even the best way to go about this. Simpler is better. But then I also want to expand this later, and be able to search through thousands of files repeatedly for different patterns, so will this method be fast enough to do that in a reasonable amount of time? I tried searching for tutorials and such, but they mostly seem to deal with transferring data from an SQL database or something. Right now I'm using another program to download the CSV files, though I suppose I'll eventually write the program to download the information and run the scans by itself.

I think that's about it. Thanks so much for any help you can provide.

[scott.brady]

I usually use this snippet to get text file data into a dataset.

 Public Function Convert(ByVal mFile As String, ByVal mTablename As String, ByVal delimiter As String) As DataSet
            ' The dataset to return
            Dim ds As New DataSet

            ' Open the file with a stream reader
            Dim sr As New StreamReader(mFile)

            ' Split the first line into the fields and add to string array called columns
            Dim columns As String() = sr.ReadLine().Split(delimiter.ToCharArray())

            ' Add the new Datatable to the DataSet
            ds.Tables.Add(mTablename)

            ' Cylcle the columns, adding those that do not exist yet and sequencing the ones that do
            For Each col As String In columns
                Dim added As Boolean = False
                Dim _next As String = ""
                Dim i As Integer = 0

                While Not added
                    ' Build the column name and remove any unwanted characters
                    Dim columnname As String = col + _next
                    columnname = columnname.Replace("#", "")
                    columnname = columnname.Replace("'", "")
                    columnname = columnname.Replace("&", "")
                    columnname = columnname.Replace("""", "")

                    ' See if the column already exists
                    If Not ds.Tables(mTablename).Columns.Contains(columnname) Then
                        ds.Tables(mTablename).Columns.Add(columnname)
                        added = True
                    Else
                        ' If it did exist then we increment the sequencer and try again
                        i = i + 1
                        _next = "_" + i.ToString()
                    End If
                End While
            Next


            ' Read the rest of the data in the file
            Dim allData As String = sr.ReadToEnd()

            ' Split off each row at the Carriage Return / Line Feed
            ' Default line ending in most windows exports
            ' You may have to edit this to match your particular file
            ' This will work for Excel, Access etc default exports.
            Dim rows As String() = allData.Split(vbCr.ToCharArray)

            ' Add each row to the Dataset
            For Each rowValue As String In rows

                ' Remove quotation field markers
                Dim row As String = rowValue.ToString().Replace("""", "")

                ' Split the row at the delimiter
                Dim items As String() = row.Split(delimiter.ToCharArray())


                ' Add the item to the dataset
                ds.Tables(mTablename).Rows.Add(items)

            Next

            ' Cleanup - Release StreamReader Resources
            sr.Close()
            sr.Dispose()

            ' Return the imported data
            Return ds

        End Function

Regards

Scott

P.S. This is not all my code though, I have modified it to suit my needs. I can't remember were I got it from though.

[jmcilhinney]

The easiest way is to use ADO.NET. It will take you four lines of code:

Dim connection As New OleDbConnection("connection string here")
Dim adapter As New OleDbDataAdapter("SQL query here", connection)
Dim table As New DataTable
 
adapter.Fill(table) 

See www.connectionstrings.com for the appropriate connection string and SQL syntax.

An alternative would be to use a FileIO.TextFieldParser, which is also very simple.

[nebeno]

ok, been trying to figure this out, but not having much luck.

scott.brady:
I tried using the function you have there, but it gives me errors saying

Argument not specified for parameter 'delimiter' of 'Public Function Convert...
Argument not specified for parameter 'mfile' of 'Public Function Convert...
Argument not specified for parameter 'mTablename' of 'Public Function Convert...

I'm not exactly sure how to use a function though. I just wanted to be able to open a file and run that function for it.


jmcilhinney:
When I put that code in, I get errors saying

Type 'OleDbConnection' is not defined.
Type 'OleDbDataAdapter' is not defined.

Is there something else I have do to? I'm just using Express Edition, does that make a difference? I don't really know anything about ADO.NET or SQL.


Sorry if these are really dumb questions. I appreciate the help.

[stanav]

You need to import System.Data.OleDb namespace. Or if you don't want to import the namespace then you have to use the fully qualified class names, i.e

Dim connection As New System.Data.OleDb.OleDbConnection("connection string here")

[scott.brady]

Here is a snippet of code that shows you how to use the function
Once the function is declared you can use it as in the following snippet

Dim ds As DataSet = Convert(Server.MapPath("test.txt"), "tblCustomers", ",") 

mFile is the path to the file you are trying to load into a dataset
mTableName is the name that you want to give the Datatable within the dataset
delimiter is what the fields in the text file are separated by. eg comma's or tabs etc.

Hope this is helpful

Regards

Scott

[Crash893]

its in C# but it should be pretty easy to convert

i use this alot for reading csv files at work

private DataTable ReadCSVFile(string path, string tablename, string adapter, string header)
        {
            try
            {
                System.Data.OleDb.OleDbConnection cnCSV = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + @";Extended Properties=""text;HDR=" + header + @";FMT=Delimited""");
                System.Data.OleDb.OleDbDataAdapter daCSV = new System.Data.OleDb.OleDbDataAdapter(adapter, cnCSV);
                DataTable dt = new DataTable(tablename);
                daCSV.Fill(dt);

                return dt;
            }

            catch (Exception ex)
            {

                MessageBox.Show("ERROR" + Environment.NewLine + ex.Message);
                Console.WriteLine(ex.ToString());
                return null;
            }
        }