Advertisement

03.29.2004 at 07:47AM PST, ID: 20936016
[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!

Read CSV file to array
Zone: .NET
Tags: csv, file, read
I know how to read a file.
But how to split the content? Beside being separated by comma, the actual data also contains comma. What should I do?
I need to store the contents into a multi-d array too.
Start your free trial to view this solution
Question Stats
Zone: Programming
Question Asked By: yan_er04
Solution Provided By: farsight
Participating Experts: 6
Solution Grade: A
Views: 828
Translate:
Loading Advertisement...
03.29.2004 at 08:29AM PST, ID: 10705438

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
03.29.2004 at 08:50AM PST, ID: 10705632

Rank: Master

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
03.29.2004 at 12:08PM PST, ID: 10707418

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
03.29.2004 at 08:26PM PST, ID: 10710508

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
03.29.2004 at 09:01PM PST, ID: 10710669

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
03.29.2004 at 10:08PM PST, ID: 10711016

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
03.29.2004 at 10:14PM PST, ID: 10711034

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
03.30.2004 at 04:58AM PST, ID: 10713019

Rank: Guru

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
07.21.2004 at 08:01AM PDT, ID: 11603448

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.26.2005 at 02:11PM PDT, ID: 13870822

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
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
OS
  • 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
 
03.29.2004 at 08:29AM PST, ID: 10705438

Rank: Guru

Dim file_name As String
Dim fnum As Integer
Dim whole_file As String
Dim lines As Variant
Dim num_rows As Long
Dim the_array() As Variant
Dim R As Long
Dim C As Long

    file_name = App.Path
    If Right$(file_name, 1) <> "\" Then file_name = _
        file_name & "\"
    file_name = file_name & "test.csv"

    ' Load the file.
    fnum = FreeFile
    Open file_name For Input As fnum
    whole_file = Input$(LOF(fnum), #fnum)
    Close fnum

    ' Break the file into lines.
    lines = Split(whole_file, vbCrLf)

    ' Dimension the array.
    num_rows = UBound(lines)
    ReDim the_array(num_rows)

    ' Copy the data into the array.
    For R = 0 To num_rows
        the_array(R) = Split(lines(R), ",")
    Next R

    ' Prove we have the data loaded.
    For R = 0 To UBound(the_array)
        For C = 0 To UBound(the_array(R))
            Debug.Print the_array(R)(C) & "/";
        Next C
        Debug.Print
    Next R
    Debug.Print "===END==="
Assisted Solution
 
03.29.2004 at 08:50AM PST, ID: 10705632

Rank: Master

The file is comma delimited and the data contains commas....

Comma delimitted is not a good option for you in that you will have to write a great deal of logic to determin true delimeters from data :(...

If possible could you delimit with different character...  Say a tilde ~ ...

That is the easiest answer and would simple use code similar to that provided by Mikal613 :)...

Good luck,
Dante
 
03.29.2004 at 12:08PM PST, ID: 10707418
// semi-pseudo-code to change your delimiter
bool inString = false;
for(int x=0;x<strFile.Length;x++)
  if(strFile[x]=='\"')  //Character that indicates that the comma is not a delimiter, normally double quote
    inString = !inString;
  else if(!inString && strFile[x]==',')
    strFile[x]='~'; //Some character that is not used in your file.

// Add code to split by new delimiter, in my example ~
 
03.29.2004 at 08:26PM PST, ID: 10710508

Rank: Guru

Give us a sample line that illustrates this.
> The file is comma delimited and the data contains commas....
Also illustrate any other special characters in the data.  What if it contains a single-quote?  A double-quote?   etc.

A simple regular expression will parse it easily (if it's parseable).

Also note that much of Mikal613's code for this example is VB6-style, instead of VB.NET style.
You may prefer to translate it to use the newer methods.
 
03.29.2004 at 09:01PM PST, ID: 10710669

Rank: Guru

(I haven't tried this one, but it sounds promising.)
Parsing Comma-Separated Values or CSV files into a DataTable using Regular Expressions
http://www.hotblue.com/article0000.aspx?a=0006

--- --- ---

from:  http://www.3leaf.com/resources/articles/regex.aspx#Examples  [in C#]

Get Values of a CSV (updated by Arnold Bailey)
,(?=(?:[^"]*"[^"]*")*(?![^"]*"))
In conjunction with this code:

Regex r = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
string s = "\"a\",b,\"c, d, e\",,f";
string[] sAry = r.Split(s);
for(int i=0;i < sAry.Length;i++)
{
   Console.WriteLine(sAry[i]);
}

--- --- ---

from:  http://radio.weblogs.com/0117167/2003/02/18.html#a132  [in C#]

 CSV Regex
I haven't found a Regex that will extract just the values from a comma separated value file, but thanks to Joe Gulisano, I do have one that will find all the "real" commas:

,(?=([^"]*"[^"]*")*(?![^"]*"))
So getting the values is just:

Regex r = new Regex(",(?=([^\"]*"[^"]*")*(?![^"]*"))");string s = "\"a",b,"c, d, e",,f";int start = 0;foreach (Match m in r.Matches(s)){  Console.WriteLine(s.Substring(start, m.Index - start));  start = m.Index + 1;}Console.WriteLine(s.Substring(start, s.Length - start));
Outputs:

"a"b"c, d, e"
f
 
03.29.2004 at 10:08PM PST, ID: 10711016
I can't change the format of the file.

A sample data will be
$100,"$2,350",a,15/04/1998

I am using VB.NET.

Is there no other way beside regular expression?

Well, I have tried regular expression before but... let's just say I am not an expert in it. =D

 
03.29.2004 at 10:14PM PST, ID: 10711034
I notice all the examples are using 1-d array.
How to change that to 2-d?
I seems to have problems adding data to a 2-d array.
 
03.30.2004 at 04:58AM PST, ID: 10713019

Rank: Guru

Here's some VB.NET based on one of those C# examples.
ParseCSV is a test driver.
ParseLine is the routine you'd actually want to use.

    Private Shared Sub ParseCSV()
        Dim oneLine As String = _
        """quoted"",nonquoted,""comma, """"inner-quoted-comma"""", chamelean"",,after-empty"

        Dim fieldValues As String() = ParseLine(oneLine)

        For i As Integer = 0 To fieldValues.Length - 1
            Console.WriteLine(i & "  [" & fieldValues(i) & "]")
        Next
    End Sub

    Private Shared Function ParseLine(ByVal oneLine As String) As String()
        ' Returns an array containing the values of the comma-separated fields.

        ' This pattern actually recognizes the correct commas.
        ' The Regex.Split() command later gets text between the commas.
        Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
        Dim r As System.Text.RegularExpressions.Regex = _
                New System.Text.RegularExpressions.Regex(pattern)

        Return r.Split(oneLine)
    End Function

Correct output:
0  ["quoted"]
1  [nonquoted]
2  ["comma, ""inner-quoted-comma"", chamelean"]
3  []
4  [after-empty]

---

Here's some simple multidimensional array code.
http://www.bhupeshs.com/vbMultiarray.htm

Here's another post about multidimensional arrays -- already answered.
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20744300.html

Here's some good stuff, especially near the bottom.
http://www.dotnet247.com/247reference/msgs/23/116904.aspx

Here's a sample program for two-dimensional arrays.
Near the bottom, there's a link to a very good PowerPoint presentation, too.  Chapter8.ppt
http://www.okc.cc.ok.us/aphilipp/WebCT/VB/09_VB_CourseContent.htm

Accepted Solution
 
07.21.2004 at 08:01AM PDT, ID: 11603448
Odbc supports reading CSV:

string ConnectionString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\";
OdbcConnection connection = new OdbcConnection(ConnectionString);
connection.Open();
OdbcCommand command = new OdbcCommand("Select * FROM Subscribers.csv", connection);
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
      // loop through every field
      for (int x = 0; x < reader.FieldCount; x++)
      {
                // add code for puting fields into array....
            Console.WriteLine(reader.GetDataTypeName(x) + " - " + reader.GetValue(x));
      }
}

                  // Close the connection
                  connection.Close();
 
04.26.2005 at 02:11PM PDT, ID: 13870822
         This code takes a comma delimited xml flie and puts it into a table. The regex is slightly more efficient I think..

            Dim regEx As Regex
            Dim cMatch As Text.RegularExpressions.MatchCollection
            Dim m As Text.RegularExpressions.Match

            cMatch = regEx.Matches(line, "(?<=(?:^|,\s*)"")(?:[^""]|"""")*|(?<=(?:^|,))(?:\s*)(?=(?:,|$))")

            For Each m In cMatch
               i += 1
               dc = New DataColumn
               dc.ColumnName = IIf(m.Value.Length = 0, colPrefix & i + 1, m.Value)
               dt.Columns.Add(dc)
            Next
 
 
20080236-EE-VQP-29