July 9, 2008 06:18am pdt

1. TheLearn... 602,345
2. jpaulino 584,489
3. Sancler 365,108
4. Idle_Mind 348,727
5. PaulHews 344,857
6. Dhaest 276,399
7. VBRocks 223,470
8. jcoehoorn 219,842
9. Fernando... 217,085
10. jaime_ol... 214,751
11. webtubbs 204,842
12. emoreau 200,998
13. vb_jonas 183,591
14. angelIII 168,540
15. GreenGhost 166,611

1. TheLearn... 4,056,828
2. Idle_Mind 3,425,879
3. Sancler 2,346,588
4. Fernando... 1,967,535
5. RonaldBi... 1,159,695
6. emoreau 1,016,321
7. jpaulino 933,856
8. iboutchkine 804,379
9. VBRocks 778,651
10. Chaosian 655,498
11. angelIII 635,247
12. PaulHews 631,898
13. webtubbs 627,263
14. arif_eqbal 596,164
15. planocz 594,245

01.17.2008 at 12:44PM PST, ID: 23091521

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.9

Read CSV into an Array, then Display Contents

Zone: Microsoft Visual Basic.Net

Tags: Microsoft, Visual Basic Studio, 2005, version 8.0 experss edition

Hello, I have worked with VB just a little bit and I need some much needed help.
I need to write a VB program that reads a Csv with contents such as:
127275,1.00,1,"133"
The number of rows is unknown, but there will always be 4 columns. Doing research on this topic I've read it is easiest to read the csv, split it than put it into a 2d array. I cannot find a solution of this for vb 2005, but reading the solutions of other programs i get the gist of it.

Any help is apperciated

Start your free trial to view this solution

Question Stats

Zone: Programming

Question Asked By: MCSECLP

Solution Provided By: GeneM

Participating Experts: 4

Solution Grade: B

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

01.17.2008 at 12:59PM PST, ID: 20685339

Rank: Master

MrClyfar:

Hi there.

I have a book called Visual Basic 2005: The Language, and in the book there is a section devoted on how to read CSV files using a new class in .NET 2.0. Typically, I've left the book at work!!!! GRRRR.

I can't seem to find this class, or the method, on Google, but I know that it does exist and will make reading CSV files a lot easier for you. Granted, there are many code examples availalbe, but this new class makes it easier.

I will post the code from the book tomorrow, but maybe someone will have posted example code before then. But I will still come back to this post with the code I was lookign for.

Jas.

01.17.2008 at 01:08PM PST, ID: 20685430

the_b1ackfox:

first create an arraylist ... and then create a data structure to hold your info. when you read the csv file, you will be using string.readline(), you will then us the string.split command feeding it the character of ','. into an array of strings, feed this into your DS and feed the DS into the array... if you want to pull out a specific row of data, you can use the foreach loop to locate the row and then use your DS to extract the data

01.17.2008 at 01:09PM PST, ID: 20685435

jbotello4:

Try this:

Dim objInput As System.IO.StreamReader
Dim LineFeed() As String

objInput = System.IO.File.OpenText("C:\MyFile.csv")

LineFeed = Split(objInput.ReadLine, ",")
Textbox1.text = LineFeed(0)
Textbox2.text = LineFeed(1)
Textbox3.text = LineFeed(2)
Textbox4.text = LineFeed(3)

objInput.Close

01.17.2008 at 01:11PM PST, ID: 20685460

the_b1ackfox:

Method #2

Check out http://www.thescripts.com/forum/thread467891.html

01.17.2008 at 06:40PM PST, ID: 20687490

GeneM:

There are a couple of problems that you can encounter when reading a .CSV file. The first problem occurs when you have a comma embedded within a single field. To avoid that problem, the field is enclosed within double quotes. The second problem is if you have a double quote enclosed with a field. That problem is addressed by changing the double quote to 2 double quotes. Thus, your line might look something like this:
12345,"333,444","Sam ""Sam"" Jones","Hi"

If your data does not have either of these conditions, you can use the Split function to parse it. Otherwise, try something like the attached code.

                       
         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

           
           
             Dim reader As Microsoft.VisualBasic.FileIO.TextFieldParser
reader = My.Computer.FileSystem.OpenTextFieldParser _
        	("C:\Temp\test.csv")
reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
reader.Delimiters = New String() {","}
Dim currentRow As String()
While Not reader.EndOfData
    Try
       currentRow = reader.ReadFields()
       Dim currentField As String
       For Each currentField In currentRow
           MsgBox(currentField)
       Next
    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
       MsgBox("Line " & ex.Message & _
                "is not valid and will be skipped.")
     End Try
End While

           
         

Open in New Window

01.18.2008 at 12:53AM PST, ID: 20688823

Rank: Master

MrClyfar:

Hi there.

Kudos to GeneM, that was the code I was looking for. The TextFieldParser class was what I was looking for.

Nice one.

Jas.

01.18.2008 at 09:22AM PST, ID: 20692186

MCSECLP:

that worked great!, it parsed everything just fine.
now for the last bit

I can get the whole file to display, but not row by row, it is all going down in one coloumn:
a
a
a

b
b
b
etc.

how can i fix this?

thanks GeneM

01.18.2008 at 01:41PM PST, ID: 20694451

GeneM:

Can you tell us how you are displaying the data? Are you trying to display it in fixed width columns?

Maybe you could post your code so we could see how you are displaying it.

GeneM

01.21.2008 at 09:19AM PST, ID: 20707678

MCSECLP:

yes i would like to display it in a fixed width columns.
5 columns

01.21.2008 at 09:47AM PST, ID: 20707908

MCSECLP:

i have currently what you have given me.

                       
         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

           
           
               Dim reader As Microsoft.VisualBasic.FileIO.TextFieldParser
        reader = My.Computer.FileSystem.OpenTextFieldParser _
                        ("AAA.csv")
        reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
        reader.Delimiters = New String() {","}
        Dim currentRow As String()
        While Not reader.EndOfData
            Try
                currentRow = reader.ReadFields()
                Dim currentField As String
                For Each currentField In currentRow
                    ListBox1.Items.Add(currentField)
                Next
            Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                ListBox1.Items.Add("Line " & ex.Message & _
                         "is not valid and will be skipped.")
            End Try
        End While

           
         

Open in New Window

01.21.2008 at 10:03AM PST, ID: 20708012

MCSECLP:

I was just told that
i will need to use a search function to remove or copy a row of data ....now this changed things bah.

01.21.2008 at 06:40PM PST, ID: 20711540

GeneM:

I feel like you have added quite a bit of function to this. Rathar than just write the code for you, I would prefer to just give you some ideas to get you started. There are probably a lot of ways to do what you want. I will tell you how I would go about doing it. I am definitely not an expert, so there may be easier ways.

You are using a list box to display the data. I think this is a good choice. In order to get columns of data in the list box, you will need to build a string with all 4 columns in it. Since you are displaying numbers, you will probably want the variables right aligned in the columns. You will also want a set number of decimal places so the decimal points align. You will then have to pad the variables with leading spaces so that the data looks like it is in columns when it is displayed. Be sure the font in the listbox is a fixed width (courier new).

If you want the data sorted in the list box on column 1 then you can set the sorted property in the list box. If you want it sorted on one of the other columns, you must sort it before you add the strings. That is another issue.

A list box gives you a lot of flexibility for searching and deleting data (in your case, rows of data). If you don't understand how to do this, you can google "Listbox vb 2005" and get lots of information on listboxes. I just looked at the Help file for listboxes, and it looks fairly easy to understand.

As far as copying the data, you just have to remember that you are copying a single string which contains your columns. I don't know if that is acceptable. You might have to provide code to do the copy that breaks the columns back up into separate fields.

I hope this helps

Gene

01.24.2008 at 11:01AM PST, ID: 20736110

MCSECLP:

GeneM,

Thanks for your help, it really gave me a better way to start the design. For sorting the data, i was thinking reading the csv into an array, my big problem is how do i go about doing this, i know it has to be a 2D array.
if i am correct, i should be able to display the array in a list box then it should be all in the right colomns...am i correct?

01.29.2008 at 06:57PM PST, ID: 20774288

GeneM:

Hi Mcseclp
Sorry I kind of left you hanging here. I got busy on other things and haven't had a chance to follow up on your problem.

First, I don't know what will happen if you try to load an array into a listbox. I would probably build a string containing my 4 columns and add each row into the list box that way. See my explanation of this above.

Now for the sort question. You may not have to do anything other than set the sort property on the list box. That would assume you wanted your columns sorted with the highest order column on the left, etc. I think I also discussed that above.

If you want to sort with the highest level of sort a column other than the first, you need to do some more work. If I was doing it, I would probably build an arraylist of structures, with each structure containing 4 variables (the 4 columns). I would then implement icomparable to do the sort. icomparable is a little tough if you have never used it, so I have attached a sample icomparable. Study it. If you still have a question then get back to me.

                       
         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

           
           
             'First, define a Structure that needs To be sorted And Implements IComparable.  
'In this Case, a Structure Is considered greater than another based On the ID 
'member:
 
'==========Begin Code==========
Public Structure TestStructure
    Implements IComparable
 
    Public ID As Integer
    Public name As String
 
    Public Function CompareTo(ByVal obj As Object) As Integer _
                        Implements System.IComparable.CompareTo
        Dim rhs As TestStructure = CType(obj, TestStructure)
 
        If Me.ID > rhs.ID Then
            'This object's ID is greater than the compared object's ID, so return 1
            Return 1
        ElseIf Me.ID = rhs.ID Then
            'This object's ID is equal to the compared object's ID, so return 0
            Return 0
        Else
            'This object's ID must be less than the compared object's ID, so return -1
            Return -1
        End If
    End Function
End Structure
'==========End Code==========
           
         

Open in New Window

Accepted Solution

01.31.2008 at 09:37AM PST, ID: 20788909

MCSECLP:

GeneM:

What you have told me, put a lot into perspective and I worked the program around a little bit.

here is what i have, it displays the data exactly how i want it:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
items = Split(infile.ReadLine, ",")
Dim fmtstr As String = "{0,-20}{1,-20}{2,-20}{3,-20}{4,-20}"

Dim numScore As String = "---"
items = Split(infile.ReadLine, ",")

Do While infile.Peek <> -1
Select Case items(4)
Case "0"
numScore = "NO"
Case "1"
numScore = "YES"
End Select
lstResults.Items.Add(String.Format(fmtstr, items(0), items(1), items(2), items(3), numScore))
items = Split(infile.ReadLine, ",")
End If

Loop
End Sub
End Class

The question:
Now to make the program easier, Ive added a 5th column in the CSV, it will be either a 0 or 1

I want to add a "complete" check box, It would look at the 5th column, if the digit was "1" the box would be checked and "0" unchecked

Is it possible to write code, so if i checked the box, it would change the "0" to a "1" ? (visa versa)

thanks

02.04.2008 at 05:19PM PST, ID: 20819877

GeneM:

I don't think there is any way to put a check box in a listbox, Thus, it doesn't seem like you could have a check box for each list box item. However, if you were only wanting to show the check box for the highlighed line, that would be easy to do. Just add some code in the click event for the list box to set (unset) the checked property of the check box.

20080236-EE-VQP-29 / EE_QW_2_20070628