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

nic311 · #1 (**permalink**) 01-26-12, 11:07

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!

Sinndho · #2 (**permalink**) 01-26-12, 13:11

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

nic311 · #3 (**permalink**) 01-27-12, 11:20

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.

Sinndho · #4 (**permalink**) 01-27-12, 12:20

Try something like:

Code:

Private Sub Command_StartImport_Click()

    ImportFromCSV Me.TextBox_PathToFile.Value

End Sub

nic311 · #5 (**permalink**) 01-27-12, 13: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?

Sinndho · #6 (**permalink**) 01-27-12, 15:24

You could use something like:

Code:

!YourNameOfColumnOneGoesHere = IIf(Len(varLine(0)) > 0, varLine(0), "n/a")

nic311 · #7 (**permalink**) 01-30-12, 08:36

Perfect, thanks. Will this same code work for a filed saved with a .csv extension, or is this only for .txt files?

Sinndho · #8 (**permalink**) 01-30-12, 13:23

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, ",")

nic311 · #9 (**permalink**) 02-01-12, 08:22

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

Sinndho · #10 (**permalink**) 02-01-12, 08:43

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

nic311 · #11 (**permalink**) 02-01-12, 09:05

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.