?
Solved

Audit Tab Delimited File - Check how many tabs in each row using ActiveX Script

Posted on 2006-05-11
6
Medium Priority
?
272 Views
Last Modified: 2012-08-13
I need to be able to check and count no. of tabs in each row for a tab delimited file before I import it into SQL Server.  I am using ActiveX Script within DTS Packages.  I would want a msgbox to display row no. and how many tabs were found if more than 314 tabs in one row.

Currently I have the following script as a base, but as im quite new to activex script, this hasnt worked:

**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
Const ForReading = 1
dim fso, tsFile, varLine, strDelimiter, strLine, ValueMsg, strTextFilePath, pkg
set pkg = DTSGlobalVariables.Parent
strTextFilePath =DTSGlobalVariables("gv_File").Value
set fso = createobject("Scripting.FileSystemObject")
set tsFile = fso.OpenTextFile(strTextFilePath, 1)
strDelimiter = vbTab '(for tab delimited)
strLine = tsFile.ReadLine

do while not tsFile.AtEndOfStream
    strLine = tsFile.ReadLine
      If UBound(Split(strLine, vbTab)) <> 314 Then
                        Msgbox("File is okay to import")
            else

                        Msgbox("Row No: (strLine) has over 314 tabs (No. of tabs)")
        End If
loop
Main = DTSTaskExecResult_Success
End Function


Please could you help...
0
Comment
Question by:jd_dobariya
  • 3
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16664166
The best way to accomplish this is to do this when you import into your staging table BEFORE you import into your final production tables.  However, if you insist you should be able to do it like you suggest as in:

Function Main()
Const ForReading = 1
Dim fso, tsFile, varLine, strTextFilePath, Tabs

strTextFilePath = DTSGlobalVariables("gv_File").Value
Set fso = CreateObject("Scripting.FileSystemObject")
Set tsFile = fso.OpenTextFile(strTextFilePath, ForReading)
varLine = 0
Tabs = 313
Do While Tabs = 313 And Not tsFile.AtEndOfStream
      strLine = tsFile.ReadLine
      varLine = varLine + 1
      Tabs = UBound(Split(strLine, vbTab))
      If Tabs <> 313 Then
            MsgBox "Houston we have a problem!  Row No:  " & CStr(varLine) & " has " & CStr(Tabs) & " tab(s)"
            Main = DTSTaskExecResult_Failure
      Else
            Main = DTSTaskExecResult_Success
      End If
Loop

End Function
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16664169
P.S.  The Message Box will only be visible if you run the DTS package interactively and not scheduled.
0
 

Author Comment

by:jd_dobariya
ID: 16666486
I know I can import inot Stage and then into Target table, but my Stage table is set to 314 columns, as we should only expect less or equal to 314 columns within the file.

Ive tried the script above, it works but it keeps notifiying me of rows which are less than 313 tabs as well (which i dont need to know), I know this is due to using not equal to 313.  I have changed the code so that it only brings up a msgbox if a row has >= 315 tabs.  But it doesnt seem to work on this file which has 317 tabs.

Function Main()
Const ForReading = 1
Dim fso, tsFile, varLine, strTextFilePath, Tabs

strTextFilePath = DTSGlobalVariables("gv_File").Value
Set fso = CreateObject("Scripting.FileSystemObject")
Set tsFile = fso.OpenTextFile(strTextFilePath, ForReading)
varLine = 0
Tabs = 314
Do While Tabs <= 314 And Not tsFile.AtEndOfStream
     strLine = tsFile.ReadLine
     varLine = varLine + 1
     Tabs = UBound(Split(strLine, vbTab))
     If Tabs >= 314 Then
         Main = DTSTaskExecResult_Success
     Else
       
       'MsgBox "Bassi we have a problem!  Row No:  " & CStr(varLine) & " has " & CStr(Tabs) & " tab(s)"
        Main = DTSTaskExecResult_Failure
     End If
Loop

End Function


any ideas?.....

P.S. I am aware of the msgbox apearing if dts package run manually - this step will be amended to print. (just used for testing purposes).
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 16672147
>> it works but it keeps notifiying me of rows which are less than 313 tabs as well
That would be because you have reversed the logic, it should be:


Function Main()
Const ForReading = 1
Dim fso, tsFile, varLine, strTextFilePath, Tabs

strTextFilePath = DTSGlobalVariables("gv_File").Value
Set fso = CreateObject("Scripting.FileSystemObject")
Set tsFile = fso.OpenTextFile(strTextFilePath, ForReading)
varLine = 0
Tabs = 314
Do While Tabs <= 314 And Not tsFile.AtEndOfStream
     strLine = tsFile.ReadLine
     varLine = varLine + 1
     Tabs = UBound(Split(strLine, vbTab))
     If Tabs >= 314 Then
      'MsgBox "Bassi we have a problem!  Row No:  " & CStr(varLine) & " has " & CStr(Tabs) & " tab(s)"
       Main = DTSTaskExecResult_Failure
    Else
        Main = DTSTaskExecResult_Success
     End If
Loop

End Function
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

864 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