Consulting

Results 1 to 4 of 4

Thread: CSV File Import

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Aug 2018
    Posts
    4
    Location

    Question CSV File Import

    Hello, I feel like this is a relatively easy answer, but no amount of searching has lead me to the answer.

    Private Sub cmdSelectFolder_Click()
    Dim fDialog As FileDialog, result As Integer
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    fDialog.AllowMultiSelect = False
    fDialog.Title = "Select folder"
    If fDialog.Show = -1 Then
       Debug.Print fDialog.SelectedItems(1)
    End If
    ep = fDialog.SelectedItems(1)
        Me.DirectoryName.Value = ep
    End Sub
    
    -----
    
    Private Sub cmdImport_Click()
    directory = DirectoryName
    FileName = "import.csv"
    Set rs = CreateObject("ADODB.Recordset")
    strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & directory & ";" _
    & "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
    strSQL = "SELECT * FROM " & FileName
    rs.Open strSQL, strconn, 3, 3
    rs.MoveFirst
    Do
       frmBook.FIRSTNAME.Value = rs("FirstName")
       frmBook.LASTNAME.Value = rs("LastName")
       frmBook.MIDDLENAME.Value = rs("MiddleName")
       End If
       rs.MoveNext
    Loop Until rs.EOF
    Unload Me
    End Sub
    I have the above listed code, which works. Ultimately the user selects the folder where "import.csv" is located and the data is imported. I would like to change this so the user can select the file, in cases where the user does not name the file "import.csv". I am confused as to how I would make the bold part above pull out the file name when the file is selected. I have my cmdSelectFile built already (listed below).

    Private Sub cmdSelectFile_Click()
      With Application.FileDialog(msoFileDialogFilePicker)
            .AllowMultiSelect = False 'Single File Select
            .Filters.Add "CSV Files", "*.csv", 1 'CSV Files Only
            .Show 'Show File Select
            Me.FileName.Value = .SelectedItems.Item(1) 'Store Path in Field
        End With
    End Sub
    Thank you in advanced for any help given.

  2. #2
    VBAX Newbie
    Joined
    Aug 2018
    Posts
    4
    Location

    Lightbulb CSV File Import - Solution

    Well, I tinkered long enough and found my solution. I modified cmdImport_Click() to the following. fileLocation is now the name of the text box where I am passing the file name and full path to. The cmdImport function then parses the name and directory out.

    Private Sub cmdImport_Click()
    
    
    Dim filesystem As Object
    Set filesystem = CreateObject("Scripting.FilesystemObject")
    fileDirectory = filesystem.GetParentFolderName(fileLocation) & "\"
    Dim fileName As String
    fileName = filesystem.GetFileName(fileLocation)
    
    
    Set rs = CreateObject("ADODB.Recordset")
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileDirectory & ";" _
    & "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
    strSQL = "SELECT * FROM [" & fileName & "] "
    rs.Open strSQL, strConn, 3, 3
    rs.MoveFirst
    Do
       frmBook.FIRSTNAME.Value = rs("FirstName")
       frmBook.LASTNAME.Value = rs("LastName")
       frmBook.MIDDLENAME.Value = rs("MiddleName")
       End If
       rs.MoveNext
    Loop Until rs.EOF
    Unload Me
    End Sub

  3. #3
    I have to say I'm thankful for all these answers as it help me to solve a similar issue that I faced.

  4. #4
    Banned VBAX Newbie Ireul's Avatar
    Joined
    Mar 2021
    Posts
    2
    Location
    That's actually true and I also realized how to do it myself. Most people consider relatable easy to deal with CSV files but a lot of times I find myself in trouble working through different functions on my site. I still use CSV files to collect data and place them in order and categorize them as well. That way I'm never lost on any work that I'm doing and when I was starting with them, I didn't even know how to open a csv file correctly for every different function that I wanted to do. For me opening them as a .txt file allows me to copy data easier.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •