Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Compare tab delimited text file with table field.aize

Posted on 2011-03-20
8
Medium Priority
?
307 Views
Last Modified: 2012-05-11
Hello,

I need to compare the field length in a text file with that of the required field size in its destination table.  If any of the fields are to long, I would like to create a report (can be an export to excel) that details which row the error occured on.  If there are no errors, I append the data to the table.  All the fields are text data type.

EXAMPLE - Field1 has a value of" A01E36" but the field size limit is 5,  this record would be on the report.

I am creating a staging table in DB1, but the destination table would be in a remote DB.

Any ideas??????

Thanks,

T


0
Comment
Question by:Halia
8 Comments
 
LVL 21
ID: 35177908
The way I handle this is the use a "staging tbale" withteh fiedl size set to 255 for all the text fiels.

In a query you can uery check the Length ( Len() ) fo rhta data look9ing for any records that have fiedl that have data that is to long.

... Where Len([FeidlName1]) > 5 or  Len([FeidlName2]) > 10 ....

Open in new window

If the query retrun any recod the you know there is an iss.
0
 

Author Comment

by:Halia
ID: 35177940
Thank You.  I need to make this a bit more dynamic though.  I have about 60 tables, the tables have anywhere from 10 - 150 columns.

I have my staging table with the field size set to 255 - is there anyway to check the staging table field lengths against the destination table field.size?

Thanks!
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 35180461
How many fields are in these text files...is that dynamic as well? or do they match the intended destination table?

What kind of data is it? All text, varied?

You can begin by creating a table of your field definitions by table.

As you open and begin importing your data, you would have to compare length to the known field length from that table.

You can do it on the fly, but it would be very slow.

What kind of text file is it? and what import method are you using?
J
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Halia
ID: 35180611
They are all tab delimited text files. All the fields are being imported as a text data type.

 We have approximately 60 different files that are imported 3-4 times a month.  The destination table for each text file is already created so they SHOULD match.  

At the moment, I am bring the data into a staging table then will be appending that to the destination table.  I'm not concerned about the speed at this point, we are just getting some very crappy data coming in!!!!

Does that explain things better?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35183295
You said you are using a staging table.
I would make all of the fields in the staging table memo.
They'll then hold all the data regardless.

Then you need to check each field for length greater than 255
Create an tblErrors.
ErrorID as AutoNumber/long, Primary Key
RowNumber as long
FieldName as text

Find somewhere to run this code
It parsed a 53 field table with 28000 entries -- granted only one was memo field -- in 40 seconds, generating 12000 entries in tbErrors.

Private Sub ParseForTooLong()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim NumFields As Integer
Dim NumRows As Long
Dim x As Integer ' to count rows
Dim myfield As Field 'each field

Set db = CurrentDb
Set rs1 = db.OpenRecordset("Select * from tblStaging;", dbOpenDynaset, dbSeeChanges)
Set rs2 = db.OpenRecordset("Select * from tblErrors where 1=2;", dbOpenDynaset, dbSeeChanges)

NumFields = rs1.Fields.count

rs1.MoveLast ' populate the recordset fully
rs1.MoveFirst
NumRows = rs1.RecordCount

x = 0

Do Until rs1.EOF = True ' loop through the staging table one record at a time
    x = x + 1 'bump the row counter
    For Each myfield In rs1.Fields 'check each field
        If Len(myfield.Value) > 254 Then 'longer than 254 so it sin;t going to work as text?
            rs2.AddNew ' write an entry to tblErrors
            rs2!rownumber = x ' the row
            rs2!FieldName = myfield.Name 'the field
            rs2.Update
        End If
    Next myfield ' next field please
    rs1.MoveNext ' move ahead in the recordset
Loop ' next record!

MsgBox "Done!"

End Sub

Open in new window

0
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 35183412
Now, instead of just a hard limit of 254, you want dynamic comparison to another table
Add some code!
Add another recordset for the destination table
Add another counter for fields -- because fieldnames may not match

Voila

 
Private Sub ParseForTooLong()
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset
Dim NumFields As Integer
Dim NumRows As Long
Dim x As Integer
Dim y As Integer
Dim myfield As Field

Set db = CurrentDb
Set rs1 = db.OpenRecordset("Select * from tblStaging;", dbOpenDynaset, dbSeeChanges)
Set rs2 = db.OpenRecordset("Select * from tblErrors where 1=2;", dbOpenDynaset, dbSeeChanges)
Set rs3 = db.OpenRecordset("Select * from tblDestination where 1=2;", dbOpenDynaset, dbSeeChanges)

NumFields = rs1.Fields.count

rs1.MoveLast
rs1.MoveFirst
NumRows = rs1.RecordCount

x = 0

Do Until rs1.EOF = True
    y = 0 'field ordinal reset to 0
    x = x + 1
    For Each myfield In rs1.Fields
        If Len(myfield.Value) > rs3.Fields(y).size Then 'fields may not be named the same so refer by index
            rs2.AddNew
            rs2!rownumber = x
            rs2!FieldName = myfield.Name
            rs2.Update
        End If
        y = y + 1 ' increment the field
    Next myfield
    rs1.MoveNext
Loop

MsgBox "Done!"

End Sub

Open in new window

0
 

Author Comment

by:Halia
ID: 35183423
THANK YOU!  I will try this tonight and let you know how it goes....

T
0
 

Author Comment

by:Halia
ID: 35190424
WORKS GREAT!!!!!!    Thank you so much!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

579 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