Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2011
    Posts
    57

    Unanswered: Importing from txt file to Table 1 and autofilling Table 2 based on this

    Hi,

    I want to import a delimited text file (with a comma as the delimiter) to an already existing table in Access. I've searched and found the docmd.transfertext, but I don't believe this will suit my needs as the text file does not have a header with the field names. I would like to do this using vba and having the code run at the click of a button.
    Once this data is imported, I would like for vba to run through each new line added on the last import, and add it to an additional table.

    Table 1 Fields:
    Contract #
    Contract Segment
    Book Date
    Client

    Table 2:
    Book Date
    Kick off Meeting
    First Milestone
    Second Milestone
    etc...

    So here, the book date from table 1 (which came from the text file) would get added to table 2, and then the rest of the fields in table 2 would be calculated based on this book date.

    I have successfully done this through the use of multiple forms, but this only adds one contract at a time and I'm not sure how to deal with the case of having multiple contracts being added at once from a text file.

    I would greatly appreciate any help on this as I am a little bit stuck on where to look next.

    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Basically, you use a loop such as:
    Code:
    Sub ImportFromCSV(ByVal FileName As String)
    
        Dim intHandle As Integer
        Dim rst As DAO.Recordset
        Dim strLine As String
        Dim varLine As Variant
        
        intHandle = FreeFile
        Open FileName For Input As #intHandle
        Set rst = CurrentDb.OpenRecordset("YourTableNameGoesHere", dbOpenDynaset)
        With rst
            Do Until EOF(intHandle)
                Line Input #intHandle, strLine
                varLine = Split(strLine, ",")
                .AddNew
                !YourNameOfColumnOneGoesHere = varLine(0)
                !YourNameOfColumnTwoGoesHere = varLine(1)
                !YourNameOfColumnThreeGoesHere = varLine(2)
                --- etc.
                .Update
            Loop
            .Close
        End With
        Close #intHandle
        Set rst = Nothing
            
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Posts
    57
    Ok I tried this and I have a few questions

    I set the Filename equal to a text box value on the form that I'm using, is this right? This would be where the user would enter the path name to the file needed.

    Also, how do I link this to a button click? I tried doing call importfromcsv() but it gave me a Compile error: Argument not optional ... I'm not sure where this is coming from.

    Thanks for the help! This has been a good start and once I get this figured out I think I can get it to autofill the other tables pretty easily by just creating a separate recordset for the other table.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try something like:
    Code:
    Private Sub Command_StartImport_Click()
    
        ImportFromCSV Me.TextBox_PathToFile.Value
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Nov 2011
    Posts
    57
    Awesome, that worked great!! I also had to add a function for Split() since I'm using Access 97 and there apparently is no built in split function.
    The only issue I'm having now is that sometimes one of the values (book date) may be left blank initially. The code is giving me a data type conversion error which I believe comes from this issue since all the lines prior to the line that has that are getting imported just fine. This field has data type date, so I can't simply add an n/a to the text file... any suggestions on how to get it to accept a null value for a field?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use something like:
    Code:
    !YourNameOfColumnOneGoesHere = IIf(Len(varLine(0)) > 0, varLine(0), "n/a")
    Have a nice day!

  7. #7
    Join Date
    Nov 2011
    Posts
    57
    Perfect, thanks. Will this same code work for a filed saved with a .csv extension, or is this only for .txt files?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The extension does not matter, provided that the format is right. When you pass the parameter "FileName" to the procedure "ImportFromCSV" you must incluse the full path, the name and the extension of the file you want to import.

    If the field seperator in the csv file is different from the comma (,), simply change this line accordingly:
    Code:
    varLine = Split(strLine, ",")
    Have a nice day!

  9. #9
    Join Date
    Nov 2011
    Posts
    57
    One more question... since I'm trying to fill multiple tables, I create two separate recordsets. However, when it gets to the second recordset it's giving me an error on the
    Do Until EOF(intHandle)

    I tried doing Close #intHandle and then resetting intHandle = FreeFile before setting the second recordset and populating it, however this does not work. (Gives a "Bad file name or number" error)
    Before when I had not first closed #intHandle, it was giving me an "Object invalid" error... Is there a way to reuse that variable to go through the .csv file for a second time?

    Thanks

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Without seeing the modifiied version of the code you used, I cannot provide a precise answer about what went wrong.

    Anyway, the most standard manner to solve the problem would consist in calling a slightly modified version of the function "ImportFromCSV" twice, once for each table:
    Code:
    Sub ImportFromCSV(ByVal FileName As String, ByVal TableName As String)
    
        Dim intHandle As Integer
        Dim rst As DAO.Recordset
        Dim strLine As String
        Dim varLine As Variant
        
        intHandle = FreeFile
        Open FileName For Input As #intHandle
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenDynaset)
        With rst
            Do Until EOF(intHandle)
                Line Input #intHandle, strLine
                varLine = Split(strLine, ",")
                .AddNew
                !YourNameOfColumnOneGoesHere = varLine(0)
                !YourNameOfColumnTwoGoesHere = varLine(1)
                !YourNameOfColumnThreeGoesHere = varLine(2)
                --- etc.
                .Update
            Loop
            .Close
        End With
        Close #intHandle
        Set rst = Nothing
            
    End Sub
    If the names of the columns vary from one table to the other, you can address them using their ordinal position instead of their name:
    Code:
                varLine = Split(strLine, ",")
                .AddNew
                .Fields(0).Value = varLine(0)
                .Fields(1).Value = varLine(1)
                .Fields(2).Value = varLine(2)
                --- etc.
                .Update
    Have a nice day!

  11. #11
    Join Date
    Nov 2011
    Posts
    57
    Code:
    Private Sub ImportFromCSV(ByVal Filename As String)
    
    Dim intHandle As Integer
    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim rst_dates As DAO.Recordset
    Dim rst_complete As DAO.Recordset
    Dim strLine As String
    Dim varLine As Variant
    Dim ImportCriteria As String
    Dim ion As Date
    Dim bookdate As Date
    Dim entrydate As Date
    
    
    intHandle = FreeFile
    Filename = txt_filename.Value
    Set dbs = CurrentDb()
    
    Open Filename For Input As #intHandle
    
    Set rst_dates = dbs.OpenRecordset("MilestoneDatesTbl", dbOpenDynaset)
    
    ImportCriteria = "SELECT LAST (CtNumber) FROM MilestoneDatesTbl"
    
    With rst_dates
        Do Until EOF(intHandle)
            Line Input #intHandle, strLine
            varLine = MySplit(strLine, ",")
            
        If varLine(0) > ImportCriteria And varLine(11) = "Unit" Or varLine(11) = "LOA-Unit" Or varLine(11) = "ION-Unit" Then
    
      If IsDate(varLine(2)) Then
                ion = IIf(Len(varLine(2)) > 0, varLine(2), Null)
            Else
                ion = Empty
            End If
            
            If IsDate(varLine(19)) Then
                bookdate = IIf(Len(varLine(19)) > 0, varLine(19), Null)
            Else
                bookdate = Empty
            End If
            
              If IsDate(varLine(3)) Then
                entrydate = IIf(Len(varLine(3)) > 0, varLine(3), Null)
            Else
                entrydate = Empty
            End If
            
            If IsDate(varLine(22)) Then
                shipdate = IIf(Len(varLine(22)) > 0, varLine(22), Null)
            
        .AddNew
            !CtNumber = varLine(0)
            !Client = IIf(Len(varLine(8)) > 0, varLine(8), Null)
            !PM = IIf(Len(varLine(12)) > 0, varLine(12), Null)
            !ProductType = IIf(Len(varLine(16)) > 0, varLine(16), Null)
            !bookdate = bookdate
            !IonDate = ion
            !ProjectEntryDate = entrydate
            !EndUser = IIf(Len(varLine(9)) > 0, varLine(9), Null)
            !KickoffMtg = entrydate + 7
            !BUP = bookdate + 14
     End If
        .Update
        End If
        Loop
        .Close
    End With
    
    Set rst_complete = dbs.OpenRecordset("MilestoneCompletionTbl", dbOpenDynaset)
    
    
    With rst_complete
        Do Until EOF(intHandle)
        Line Input #intHandle, strLine
            varLine = MySplit(strLine, ",")
         'If varLine(0) > ImportCriteria And varLine(11) = "Unit" Or varLine(11) = "LOA-Unit" Or varLine(11) = "ION-Unit" Then
        If varLine(11) = "Unit" Or varLine(11) = "LOA-Unit" Or varLine(11) = "ION-Unit" Then
             .AddNew
                !CtNumber = varLine(0)
                !Client = IIf(Len(varLine(8)) > 0, varLine(8), Null)
                !PM = IIf(Len(varLine(12)) > 0, varLine(12), Null)
            .Update
        End If
        Loop
            .Close
    End With
    
    Close #intHandle
    dbs.Close
    
    End Sub
    The error comes in on the bolded line : Bad file name or number. I tried doing as you suggested but still got the same error when it got to that same line in the new sub.

    Another question I had... why will VBA not let me set the variables entrydate and bookdate = null? It allows it if I set it up in an entrydate = iif(len(varline(x)>0, varline(x), null) but it won't let me just go entrydate = null.

  12. #12
    Join Date
    Nov 2011
    Posts
    57
    I'm still stuck on figuring out how to get this to work... can anyone offer any suggestions? Thank you

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You must close the file handle before opening another file (or reopening the same), you cannot reuse it directly:
    Code:
    Close #intHandle
    Have a nice day!

Posting Permissions

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