Solved

COMMA DELIMITED

Posted on 2001-06-29
9
571 Views
Last Modified: 2008-10-15
Okey peeps,

help me on this,

i need to read a line in sometimes it can be 36 fields in comma delimeted format and sometimes the lines change.

in other words the first line could be 36 fields and then the second line could change to 42 fields.

what i would like to do it to write the whole line and then read the first 36 fields of that line.

and assign each field to a var,

i have a program already written which can read a normal deliemeted file but when the delimiter changes my program spouts up.

i dont want any url's posting. if somebody could show a peiece of code that would be great.

eg.
this could be a example of a file

1) line "Experts exchange","64 lost am i","kkkk","llllll","kkkk"

2) line "lerlek","lkl","reklre","lrkel","rkel","rkelk","rekje"

i need to read the first 36 or what ever and disregard the rest

cheers
Andy]
0
Comment
Question by:andysalih
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 6237516
Hi use the Split function in VB6

dim array() as string

array = split("Experts exchange","64 lost am i","kkkk","llllll","kkkk")

for i = Lbound(array) to Ubound(array)
  'do thing you want..
next i

'Hope will help.



0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 6237520
Hi,

to read the first 36

mylimit =36

if ubound(array) < mylimit -1 then exit sub 'Do what ever to exit

for i = Lbound(array) to mylimit -1
 'do thing you want..
next i

'Hope will help.
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 200 total points
ID: 6237527
ryancys has the solution:

Dim aryContent() As String
Dim strLine As String
Dim intElement As Integer
Open "MyFile" For Input As #1
Do
  Line Input #1,strLine
  aryContent = Split(Replace(strLine,Chr(34),""),",")
  ReDim Preserve aryContent(36)
  For intElement = LBound(aryContent) To UBound(aryContent)
    'Do Something with each element
  Next
Loop Until Eof(1)
Close #1

This version strips out the " character(s) from the elements and redims the array to the first 36 elements only each time, discarding the remaining if any exist (and if there are less than 36 it will fill the remaining elements with empty strings.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6237567
Here is a sub that will do what you need:

The sub handles Strings, numerics, dates and nulls.

Strings can have Double Double quotes in them like:

"The boat is 25"" Long"

The CSV line can have any amount of fields and you can ignore the onces you don't need.

How to use:

Redim YourFields(0) as variant
Redim FieldTypes(0) as string
Dim UserOptions$
ReDim FieldCount as long
Dim ErMessage as string

UserOptions$ = "/ND" ' Return dates as strings


Open "Yourfile.csv" for input as #1

Do While Not Eof(1)
  Line Input #1, DataIn$
  SplitCSV DataIn$, YourFields(), FieldTypes(), FieldCount, UserOptions, ErMessage
  if Len(ErMessage$)>0 Then
      Msgbox ermessage,vbexclamation,"CSV Format Error"
  End If  

  ' Now save you data
  ' here is a sample recordset add
  RS.AddNew
  For FC= 1 to FieldCount
    RS("Field"+cstr(fc))=YourFields(FC)
  Next
  RS.Update

Loop

close 1


See comments in the sub for more details.

Hope this helps.....forever inthedark


Public Sub SplitCSV(StringToSplit As String, ReturnArray() As Variant, ReturnTypes() As String, FieldCount As Long, UserOptions$, InvalidFormat As Boolean)

' Subroutine to split CSV lines
'
' Created by Nick Young
' Date: 15/01/2001
' Please report any bug fixes to nyoung@vipintersoft.com

' Pass Stringtosplit as the line of data to be split
' Returns the following fields:

' ReturnArray() variant containing values either string, date or numeric
' Notes: strings have their "" removed
' Also handles strings like "This text has a "" double double quote inside"
' first field is ReturnArray(1)

' returntypes() array of type of data that was found
' T=Text, N=Numeric, D=Date, E=Empty field
' FieldCount is returned as the number of fields, 1=first field


' FormatError is set if the file is invalid

' UserOptions /ND = no dates conversion /NN No numeric conversion
' if /ND is specified then dates are returned as strings
' if /NN is specified numerics are returnd as strings

' Example:
' Redim Flds() as variant
' Redim FldTypes$()
' Dim FieldCount as long
' Dim InvalidFormat as boolean
' call SplitCSV(L$, Flds(), FldTypes(), FieldCount,"",InvalidFormat)

Static Fieldslasttime As Long

If Fieldslasttime = 0 Then
    Fieldslasttime = 100
End If

ReDim ReturnArray(Fieldslasttime)
ReDim ReturnTypes(Fieldslasttime)


Dim CPos As Long    ' next comma
Dim LPos As Long    ' last comma

Dim FieldLength As Long

Dim StringDelimiter As Long
Dim Delimiter$
Dim SS$     ' string to split

' handle double quotes
Dim DoubleDoubleFound As Boolean
Dim StartFromPos As Long
Dim XPos As Long    ' postion of "
Dim DV As Date
Dim NV As Double

Dim ConvertDates As Boolean
Dim ConvertNumerics As Boolean

ConvertDates = True
ConvertNumerics = True

If Len(UserOptions$) = 0 Then
    SS$ = UCase$(UserOptions)
    If InStr(SS$, "/ND") Then
        ConvertDates = False
    End If
    If InStr(SS$, "/NN") Then
        ConvertNumerics = False
    End If
End If

SS$ = Trim$(StringToSplit)

StringDelimiter = 34 ' Double quote
Delimiter$ = ","
Dim StringLength As Long

StringLength = Len(SS$)

FieldCount = 0
LPos = 0
Do
    'find first comma
    LPos = LPos + 1
    If LPos > StringLength Then
        If Right(SS$, 1) = Delimiter$ Then
            GoSub AddFieldCount ' must be a blank field
        End If
        Exit Do
    End If
    CPos = InStr(LPos, SS$, Delimiter$)
   
    GoSub AddFieldCount
    If CPos = 0 Then
        CPos = StringLength + 1
    End If
   
    FieldLength = CPos - LPos
   
    If FieldLength > 0 Then
   
        ' Have we found a double quoted string?
        If Asc(Mid(SS$, LPos, 1)) = StringDelimiter Then
            ' so the comma we found may not be the end of the field.
            ' because its a string ther must be another " to find
            StartFromPos = LPos + 1
            Do
                XPos = InStr(StartFromPos, SS$, Chr$(StringDelimiter))
                If XPos < 1 Then
                    ' File format error
                    FieldCount = -FieldCount
                    Exit Sub
                End If
               
                ' But we may have a situation like "Some Text""MoreText"
                If XPos >= StringLength Then Exit Do
               
                If Asc(Mid(SS$, XPos + 1, 1)) <> StringDelimiter Then Exit Do
               
                ' we need to keep searching for next single double quote
                DoubleDoubleFound = True
                StartFromPos = XPos + 2
            Loop
                   
           
            If CPos < XPos Then
                ' need to find next comma after string delimiter
                CPos = InStr(XPos, SS$, Delimiter$)
                If CPos = 0 Then
                    CPos = StringLength + 1
                End If
               
            End If

            FieldLength = XPos - LPos - 1
           
            ' So we now have a quoted string string
            ReturnTypes(FieldCount) = "T"
            If FieldLength > 0 Then
                ReturnArray(FieldCount) = Mid(SS$, LPos + 1, FieldLength)
                If DoubleDoubleFound Then
                    ReturnArray(FieldCount) = Replace(ReturnArray(FieldCount), Chr$(StringDelimiter) + Chr$(StringDelimiter), Chr$(StringDelimiter), 1, -1)
                End If
            Else
                ReturnArray(FieldCount) = ""
            End If
        Else
            ReturnArray(FieldCount) = Mid(SS$, LPos, FieldLength)
            If InStr(ReturnArray(FieldCount), "/") > 0 Then
                ' must be a date
                ReturnTypes(FieldCount) = "D"
                If ConvertDates Then
                    On Error Resume Next
                    Err.Clear
                    DV = DateValue(ReturnArray(FieldCount))
                    If Err.Number = 0 Then
                        ReturnArray(FieldCount) = DV
                    Else
                        InvalidFormat = True
                    End If
                    On Error GoTo 0
                End If
            Else
                ' must be a number
                ReturnTypes(FieldCount) = "N"
                If ConvertNumerics Then
                    On Error Resume Next
                    Err.Clear
                    NV = Val(ReturnArray(FieldCount))
                    If Err.Number = 0 Then
                        ReturnArray(FieldCount) = NV
                    Else
                        InvalidFormat = True
                    End If
                    On Error GoTo 0
                End If
            End If
        End If
    End If
    LPos = CPos
Loop

If FieldCount <> Fieldslasttime Then
    Fieldslasttime = FieldCount
    ReDim Preserve ReturnArray(Fieldslasttime)
    ReDim Preserve ReturnTypes(Fieldslasttime)
End If

Exit Sub

AddFieldCount:

FieldCount = FieldCount + 1

If FieldCount > UBound(ReturnArray) Then
    ReDim Preserve ReturnArray(FieldCount + 50)
    ReDim Preserve ReturnTypes(FieldCount + 50)
End If
   
ReturnArray(FieldCount) = Null
ReturnTypes(FieldCount) = "E"

Return


End Sub



0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:andysalih
ID: 6237581
tim it doesnt read the next line, its not looping around

also how do i display whats in the array,

im lost with this one,

im prob that nervusabout getting this done im bneing thick

0
 
LVL 17

Expert Comment

by:inthedark
ID: 6237587
The split function is fast but you will find that oneday your data gets screwed because somebody created aline like:

"Field1 has commas, quotes " and other stange things in it", "Field 2", "Feidl3: To Be, Or Not To Be"

You can't use the Split because a comma is valid within Quotes.

The SplitCSV Sub that I posted will also convert Numerics and Dates and remove the "" from arround strings.

Best of luck....
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6237601
Sorry I forgot that the ermessage is a boolean so change:

Dim ErMessage as Boolean

and

SplitCSV DataIn$, YourFields(), FieldTypes(), FieldCount, UserOptions, ErMessage
 if ErMessage Then
     Msgbox "File is damaged in line: "+vbcrlf+Datain$,vbexclamation,"CSV Format Error"
 End If  

Woops...
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6237674
So now modified example to ignore fields above 36:


Redim YourFields(0) as variant
Redim FieldTypes(0) as string
Dim UserOptions$
ReDim FieldCount as long
Dim ErMessage as boolean
UserOptions$ = "/ND" ' Return dates as strings


Open "Yourfile.csv" for input as #1

Do While Not Eof(1)
 Line Input #1, DataIn$SplitCSV DataIn$, YourFields(), FieldTypes(), FieldCount, UserOptions, ErMessage
if ErMessage Then
    Msgbox "File is damaged in line: "+vbcrlf+Datain$,vbexclamation,"CSV Format Error"
End If  

 ' Now save you data
 ' here is a sample recordset add
 RS.AddNew

 ' to ignore more than 36 items
 if fieldcount>36 then
    fieldcount = 36
 end if

 For FC= 1 to FieldCount
   RS("Field"+cstr(fc))=YourFields(FC)
 Next
 RS.Update

Loop

close 1
0
 
LVL 3

Author Comment

by:andysalih
ID: 6238138
Tim,

thanks matey, it worked like a dream, short and sweet.

does it brilliant.


thanks for a fast responce.

points well earned
cheers
Andy


others
-------

thanks to everybody who contributed to my question.
im sorry i didnt take your samples but i was also after the shortest.

im aware that adding a comma may mess the file up but it will only make it read it as a field so im not to fussy,

the program is working great and is going to be installed in the company who has purchased it soon.

many Thanks
Andy



0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now