|
I'm a .NET Newbie using VB.Net 2005 pro. I have need for a CSV Reader and thought if I could get this working with VB.Net it would be great. I've tried using the code below, but it doesn't find references or execute.
Imports System.IO
Public Class Form1
Private Sub GetCSVFile()
Dim Idx As Integer
Dim csv As csvreader
Dim FieldCount As Integer
CsvReader(csv = New CsvReader(New StreamReader("data.csv"), False))
FieldCount = csv.FieldCount
While (csv.ReadNextRecord())
For Idx = 0 To FieldCount - 1
If Idx = 0 Then
Debug.Print("--------------------")
End If
debug.Print(csv(idx).tostring))
Next
End While
End Sub
How do I get a reference to LumenWorks.Framework.IO.dll? Is there a document other than the source code that defines the methods, parameters, etc. for each CSV related statement (such as the 'False' in the 'New CSVReader' statement above)?
Does anybody have this working with VB?
Thanks,
Bob
|
|
|
|
|
You will find the dll in the folder "[...]\LumenWorks.Framework.IO\bin\Release". You should copy "LumenWorks.Framework.IO.dll" and "LumenWorks.Framework.IO.xml" to the location where you keep the libraries you use. Then you can add a reference to this dll in your project. It is important to also copy the xml file because it is what will give you intellisense support.
Your code should be the following:
Imports System
Imports System.Diagnostics
Imports System.IO
Imports LumenWorks.Framework.IO.Csv
Class Test
Private Sub ReadCSVFile()
Dim csv As New CsvReader(New StreamReader("data.csv"), False)
Try
Dim fieldCount As Integer = csv.FieldCount
Do While (csv.ReadNextRecord())
For i As Integer = 0 To fieldCount - 1
If i = 0 Then
Debug.WriteLine("--------------------")
End If
Debug.WriteLine(csv.Item(i))
Next
Loop
Finally
csv.Dispose()
End Try
End Sub
End Class
It is a good practice to declare variables as they are required because you will limit their scope, the lifetime of the object they point to and it will also be easier to look at them since they are closer to where they are actually used.
VB.NET support declaration and initialization on the same line.
Do While ... Loop is the more common way of writing while loops.
Declared this way, the loop variable i will be accessible only during the loop.
.ToString() is not necessary as the return type of csv.Item(i) is already a string.
There is no using keyword in VB.NET, so you need to replace it with a Try/Finally clause as you see above.
Sébastien
Intelligence shared is intelligence squared.
Homepage : http://sebastienlorion.com
|
|
|
|
|
Thanks Sebastien for the fast response and the tip on "Do While...". The code works perfectly on a csv file that contains only one record type with the same number of fields in each record.
As in:
rec1, fld2, fld3
rec2, fld2, fld3
rec3, fld2, fld3
Unfortunately, the csv files I have to deal with have several 'record types' that contain different number and types of fields. The first field is the record type.
As in:
H, fld2, fld3, fld4
C, fld2, fld3, fld4, fld5,.... fld15
C, fld2, fld3, fld4, fld5,.... fld15
T, fld2, fld3, fld4, fld5
It appears that CSV Reader reads the first line of data and determines how many fields are in each record and processes that number for the entire file. That makes sense when using headers and only one record type within the file.
Is there any workaround using CSV Reader to process the kind of files I'm receiving?
Thanks,
Bob
-- modified at 3:15 Wednesday 19th July, 2006
|
|
|
|
|
Currently, the CsvReader does not support dynamic field counts. Adding that functionality would take a non negligeable amount of time which I prefer to use in other projects. Besides, this kind of CSV should be considered malformed anyway. Null fields should still be present and separated by a comma:
H,fld2,fld3,fld4,,,,,[...],
C,fld2,fld3,fld4,fld5,[...],fld15
C,fld2,fld3,fld4,fld5,[...],fld15
T,fld2,fld3,fld4,fld5,,,,,[...],
Sébastien
Intelligence shared is intelligence squared.
Homepage : http://sebastienlorion.com
|
|
|
|
|
Thanks again. Guess I'll just have to skin this cat another way!
Bob
|
|
|
|
|
Just a small note. There is now a keyword "using" in VB .Net 2005, so I highly recommend using it if you're coding in VB as it's a great feature. The syntax is as follows...
Using reader As CsvReader = New CsvReader(...)
...
End Using
Bruce Dunwiddie
|
|
|
|
|
It does not mention which open source license is used with this software.
See http://www.opensource.org/licenses/[^] for more info.
In my case, I want to use it in a commercial product, which is, unfortunately, distributed without source code. So, all your copyright notices, mostly placed at the beginning of each code file, will not be visible to our clients. Are you okay with that? If you prefer that I do something in the product to give you the credit, let me know.
Thanks for the effort. I like your project.
|
|
|
|
|
Humm, I guess your software will have a licence or a readme file ... So I would like that you include a copy of the license at the end of your own. In fact, this is the only thing required by the MIT license which I use.
http://www.opensource.org/licenses/mit-license.php
Out of curiosity, may I know what is your product ?
Thanks for your kind comments!
Sébastien
Intelligence shared is intelligence squared.
Homepage : http://sebastienlorion.com
|
|
|
|
|
Just to be as clear as I can, here is what I have in mind:
Your software licence
...
The Software includes a third-party component "LumenWorks.Framework.IO.CSV.CsvReader" licensed under the following terms:
LumenWorks.Framework.IO.CSV.CsvReader
Copyright (c) 2005 Sébastien Lorion
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies
of the Software, and to permit persons to whom the Software is furnished to do so,
subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE
FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
Sébastien
Intelligence shared is intelligence squared.
Homepage : http://sebastienlorion.com
|
|
|
|
|
I have spoken to our legal department about modification of our license to include yours in it. However, this would have a great impact on many aspects of our business. Besides, this component is not even 1% of the product we are selling, so it will be very hard for me to sell your proposal internally. In other words, we need an alternative solution, such as inclusion of the notice at a different location.
Personally, I really like the way you have designed this component, as you have taken the time to implement interfaces to facilitate integration and benchmark the performance to show its competitive edge over other available solutions in the industry. So, I certainly want to give another try.
Thanks.
|
|
|
|
|
Humm, what can I say about that ... I chose the MIT license specifically to avoid such issues. The license is very short and clear, with no weird clauses. The only string attached to Open Source software is the mention of its authors and a company using Open Source component should be aware of that *before* using it. I have seen many commercial applications using OSS and they will all have addendum to their own license.
That said, a compromise would be to include the license in another file, but located in the same folder as yours. You could name it "CsvReader license.txt" or something similar.
Good luck!
Sébastien
Intelligence shared is intelligence squared.
Homepage : http://sebastienlorion.com
|
|
|
|
|
Well, we have not included your component in any release of our product *yet*. I will certainly try to convince the decision maker to accept the alternative solution. If not, I will have to create one on my own or find another component that does a good job. All in all, thanks for your effort in explanation and clarification. I truly appreciate it.
I will get back to you when the decision is made.
|
|
|
|
|
Okay, we will put it in the license agreement that goes with the installer, as part of addendum. Thanks.
|
|
|
|
|
Wow, you have great diplomatic skills If you can tell me what is your product, I would be curious to check it out.
Thanks!
Sébastien
Intelligence shared is intelligence squared.
Homepage : http://sebastienlorion.com
|
|
|
|
|
You can check out our web site at http://explorance.com/[^]. However, the demo is not available to the general public.
|
|
|
|
|
Hello, this script contain bug.
This is one record by http://tools.ietf.org/html/4180 but your reader it read as two records.
1996,Jeep,Grand Cherokee,"MUST SELL!
air, moon roof, loaded",4799.00
Sorry for my poor english...
|
|
|
|
|
I just tested your code and I got 1 record with a field count of 5. This example is really basic and is already covered *many* times in the unit tests.
Please provide me the source code if this is really not a joke...
using (LumenWorks.Framework.IO.Csv.CsvReader csv = new LumenWorks.Framework.IO.Csv.CsvReader(new System.IO.StringReader(@"1996,Jeep,Grand Cherokee,""MUST SELL!
air, moon roof, loaded"",4799.00"), false))
{
int count = csv.FieldCount;
while (csv.ReadNextRecord())
{
for (int i = 0; i < count; i++)
Console.Write(csv[i]);
Console.WriteLine();
}
}
Sébastien
Intelligence shared is intelligence squared.
Homepage : http://sebastienlorion.com
-- modified at 22:21 Wednesday 12th July, 2006
|
|
|
|
|
Oh my mistake sorry. I thought that script automatic replace \n for " ". I am very sorry...
I am beginner...
BTW: My code isn't 1996,Jeep,Grand Cherokee,"MUST SELL! air, moon roof, loaded",4799.00
but is
1996,Jeep,Grand Cherokee,"MUST SELL! // here is \n
air, moon roof, loaded",4799.00
Anyway how replace automatic \n for " " or ""?
|
|
|
|
|
The parser will not do that for you. However, you can do as follow...
using (LumenWorks.Framework.IO.Csv.CsvReader csv = new LumenWorks.Framework.IO.Csv.CsvReader(new System.IO.StringReader("1996,Jeep,Grand Cherokee,\"MUST SELL!\nair, moon roof, loaded\",4799.00"), false))
{
int count = csv.FieldCount;
while (csv.ReadNextRecord())
{
for (int i = 0; i < count; i++)
Console.Write(csv[i].Replace("\n", " ");
Console.WriteLine();
}
}
Sébastien
Intelligence shared is intelligence squared.
Homepage : http://sebastienlorion.com
|
|
|
|
|
Hello,
first of all i like to thank you. The Csv-Reader is very comfortable.
The only thing i want you to ask is that if it is possible, to read the fieldcount again.
i. e. I have a csv-file with the first line only consisting of a white-space. so the fieldcount says 1. Next line i have all data i need, but i cant read them because i compare the fieldcount with greater or equal than one.
|
|
|
|
|
Humm, one thing you could do is call ReadLine() on your stream reader before passing it to the CsvReader. Would that solve your current problem ?
Thanks for your comment!
Sébastien
Intelligence shared is intelligence squared.
Homepage : http://sebastienlorion.com
|
|
|
|
|
Thanks for the answer. I will try it out and at the moment i think this will work. Unfortunately not the best way to solve this problem but at least one way.
|
|
|
|
|
Another way would be to mark the first line as a comment by putting '#' at its beginning.
The option of having a dynamic field count would be great, but unfortunately, I currently have no time to code it myself.
Thanks for your comment!
Sébastien
Intelligence shared is intelligence squared.
Homepage : http://sebastienlorion.com
|
|
|
|
|
The following exception occurred in the DataGridView:
System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Paratmeter name: index
at System.ThrowHelper.ThrowArgumentOutofRangeException(...)
at Sstem.ThrowHelper.ThrowArgumentOutofRangeException()
at LumenWorks.Framework.IO.Csv.CachedCsvReader.CsvBinding list.get_Item(Int32 index)
at LumenWorks.Framework.IO.Csv.CachedCsvReader.CsvBinding list.System.Collections.IList.get_Item(Int32 index)
at System.Windows.Forms.CurrencyManager.get_Item(Int32 index)
at System.Windows.Forms.DataGridview.DataGridViewDataConnection.GetError(Int32 rowIndex)
To replace this default dialog please handle the DataError event
My Form1 has just a Button1 and a DataGridView1 and code lists:
Imports System.IO
Imports LumenWorks.Framework.IO.Csv
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim csv As New CachedCsvReader(New StreamReader("H:/DB60630.DAT"), False)
Me.DataGridView1.DataSource = csv
End Sub
End Class
It's able to get the data in the DataGridView1 when execute but when I scroll to the end it gives the above exception message. I'm a beginner in .NET, sorry if that is an obvious mistake.
My DB60630.DAT looks like:
40,2006,180,1030,17.26,53.65,35.49,1.333,0,0,0,.018,17.26,53.65,35.49,1.296,17.26,53.65,35.49,1.351,3.392,152,5.857,1183,-449.3,-192.3,174.3,81.6,32.66,1052,-841,-336.1,2037,-350.6,-143.8,17.73,.01,17.68,17.75,13.15
40,2006,180,1045,17.27,53.65,35.49,1.372,0,.005,0,.01,17.27,53.64,35.49,1.357,17.27,53.65,35.49,1.388,3.288,155.9,5.354,1122,-415.8,-174.1,163.4,71.8,31.55,947,-689.7,-307.4,1888,-328.8,-127.9,17.73,.009,17.71,17.77,13.23
40,2006,180,1100,17.29,53.64,35.49,1.399,0,.005,0,.007,17.29,53.64,35.49,1.39,17.29,53.65,35.49,1.409,2.575,152.5,5.996,1071,-388.2,-162.7,146.4,58.38,27.98,811,-643.3,-263.3,1710,-275.8,-96.2,17.73,.008,17.71,17.74,13.57
40,2006,180,1115,17.31,53.64,35.49,1.423,0,.005,0,.006,17.31,53.64,35.49,1.413,17.31,53.65,35.49,1.433,6.52,147.3,4.784,741,-257.6,-110,17.8,13.6,7.35,710,-297.2,-127.4,802,-232.6,-92.2,17.73,.007,17.71,17.74,13.47
40,2006,180,1130,17.33,53.65,35.49,1.419,0,.003,0,.012,17.33,53.64,35.49,1.401,17.33,53.65,35.49,1.433,6.779,151.5,4.196,654.6,-224.9,-98.1,26.28,17.04,8.01,614.5,-267.8,-118,708,-188,-80.8,17.72,.01,17.66,17.74,13.39
40,2006,180,1145,17.33,53.65,35.49,1.414,0,0,0,.011,17.33,53.65,35.49,1.401,17.33,53.65,35.49,1.43,5.492,156.3,5.084,718,-253.9,-112.9,53.88,32.81,13.28,674.8,-335.9,-151.7,879,-101.6,-95.7,17.72,.008,17.7,17.74,13.32
40,2006,180,1200,17.34,53.66,35.5,1.392,0,.005,.004,.009,17.34,53.65,35.49,1.38,17.34,53.66,35.5,1.412,5.45,158.1,4.495,917,-337.8,-150.1,213.4,90.3,40.53,700,-561.4,-242.9,1410,-236.1,-104.1,17.72,.009,17.69,17.74,13.26
40,2006,180,1215,17.37,53.67,35.5,1.36,.005,0,.003,.015,17.36,53.67,35.5,1.338,17.37,53.67,35.51,1.385,6.27,147.1,8.14,1580,-629.7,-283.4,440,190.8,85.8,1037,-1343,-540.9,2609,-389.4,-175.5,17.72,.009,17.7,17.77,13.21
40,2006,180,1230,17.35,53.7,35.53,1.336,0,.003,0,.009,17.35,53.69,35.53,1.322,17.35,53.7,35.53,1.353,6.439,153.1,4.061,957,-369.6,-164.4,455.9,188.6,82.3,689.5,-1122,-522.5,2638,-236.1,-106.1,17.71,.013,17.68,17.74,13.16
40,2006,180,1245,17.34,53.69,35.52,1.295,0,.004,0,.009,17.34,53.68,35.52,1.284,17.34,53.69,35.52,1.312,6.029,150.8,4.292,721,-286.4,-129.7,63.49,31.41,12.91,648,-445.1,-187.9,1041,-238.4,-108.1,17.71,.019,17.64,17.75,13.19
40,2006,180,1300,17.39,53.68,35.51,1.253,.005,0,0,.013,17.38,53.68,35.51,1.235,17.39,53.68,35.51,1.273,5.659,1
Thanks for any help!
Jason
|
|
|
|
|
Humm, unless there was a problem with copy/paste, your last record is missing about half of the fields ...
With this input data, the latest build will correctly throw a MissingFieldCsvException unless you specify another action to be taken.
I will post an update to the article no later than this week.
Sébastien
Intelligence shared is intelligence squared.
Homepage : http://sebastienlorion.com
|
|
|
|
|