Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 23rd, 2006, 06:48 PM
Banned
Points: 1,561, Level: 16
Points: 1,561, Level: 16 Points: 1,561, Level: 16 Points: 1,561, Level: 16
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2005
Location: , , .
Posts: 317
Thanks: 0
Thanked 0 Times in 0 Posts
Default ActiveX Script Question

I'm using this ActiveX Script within a DTS package that imports data from a .txt file into a DB table. This is a sample of what the text file looks like:
Code:
text_registrant_id,text_name,date_of_birth
01,01.01,01/01/1981
02,02.02,02/02/1982
03,03.03,03/03/1983
...and so on
...and here's the ActiveX Script:
Code:
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
    Main = DTSTaskExecResult_Success
    Dim fso, f, i, j, k, t, row, datetime_array, StartingKeyValue, StartingOtherValue, NewStartingKey, NewStartingOther, swap_pos, DimensionToSort, OtherDimension
    Dim NewFile, OldFile2, log, incoming, incoming_files, dir_count, willholddate
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set incoming = fso.GetFolder("\\SERVERNAME\DIRECTORY\FOLDER1\") 
    Set incoming_files = incoming.Files
    dir_count = incoming_files.Count
    ReDim datetime_array(dir_count, 1)
    ' *** This loop will load the array with the DateLastModified file attribute ***
    For Each k in incoming_files
        datetime_array(i, 0) = k.Name
        datetime_array(i, 1) = k.DateLastModified
        i = i + 1
    Next 
    ' *** This loop will perform the DateTime sort ***
    Const column = 1
    DimensionToSort = 1
    OtherDimension = 0
    For row = 0 To dir_count - 1
        StartingKeyValue = datetime_array ( row, DimensionToSort )
        StartingOtherValue = datetime_array ( row, OtherDimension )
        NewStartingKey = datetime_array ( row, DimensionToSort )
        NewStartingOther = datetime_array ( row, OtherDimension )
        swap_pos = row
            For j = row + 1 to dir_count - 1
            If datetime_array ( j, DimensionToSort ) < NewStartingKey Then
                swap_pos = j
                NewStartingKey = datetime_array ( j, DimensionToSort )
                NewStartingOther = datetime_array ( j, OtherDimension )
            End If
        Next
        If swap_pos <> row Then
            datetime_array ( swap_pos, DimensionToSort ) = StartingKeyValue
            datetime_array ( swap_pos, OtherDimension ) = StartingOtherValue
            datetime_array ( row, DimensionToSort ) = NewStartingKey
            datetime_array ( row, OtherDimension ) = NewStartingOther
        End If    
    Next

    ' *** This is where the file gets set ***
    NewFile = "\\SERVERNAME\DIRECTORY\FOLDER1\" & datetime_array(dir_count - 1, 0)
    RenamedFile = "\\SERVERNAME\DIRECTORY\FOLDER1\new_export.txt"
    OldFile = "\\SERVERNAME\DIRECTORY\reg_voters2\" & datetime_array(dir_count - 1, 0)
    Import = "\\SERVERNAME\DIRECTORY\reg_voters2\new_export.txt"
    ' *** Rename the file to the name that the Data Source expects ***
    If fso.FileExists(NewFile) Then
    fso.MoveFile NewFile, RenamedFile
    fso.CopyFile RenamedFile, Import
    fso.DeleteFile RenamedFile
    End If
End Function
It works great, but now I need to do the following, and I'm not sure how to include it in this code.

1) Split Precinct_ID and Part_ID (one value = "01.01") into two values ("01" and "01")
2) 'Split and reformat DOB (from "01/01/1981" to "19810101")

This is what I have so far:
Code:
Function newImport()

    'Split Precinct_ID and Part_ID
    Dim OldPP As String = text_name
    If OldPP <> "" Then
        Dim Precinct_ID As String = Substring-before(OldPP, ".")
        Part_ID As String = Substring-after(OldPP, ".")
    End If

    'Split and reformat DOB
    Dim FullDate As String = date_of_birth
    If FullDate <> "" Then
        Dim OldDate As String = RTrim(date_of_birth)
        Dim Date_mm As String = Substring-before(OldDate, "/")
        Dim Date_yyyy As String = Substring-after(OldDate, "/")
        Dim Date_dd As String = Substring-before(Date_ddyyyy, "/")
        Dim Date_ddyyyy As String = Substring-after(Date_ddyyyy, "/")
        Dim NewDate As String = Date_yyyy + Date_mm + Date_dd
    End If

End Function
...but I'm not sure how to include it within the ActiveX Script, being that I'm somewhat of a newbie. If anyone can help me out or point me in the right direction, it would be greatly appreciated. Thanks.

KWilliams
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting Excel Cells Within ActiveX script ninel SQL Server DTS 3 January 28th, 2008 04:17 PM
DTS and ActiveX VB Script gscott SQL Server DTS 6 May 16th, 2007 07:38 AM
$$ Looking for an image uploader activex script medusa PHP How-To 0 February 7th, 2006 12:42 AM
DTS Problem with ActiveX Script testsubject SQL Server DTS 1 December 23rd, 2005 05:11 PM
ActiveX script BradOyler SQL Server DTS 1 May 5th, 2004 06:20 PM



All times are GMT -4. The time now is 02:55 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.