|
|
#1 |
|
New Member
Join Date: Jun 08
Posts: 3
![]() |
[2008] CSV file into Dataset...
...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. |
|
|
|
|
|
#2 |
|
New Member
Join Date: Jun 06
Posts: 13
![]() |
Re: [2008] CSV file into Dataset...
I usually use this snippet to get text file data into a dataset.
Code:
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
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. |
|
|
|
|
|
#3 |
|
.NUT
Join Date: May 05
Location: Sydney, Australia
Posts: 54,913
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
Re: [2008] CSV file into Dataset...
The easiest way is to use ADO.NET. It will take you four lines of code:
vb.net Code:
An alternative would be to use a FileIO.TextFieldParser, which is also very simple.
__________________
![]() 2007, 2008, 2009, 2010 Why is my data not saved to my database? | Communicating between multiple forms | MSDN Data Walkthroughs MSDN "How Do I?" Videos: VB | C# VBForums Database Development FAQ My CodeBank Submissions: VB | C# (ForumAccount has translated some of my VB submissions to C#) My Blog: Defining and Raising Custom Events | Manipulating GDI+ Drawings | Using Parameters in ADO.NET |
|
|
|
|
|
#4 |
|
New Member
Join Date: Jun 08
Posts: 3
![]() |
Re: [2008] CSV file into Dataset...
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. |
|
|
|
|
|
#5 |
|
PowerPoster
Join Date: Jul 06
Location: Providence, RI - USA
Posts: 6,848
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
Re: [2008] CSV file into Dataset...
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
Code:
Dim connection As New System.Data.OleDb.OleDbConnection("connection string here")
__________________
Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it. - Abraham Lincoln - Last edited by stanav; Nov 5th, 2008 at 11:08 AM. |
|
|
|
|
|
#6 |
|
New Member
Join Date: Jun 06
Posts: 13
![]() |
Re: [2008] CSV file into Dataset...
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 vb Code:
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 |
|
|
|
|
|
#7 |
|
Fanatic Member
Join Date: Dec 05
Posts: 923
![]() |
Re: [2008] CSV file into Dataset...
its in C# but it should be pretty easy to convert
i use this alot for reading csv files at work c# Code:
__________________
Noli turbare circulos meos! |
|
|
|
![]() |
|
||||||
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|