Count columns in delimited file

[Ixtreme]

I have some tables with a specified number of fields. However,
sometimes I need to import files that have more fields than the table
holds. What I would like is to count the total columns of a ~
delimited file prior to have it imported into MS-Access. After that, a
message should say something like: "The table holds 40 fields, you are
about to import 45 fields for each record. As a consequence, data will
get lost." What do I have to do to achieve this?

 

[Danny Lesandrini]

What I suppose I'd do is to read the first line of the file and slam
it into an Array using the Split() function. Then just read the upper
bounds of the array.

 

[Jack Cannon]

Assuming that you are referring to a txt or csv file that uses the ~ as a
delimiter
then the following should accomplish the objective.


Dim fs, f, ts, strText As String, intCount As Integer, intPos As Integer

Set fs = CreateObject("Scripting.FileSystemObject") ' Create object
for File System
Set f = fs.GetFile("MyDelimitedFile.txt")
Set ts = f.OpenAsTextStream(1)
strText = ts.readline
intCount = 0
If Right(strText, 1) = "~" Then ' Is ~ in the last
position
strText = Left(strText, Len(strText) - 1) ' Remove trailing ~
End If
If Len(strText) > 0 Then intCount = 1 ' At least one column
While Len(strText) > 0
intPos = InStr(strText, "~")
If intPos = 0 Then
strText = ""
Else
intCount = intCount + 1
strText = Right(strText, Len(strText) - intPos)
End If
Wend
ts.Close
Set ts = Nothing
Set f = Nothing
Set fs = Nothing
' intCount now contains the number of columns


Jack Cannon

 

[Ixtreme]

Assuming that you are referring to a txt or csv file that uses the ~ as a
delimiter
then the following should accomplish the objective.

    Dim fs, f, ts, strText As String, intCount As Integer, intPos As Integer

    Set fs = CreateObject("Scripting.FileSystemObject")     ' Create object
for File System
    Set f = fs.GetFile("MyDelimitedFile.txt")
    Set ts = f.OpenAsTextStream(1)
    strText = ts.readline
    intCount = 0
    If Right(strText, 1) = "~" Then                ' Is ~ in the last
position
        strText = Left(strText, Len(strText) - 1)   ' Remove trailing ~
    End If
    If Len(strText) > 0 Then intCount = 1           ' At least one column
    While Len(strText) > 0
        intPos = InStr(strText, "~")
        If intPos = 0 Then
            strText = ""
        Else
            intCount = intCount + 1
            strText = Right(strText, Len(strText) - intPos)
        End If
    Wend
    ts.Close
    Set ts = Nothing
    Set f = Nothing
    Set fs = Nothing
    ' intCount now contains the number of columns

Jack Cannon





- Tekst uit oorspronkelijk bericht weergeven -

Jack,

Thanks! That did the trick.