Reading CSV file 
Author Message
 Reading CSV file

I want to use the Input() function to read items from a CSV text file.  The
problem is that each line of data contains anywhere from 17 to 20
comma-separated string items, so I can't just issue "n" Input() statements
per file row.  Is there an end-of-line type of function that will tell me if
I've just Input() the last item on a row?  Alternatively, is there a better
way to retrieve the data?

Thanks!

-----
Tim Thornton
College Station, Texas, USA



Sun, 30 Jan 2005 04:21:36 GMT  
 Reading CSV file
Use the ReadLine method. It reads an entire line from a text file associated
with a StreamReader object and returns a string. Then use Split method on
string to create an array of strings.You can then iterate the string array
to process the individual strings.

dim SR as New StreamReader("c:\My Documents\FileName.txt")
dim txtLine as String
dim Strings as String()
txtLine = SR.ReadLine()
Strings()=String.Split(",",txtLine)



Quote:
> I want to use the Input() function to read items from a CSV text file.
The
> problem is that each line of data contains anywhere from 17 to 20
> comma-separated string items, so I can't just issue "n" Input() statements
> per file row.  Is there an end-of-line type of function that will tell me
if
> I've just Input() the last item on a row?  Alternatively, is there a
better
> way to retrieve the data?

> Thanks!

> -----
> Tim Thornton
> College Station, Texas, USA



Sun, 30 Jan 2005 05:41:46 GMT  
 Reading CSV file
You could use the ReadLine function of the StreamReader
class and then use the Split function on the input line.  
Each field would then be contained in the array returned
from Split.  There could be problems if there are ever
commas embedded in a string field.

Keith

Quote:
>-----Original Message-----
>I want to use the Input() function to read items from a
CSV text file.  The
>problem is that each line of data contains anywhere from
17 to 20
>comma-separated string items, so I can't just issue "n"
Input() statements
>per file row.  Is there an end-of-line type of function

that will tell me if
Quote:
>I've just Input() the last item on a row?  Alternatively,
is there a better
>way to retrieve the data?

>Thanks!

>-----
>Tim Thornton
>College Station, Texas, USA

>.



Sun, 30 Jan 2005 05:49:07 GMT  
 Reading CSV file
Thanks to both you and Keith Matzen.  Keith pointed out that there might be
a problem if one or more strings has an embedded comma.  Is there any
workaround for this?

-----
Tim Thornton
College Station, Texas, USA


Quote:
> Use the ReadLine method. It reads an entire line from a text file
associated
> with a StreamReader object and returns a string. Then use Split method on
> string to create an array of strings.You can then iterate the string array
> to process the individual strings.

> dim SR as New StreamReader("c:\My Documents\FileName.txt")
> dim txtLine as String
> dim Strings as String()
> txtLine = SR.ReadLine()
> Strings()=String.Split(",",txtLine)



> > I want to use the Input() function to read items from a CSV text file.
> The
> > problem is that each line of data contains anywhere from 17 to 20
> > comma-separated string items, so I can't just issue "n" Input()
statements
> > per file row.  Is there an end-of-line type of function that will tell
me
> if
> > I've just Input() the last item on a row?  Alternatively, is there a
> better
> > way to retrieve the data?

> > Thanks!

> > -----
> > Tim Thornton
> > College Station, Texas, USA



Sun, 30 Jan 2005 06:02:46 GMT  
 Reading CSV file
Some applications will wrap the data in quote marks if the data has an
embedded comma. Can't think of another way.



Quote:
> Thanks to both you and Keith Matzen.  Keith pointed out that there might
be
> a problem if one or more strings has an embedded comma.  Is there any
> workaround for this?

> -----
> Tim Thornton
> College Station, Texas, USA



> > Use the ReadLine method. It reads an entire line from a text file
> associated
> > with a StreamReader object and returns a string. Then use Split method
on
> > string to create an array of strings.You can then iterate the string
array
> > to process the individual strings.

> > dim SR as New StreamReader("c:\My Documents\FileName.txt")
> > dim txtLine as String
> > dim Strings as String()
> > txtLine = SR.ReadLine()
> > Strings()=String.Split(",",txtLine)



> > > I want to use the Input() function to read items from a CSV text file.
> > The
> > > problem is that each line of data contains anywhere from 17 to 20
> > > comma-separated string items, so I can't just issue "n" Input()
> statements
> > > per file row.  Is there an end-of-line type of function that will tell
> me
> > if
> > > I've just Input() the last item on a row?  Alternatively, is there a
> > better
> > > way to retrieve the data?

> > > Thanks!

> > > -----
> > > Tim Thornton
> > > College Station, Texas, USA



Sun, 30 Jan 2005 08:20:44 GMT  
 Reading CSV file
Here is a simple function I wrote to handle embeded "," and quotes etc. Feel
free to optimize this and resubmit :)

 Public Shared Function Split(ByVal s As String, ByVal delimiter As Char) As
String()
  Dim c As Char
  Dim quoted As Boolean = False
  Dim quote As Char
  Dim ret As ArrayList = New ArrayList()
  Dim element As String

  For Each c In s
   If c = delimiter AndAlso Not (quoted) Then
    ret.Add(element)
    element = ""
   ElseIf Not quoted AndAlso (c = "'"c Or c = """"c) Then
    quoted = Not quoted
    quote = c
   ElseIf quoted AndAlso c = quote Then
    quoted = Not quoted
   Else
    element += c
   End If
  Next
  ret.Add(element)

  Return ret.ToArray(GetType(String))
 End Function



Quote:
> I want to use the Input() function to read items from a CSV text file.
The
> problem is that each line of data contains anywhere from 17 to 20
> comma-separated string items, so I can't just issue "n" Input() statements
> per file row.  Is there an end-of-line type of function that will tell me
if
> I've just Input() the last item on a row?  Alternatively, is there a
better
> way to retrieve the data?

> Thanks!

> -----
> Tim Thornton
> College Station, Texas, USA



Sun, 30 Jan 2005 23:44:02 GMT  
 Reading CSV file
I changed my code to handle multiple numbers of quotes. It could treat a
quoted item that spans multiple lines, if you pass a non-empty quote stack
in, and handled the concatenation in the calling routine.

For anyone who has not seen "AndAlso" it is a short circuit method so the if
statements won't all be evaluated unless needed (quotes.Peek() would throw
an exception if the stack is empty)

'Code starts here
   Public Shared Sub split(ByVal s As String, ByVal delimiter As Char, ByRef
into As String())
      Dim q as stack = new Stack()
      split(s, delimiter, into, q)
   End Sub

   Public Shared Sub split(ByVal s As String, ByVal delimiter As Char, ByRef
into As String(), ByRef quotes As Stack)
      Dim c As Char
      Dim ret As ArrayList = New ArrayList()
      Dim element As String
      Dim quoted As Boolean = quotes.Count > 0

      For Each c In s
         If c = delimiter AndAlso Not (quoted) Then
            ret.Add(element)
            element = ""
         ElseIf c = "'"c Or c = """"c Then
            If quotes.Count > 0 AndAlso quotes.Peek() = c Then
               quotes.Pop()

               quoted = quotes.Count > 0
               ' if we are still quoted then we are in the value
               If quoted Then
                  element += c
               End If
            Else
               quotes.Push(c)

               ' if we were quoted then we are in the value
               If quoted Then
                  element += c
               Else
                  quoted = True
               End If
            End If
         Else
            element += c
         End If
      Next
      ret.Add(element)

      into = ret.ToArray(GetType(String))
   End Sub



Mon, 31 Jan 2005 04:46:28 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Reading CSV Files in XL-VBA

2. Read CSV files in VB.NET

3. New to VB.net - need to read CSV File into Data Grid

4. Reading CSV file with OleDbDataReader

5. Reading CSV file problem

6. Problem reading CSV file

7. reading CSV Files with VB

8. Read CSV file containing quotes

9. Help reading CSV files

10. Interesting Problem with reading CSV File

11. Reading .CSV files.. any suggestions?

12. Reading a comma separated variable file? (.csv file)

 

 
Powered by phpBB® Forum Software