Button to browse for csv file and add it to a table (1 Viewer)

[Sec3Op41514]

Hello! New user here, I am looking for a way to hit a button and add a file to a table.

The file name changes constantly based on the date and time so i cannot use a default file location, it HAS to be a browser. I can attach a sample csv to show what the exported file looks like.

It either needs to append to a current table, OR the button can delete the table, then recreate it with the exact same name when importing, because the import file will always have the same data as it's predecessor, just with more new stuff added on. The table would be called "Catalog ID Imports"!

Thanks for all the help! It just seems to be super confusing and I am just learning how to use access vba.

~I'm a noob but i'm like a sponge, ready to learn!~

Edit: just to state i have tried many things I've found on internet and they all seem catered to something specific and i'm just not knowledgeable enough to figure out how to change those to my needs... i appreciate any and all help given though!

 

[theDBguy]

Hi. Welcome to AWF!

Have you considered breaking down the task into separate pieces to make it easier to get what you need? For example, are you already able to browse for CSV files yet?

 

[Sec3Op41514]

Hi. Welcome to AWF!

Have you considered breaking down the task into separate pieces to make it easier to get what you need? For example, are you already able to browse for CSV files yet?

Yes I can browse for the CSV in some instances.. but it doesnt do anything. It's like i'm calling forth the file but that's it. Someone on reddit had helped me with atleast that far. Here is the code so far:

Private Sub Command51_Click()
Dim Filename As String
Filename = SelectFilename("Select Products Export with Catalog ID Numbers")
MsgBox Filename
End Sub

Public Function SelectFilename(strTitle As String, Optional strInitialDir As String, Optional strExt As String, Optional blnMultiSelect As Boolean, Optional intAction As Integer = 3) As Variant
'Returns an array of values if blnMultiselect is True; returns a string value otherwise.
'If blnMultiSelect is False, then check for empty string upon return; if True, check that returned array is not Empty.
'intAction: type of action from list of msoFileDialog constants (default is FilePicker).

On Error GoTo Error_Label
    
    Const msoFileDialogOpen = 1
    Const msoFileDialogSaveAs = 2
    Const msoFileDialogFilePicker = 3
    Const msoFileDialogFolderPicker = 4
    
    Dim fDialog As Object
    Dim varFile As Variant
    Dim strInitDir As String
    Dim varItems() As Variant
    Dim i As Integer
    
    If strInitialDir = "" Then
        strInitDir = "C:\"
    ElseIf Dir(strInitialDir, vbDirectory) = "" Then
        strInitDir = "C:\"
    Else
        strInitDir = strInitialDir
    End If

    If Right(strInitDir, 1) <> "\" Then strInitDir = strInitDir & "\"
    
    If strExt <> "" Then
        strInitDir = strInitDir & "*." & strExt
    End If
    
    Set fDialog = Application.FileDialog(intAction)
    
    With fDialog
        .AllowMultiSelect = blnMultiSelect
        .Title = strTitle
        .InitialFileName = strInitDir
        
        If .Show = True Then
            If Not blnMultiSelect Then
                SelectFilename = .SelectedItems(1)
            Else
                ReDim varItems(1 To .SelectedItems.Count)
                For i = 1 To .SelectedItems.Count
                    varItems(i) = .SelectedItems(i)
                Next
                SelectFilename = varItems()
            End If
        End If
    End With
    
Exit_Label:
    Set fDialog = Nothing
    Exit Function
    
Error_Label:
    MsgBox Err.Description, vbCritical, "Error " & Err.Number
    Resume Exit_Label
    Resume 0

End Function

 

[Gasman]

Why do you have multiselect?, surely you want just the one file?

 

[Sec3Op41514]

Why do you have multiselect?, surely you want just the one file?

haha yes, just one file. again this was from someone who was helping me on reddit.

 

[The_Doc_Man]

It either needs to append to a current table, OR the button can delete the table, then recreate it with the exact same name when importing, because the import file will always have the same data as it's predecessor, just with more new stuff added on. The table would be called "Catalog ID Imports"!

Since you are new, I'll give you some peripheral information to file for future reference. A sequence to delete a table (or erase it) and then re-load it would be a bad choice because of something called "database bloat." Access is not 100% efficient with something the computer industry calls by the unseemly name "garbage collection" - which relates to a program's ability to dynamically re-optimize its use of working memory. This lack of efficiency causes the file holding this deleted / erased table to grow much bigger than it really needs to be, leading eventually to a need to perform a Compact & Repair. This C&R action is found on the File tab of the Access ribbon.

Advice from theDBguy to break the problem up into parts is ALSO good. (It was good enough for Julius Caesar when he took over France.) "Divide and conquer" is ALWAYS a good strategy in any computer problem analysis. If you can find a way to download the file you want, then you can take a separate action on that (now monolithic) file and not get involved in manipulating a web page to get a piece at a time.

IF you are ALWAYS adding data, but what was already there will always be the same in the next cycle, you would do better to follow a path that links the .CSV file, which Access can treat as an external table. Then you could run a query to test whether for a given record you already had that information and not append the record, but if it is new data you could append it as needed. This approach would require that you have a way to test some unique field or unique combination of fields so you could identify the new data. If you can't do that, this problem would take more work.

You said you are a new Access user, so the key words and concepts to look up / study would be "dynamically link at a table" and perhaps the structure of append queries using the INSERT INTO...SELECT...FROM sequence.

Your avatar icon and your name suggest that you are from the Office of Naval Intelligence. Some people on this forum have U.S. Navy experience. I was a contractor for part of SPAWAR before their most recent name change due to the creation of the US Space Force. I retired before the name change.

 

[Sec3Op41514]

Since you are new, I'll give you some peripheral information to file for future reference. A sequence to delete a table (or erase it) and then re-load it would be a bad choice because of something called "database bloat." Access is not 100% efficient with something the computer industry calls by the unseemly name "garbage collection" - which relates to a program's ability to dynamically re-optimize its use of working memory. This lack of efficiency causes the file holding this deleted / erased table to grow much bigger than it really needs to be, leading eventually to a need to perform a Compact & Repair. This C&R action is found on the File tab of the Access ribbon.

Advice from theDBguy to break the problem up into parts is ALSO good. (It was good enough for Julius Caesar when he took over France.) "Divide and conquer" is ALWAYS a good strategy in any computer problem analysis. If you can find a way to download the file you want, then you can take a separate action on that (now monolithic) file and not get involved in manipulating a web page to get a piece at a time.

IF you are ALWAYS adding data, but what was already there will always be the same in the next cycle, you would do better to follow a path that links the .CSV file, which Access can treat as an external table. Then you could run a query to test whether for a given record you already had that information and not append the record, but if it is new data you could append it as needed. This approach would require that you have a way to test some unique field or unique combination of fields so you could identify the new data. If you can't do that, this problem would take more work.

You said you are a new Access user, so the key words and concepts to look up / study would be "dynamically link at a table" and perhaps the structure of append queries using the INSERT INTO...SELECT...FROM sequence.

Your avatar icon and your name suggest that you are from the Office of Naval Intelligence. Some people on this forum have U.S. Navy experience. I was a contractor for part of SPAWAR before their most recent name change due to the creation of the US Space Force. I retired before the name change.

Thanks for the info!
I always appreciate getting information that allows me to learn and grow more!

A couple things though, the csv we are using is constantly changing names. The program that exports it names it based on date and time with no way to make ot change how it's named.. and that is why I need a file browser otherwise this whole thing would be as easy as just using a static address/location name of a csv.

And I'm just a nerd with a love for Halo. That IS office of naval intelligence but it's fictional. ONI Section 3, a sect of Oni that created Spartans and other advanced technologies haha.

 

[The_Doc_Man]

Ah, Halo.

OK, the issue with the file is that using something called the FileSystemObject (FSO), you can look for a file and rename it. So if you can divide up the export of the file with the later import, you do a Divide and Conquer. Just trying to plant a seed.

 

[Pat Hartman]

See if this example helps you out.

 

[Sec3Op41514]

See if this example helps you out.

Thanks just seen this! will take a look

 

[Mike Krailo]

If the location of the file has many similar files that have different names, you could create a batch file that lists all the filenames only in reverse order by time created. Then select the very first one on the list as the target filename to copy over to a standard name and location that you use for importing.

I would just use the standard dir command with the appropriate options to get that reverse listing. Once you get the bat file working, then you can call it from within Access using the shell execute comnand and go from there with the import.

 

[Mike Krailo]

Find recent file using a script

 

[ebs17]

the csv we are using is constantly changing names

A loop with the Dir statement can use patterns and thus filter out what you want.
You should just be able to put the relevant files in your import folder beforehand.

Sub Import_X()
   Dim sPath As String
    Dim sFile As String
  
    sPath = "X:\Anywhere\Importfiles\"
    sFile = Dir(sPath & "*pattern*.csv")
    Do While sFile > vbNullString
        'Debug.Print sFile

        ' import instructions
        
        ' document import if necessary
        ' delete file or move to an archive to avoid double import
  
        sFile = Dir
    Loop
End Sub

 

[Pat Hartman]

This sample logs the files as they are imported so you can avoid importing them multiple times. When I do this in real applications, I also move the imported files to an archive table to keep the pending import folder clean and avoid visual confusion.