Auto Import - Strip Out Certain Records.

[JMarcus]

Does anyone know a good foundation for the following?:

Allow end user to import a spreadsheet with one column as a table into database
Run process from a button to do the following:

Strip the last two numbers in the column. The remaining characters in the column link to the main table.
Take the imported table and do a right join with the main table.
Do a query by a date but stripping out all the results of the ones which were imported.

In summary, doing a query stripping out the ones which were imported. If you need anything further please let me know.

 

[sneuberg]

Does anyone know a good foundation for the following?:

Allow end user to import a spreadsheet with one column as a table into database
.

TransferSpreadsheet

Run process from a button to do the following:

Strip the last two numbers in the column. The remaining characters in the column link to the main table.

SQL UPDATE

with

The Left Function

and RunSQL

Take the imported table and do a right join with the main table.
Do a query by a date but stripping out all the results of the ones which were imported.

In summary, doing a query stripping out the ones which were imported. If you need anything further please let me know.

Strip out what from where based on what? Delete records from the main table?

 

[JMarcus]

When running query only have results which were not in the imported table. Not delete any records from the main table.

 

[sneuberg]

When running query only have results which were not in the imported table. Not delete any records from the main table.

Do you mean something like:

SELECT* FROM MainTable WHERE MainTable.SomeDate Not IN (SELECT SomeDate FROM ImportedTable)

 

[JMarcus]

I need to get through step 1 first. I would have to have an end user import the table. I suppose I would have to have them rename it? Then strip out the last (2) characters. I might be able to figure it out past that.

 

[JMarcus]

I can do the import part. I'm just confused as to if the end user is uploading it. It would have to be named a certain way in order for it be imported. Then once imported run a query to strip out the last two characters. Then create a second query with the join and leave off the records from the imported group. Maybe its easy?

 

[sneuberg]

Rather than hard code a location for the imported file I suggest letting the user browse for the file using the file dialog. The function below from here might get you started.

Function getFileName() As String
    Dim fDialog    As Object
    Set fDialog = Application.FileDialog(msoFileDialogSaveAs)
    Dim varFile As Variant

    With fDialog
       .AllowMultiSelect = False
       .Title = "Select File Location to Export XLSx :"
       .InitialFileName = "jeffatwood.xlsx"

    If .Show = True Then
       For Each varFile In .SelectedItems
         getFileName = varFile
       Next
    End If
End With
End Function

 

[gemma-the-husky]

the general technique you want is:

import to a new or temporary table
use queries and code to validate the import, delete unrequired rows, massage the data - add columns, delete columns, etc, and so on.
then append the import to your "real" data tables.