VBScript (.vbs) to rename text file and import new data to Access

Here is the code:

Option Explicit

' UnZip "C:\test.zip" into the folder "C:\test1"
Extract "C:\test.zip", "C:\test1"

' Extract "C:\test.cab" into the folder "C:\test2"
'Extract "C:\test.cab", "C:\test2"

' Copy the contents of folder "C:\test2" to the folder "C:\test3"
'Extract "C:\test2", "C:\test3"


Sub Extract( ByVal myZipFile, ByVal myTargetDir )


    Dim intOptions, objShell, objSource, objTarget

    ' Create the required Shell objects
    Set objShell = CreateObject( "Shell.Application" )

    ' Create a reference to the files and folders in the ZIP file
    Set objSource = objShell.NameSpace(File).Items( )

    ' Create a reference to the target folder
    Set objTarget = objShell.NameSpace(Folder)

   
    intOptions = 256

    ' UnZIP the files
    objTarget.CopyHere objSource, intOptions

    ' Release the objects
    Set objSource = Nothing
    Set objTarget = Nothing
    Set objShell  = Nothing
End Sub

Hi,

In VBA the Dir("foldername")  command returns the file names in foldername.  You can pass the file name into a variable and use that to change the name.

Regards,

Bill

Thanks.  What do i need to do to only append new data into an access database table?

Hi,

Once you know the file name you can use the option docmd.transfertext ..... in your vba code.

This is the same as automating get external data from the menu.

Regards,

Bill

Wouldnt that append all of the data?  

For example, say your bank puts a text file out on their website with one row of data for each billing period.  when they update the file, which is a rolling twelve months of history, you would see only one new row.  if i want to append only that one new row, how do i do that?  

I used this example since it was easy to explain.  If you expland this by a few tens of thousands of rows, you will see that I don't want to keep adding all of these rows to make duplicate entries.

Hi,

This is quite common.  When the bank sends over transactions they always have a unique transaction ID.  Make sure the imported data has a primary key and that is the primary key in your table.  Any existing rows found in the export will be discarded even if they exist in the file.

Regards,

Bill  

Unfortunately, the data isnt actually bank transactions.  It is electricity usage data that doesnt have a unique key without combining two columns together.

Hi,

That's OK.  Just make the same fields the PK in the import table as the 2 fields in the import file.  The import table must have a primary key that matches the data anyway since you need to know which records to import so all should be fine.

Send the file structure if this isn't clear.

Regards,

Bill

Ok, now I have a minor problem.  One of the primary keys is a 20 digit number and when I try importing as a number, it will give me a scientific number.  I thought about parsing through the text files to find a distinct list of the numbers and their corresponding max date record.  I would then compare that to the maximum date for that particular number in the database table and only import the records with dates that are greater than what is in the database.  The only problem with this is I don't know how to do any of this.  Have any thoughts?

Hi,

Please post a sample of the import file and your database.  

You can easily use a text field for the 20 digit field in yoru import file.  You aren't going to do any math on it so treat it like a big word made of numbers.  I.e. an account number would not really be a number but just a long string of digits.

It is not a good idea to import the data then exclude some data prior to a certain date.  That would make it possibile to import the same info twice or miss some data all together.

In my experience you should concentrate on the unique record/primary key process above.

Regards,

Bill

Due to the nature of the data, I can't post a sample as it stands right now.  However, the account number + the date would collectively be the only way I could key off of anything.  There is nothing else in the import file I can use to key off of.  I will work on getting the sample data together in the next 20 minutes.

OK.

Here is the sample data:
Sample-Source-Data.txt
SampleDB.mdb

Ok, I have both of the fields set as the primary key.  Is there any other setting that I need to make in order to keep duplicate records from being entered?

One more question for you.  Is there a way to link to this macro from VBScript to have it run?  Can I have the VBScript update the file name as it changes?

Thanks Bill.  How can I call this particular macro from a VBScript instead of from within the Access Database?

I have a lot more experience with Excel to PowerPoint and Excel to Internet Explorer VBA code.  My limited Excel to Access (Upload and Query) experience doesn't include calling Access based VBA code.

Also, where does the Specification Name "ImportDataSpec" come from?  I am trying to re-create what you did in the sample database in my real one and it doesn't work.

Hey Bill,

I am not having any luck with getting the text file to import into the database tables.  Here is the code:

rivate Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Const DBPath = "C:\SampleDB.mdb"
Sub TransferData()

Dim AccessApp As Object

Set AccessApp = GetObject(DBPath, "Access.Application")
AccessApp.DoCmd.TransferText acExportDelim, "ImportDataSpec", "ImportData", "C:\Import.txt", True
Sleep 60000
AccessApp.Quit

End Sub

Select allOpen in new window

you can disregard my previous comment.  I just changed the acExportDelim to acImportDelim and it works.  Thank you so much for you help.

Glad to help.