Count field length in delimeted file and compare with predefined listof field length

I

Ixtreme

I have a database that imports data from a csv file ( ~ separated).
The tables have all fields set to text.

What I want is to display a message after importing the files that
list the total number of records per table per field where the field
length contains more characters than is allowed.

Is there an easy way to achieve this?
 
B

Brendan Reynolds

Ixtreme said:
I have a database that imports data from a csv file ( ~ separated).
The tables have all fields set to text.

What I want is to display a message after importing the files that
list the total number of records per table per field where the field
length contains more characters than is allowed.

Is there an easy way to achieve this?
Click to expand...


Well, easy is a relative term. It's easy if you're an experienced VBA
programmer. Perhaps not so easy if you're not. Anyhow, here's an example ...

Public Function CountLongFields() As Long

Dim intFile As Integer
Dim strLine As String
Dim astrFields() As String
Dim iastrFields As Long
Dim strField As String
Dim lngCounter As Long

intFile = FreeFile

'open the text file
Open CurrentProject.Path & "\test.txt" For Input As #intFile

'loop until the end of the file
Do Until EOF(intFile)

'read each line of text from the file
Line Input #intFile, strLine

'split the line into fields
astrFields = Split(strLine, "~")

'check the length of each field
For iastrFields = LBound(astrFields) To UBound(astrFields)
strField = astrFields(iastrFields)

'if the length exceeds the max, increment a counter
If Len(strField) > 4 Then
lngCounter = lngCounter + 1
End If

Next iastrFields
Loop

'close the file
Close #intFile

'return the number of over-long fields
CountLongFields = lngCounter

End Function
 
I

Ixtreme

Hi Brendan,

Thanks for your answer. If I understand correctly, this code checks
all fields in file text.txt and increases the counter if the field
lenght exceeds 4 characters. However, I would like to check all fields
against different field lengths. I mean fieldd 1 may have max 4
characters, field 2 may have max 10 characters, field 3 may have max
25 characters. Hopefully it's 'easy' to adapt this code but I have no
idea how ;-)

Mark
 
J

John Spencer

To adapt that handle an unknown number of fields with unknown lengths
would be considerable effort.

If you know the number of fields and the lengths you could build an
array into the function and populate array. Lets say you have 22 fields

Public Function CountLongFields() As Long

Dim vArr(21,2) as Long
vArr(0,0) = 4 'Max Length of first field
vArr(1,0) = 22 'Max length of second field
.... repeat for remaining fields

Dim intFile As Integer
Dim strLine As String
Dim astrFields() As String
Dim iastrFields As Long
Dim strField As String
Dim lngCounter As Long

intFile = FreeFile

'open the text file
Open CurrentProject.Path & "\test.txt" For Input As #intFile

'loop until the end of the file
Do Until EOF(intFile)

'read each line of text from the file
Line Input #intFile, strLine

'split the line into fields
astrFields = Split(strLine, "~")

'check the length of each field
For iastrFields = LBound(astrFields) To UBound(astrFields)
strField = astrFields(iastrFields)

'if the length exceeds the max, increment a counter
If Len(strField) > vArr(iAstrFields,0) Then
vArr(iAstrFields,1) = vArr(iAstrFields,1)
End If

Next iastrFields
Loop

'close the file
Close #intFile

'return the number of over-long fields
' CountLongFields = lngCounter
' Now you need to loop through vArr and get the counts over zero
' And report that data back somehow. This prints the info in the
' immediate window. You could write the data back to a string,
' put it into a work table, write it to a text file, etc.
For iAstrFields = 0 to 21
If vArr(iAstrFields,1) > 0 then
Debug.Print vArr(Iastrfields,1) & _
" records for Field " & iAstrFields +1 & _
" are over length"

End If

Next iAstrFields

End Function

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
I

Ixtreme

To adapt that handle an unknown number of fields with unknown lengths
would be considerable effort.

If you know the number of fields and the lengths you could build an
array into the function and populate array.  Lets say you have 22 fields

Public Function CountLongFields() As Long

Dim vArr(21,2) as Long
vArr(0,0) = 4 'Max Length of first field
vArr(1,0) = 22 'Max length of second field
... repeat for remaining fields

    Dim intFile As Integer
    Dim strLine As String
    Dim astrFields() As String
    Dim iastrFields As Long
    Dim strField As String
    Dim lngCounter As Long

    intFile = FreeFile

    'open the text file
    Open CurrentProject.Path & "\test.txt" For Input As #intFile

    'loop until the end of the file
    Do Until EOF(intFile)

        'read each line of text from the file
        Line Input #intFile, strLine

        'split the line into fields
        astrFields = Split(strLine, "~")

        'check the length of each field
        For iastrFields = LBound(astrFields) To UBound(astrFields)
            strField = astrFields(iastrFields)

            'if the length exceeds the max, increment a counter
            If Len(strField) > vArr(iAstrFields,0) Then
                vArr(iAstrFields,1) = vArr(iAstrFields,1)
            End If

        Next iastrFields
    Loop

    'close the file
    Close #intFile

    'return the number of over-long fields
'   CountLongFields = lngCounter
' Now you need to loop through vArr and get the counts over zero
' And report that data back somehow.  This prints the info in the
' immediate window.  You could write the data back to a string,
' put it into a work table, write it to a text file, etc.
   For iAstrFields = 0 to 21
     If vArr(iAstrFields,1) > 0 then
        Debug.Print vArr(Iastrfields,1) & _
         " records for Field " & iAstrFields +1 & _
         " are over length"

     End If

   Next iAstrFields

End Function

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================
Click to expand...
John,

I have a file that contains 88 fields. Each field has a fixed maximum
length.If I change your code, it somehow gives me an error on "If Len
(strField) > vArr(iastrFields, 0) Then".

The value of iastrFields = 89 which is incorrect right?

I have changed your code like this:

Dim vArr(88, 2) As Long
vArr(0, 0) = 50 'Max Length of first field
vArr(1, 0) = 50 'Max length of second field
vArr(2, 0) = 20
.....
.....
vArr(87, 0) = 4


What if I want to check if the field contains a (valid) date? Could I
adapt the code a bit to check that as well?

Thanks for your help.
 
J

John Spencer

Checking for a valid date is going to complicate this function quite a
bit more. You can use the IsDate function to check a string to see if
it is a valid date. However, the code is going to need a way to
determine if the field is supposed to be a date field.

Next you are going to ask about numbers. The IsNumeric function will
tell you if something is a number. BOTH functions will return false for
nulls, so you would also need to test for nulls as valid (or not).

TRY modifying the line that fails to the following.

IF Len(strField & "") > vArr(iAstrFields,0) THEN

If that still errors then could you at least tell us WHAT the error
message is? It helps if you know what the error is.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
I

Ixtreme

I have:
Dim vArr(87, 2) As Long
vArr(0, 0) = 50 'Max Length of first field
vArr(1, 0) = 50 'Max length of second field
etc etc until
vArr(87, 0) = 20

(0 - 87) that should be a total of 88 fields right?
I know what each field should look like. So for all 88 I know what
field should come in a a number, a date, or a currency etc.

On the new code, it gives me this message:

Runtime-error '9'
Subscript out of range.

If I click on iastrFields it tells me iastrFields = 88. Since I have
only 87 'fields' defined, I understand it gives me an Runtime error
'9'

If I open my txt file in notepad, I see all fields separated by '~'
and the end of the records looks like a tiny rectangle which I cannot
paste here (except the last record, that does not have that.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import fixed-length file and pad field with spaces 1
space filled field in fixed length file 1
Monthly Reports - Macro Count 2
CSV import error only with code 1
Fixed Character (alpha) length 5
comparing two tables with the same field 10
How many nulls in each field? 10
Exporting to a txt-file with fixed field length 2

Top