Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011

    Red face Unanswered: Import data in a .txt file in to a table in Access 2010


    I was wondering if somebody could help guide me.

    I have produced a module in Access 2010 that imports a specific .txt file from a specific directory.

    This file is just full of text, no commas to indicate columns or column names.

    I have done it so that I am splitting the text up in to different fields, by stating the database table field name, and the values from the text file that will be in them columns.

    I have so far got to the point, where the text file is being opened and so is the table, I can see the data that is going in to the table.

    The module finishes, I go in to the table, and there are 6 empty rows (the .txt file contains 6 rows of text).

    My code is as follows:

    Sub ImportTextFile()
    Dim LineData As String

    Dim Field1 As String
    Dim Field2 As String
    Dim Field3 As String
    Dim Field4 As String
    Dim Field5 As String
    Dim Field6 As String
    Dim Field7 As String
    Dim Field8 As String
    Dim Field9 As String
    Dim Field10 As String
    Dim Field11 As String
    Dim Field12 As String
    Dim Field13 As String
    Dim Field14 As String
    Dim Field15 As String
    Dim Field16 As String
    Dim Field17 As String
    Dim Field18 As String
    Set cncurrent = CurrentProject.Connection
    Set rsDiag = New ADODB.Recordset
    ' Open the text file
    Open "T:\File.txt" For Input As #1
    MsgBox "File is open..."
    ' Open the table to insert the text file into
    strsql = "Select * from tbltestTable"

    MsgBox "Fields: " + strsql

    rsDiag.Open strsql, cncurrent, adOpenDynamic, adLockOptimistic
    MsgBox "Database table is open..."
    Do While Not EOF(1)
    ' Read a line of data.
    Line Input #1, LineData
    Field1 = Left(LineData, 4)
    Field2 = Mid(LineData, 5, 12)
    Field3 = Mid(LineData, 17, 9)
    Field4 = Mid(LineData, 26, 7)
    Field5 = Mid(LineData, 33, 4)
    Field6 = Mid(LineData, 37, 20)
    Field7 = Mid(LineData, 57, 2)
    Field8 = Mid(LineData, 59, 7)
    Field9 = Mid(LineData, 66, 11)
    Field10 = Mid(LineData, 77, 2)
    Field11 = Mid(LineData, 79, 12)
    Field12 = Mid(LineData, 91, 4)
    Field13 = Mid(LineData, 95, 4)
    Field14 = Mid(LineData, 99, 30)
    Field15 = Mid(LineData, 129, 2)
    Field16 = Mid(LineData, 131, 3)
    Field17 = Mid(LineData, 134, 2)
    Field18 = Mid(LineData, 136, 2)

    MsgBox "Data: " + LineData

    'rsDiag!ICDraw = ICDraw
    'rsDiag!Description = ICDDesc

    MsgBox "Import Complete..."

    ' Close the data file.
    Close #1
    End Sub

    Could anyone advise me on what I might be doing wrong or what is actually happening and why I am getting blank rows?

    Many thanks,


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if its an Access question then its best asked in the Access forum

    bear in mind VB is not VBA is not VBS
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    at what point do you think you are assining values to the columns in the recordset?
    I can see you are assiging the value from the input to working varaibles, but not where you then assign those values to the recordset rsdiag

    assuming that field1..field18 are actually the names of your columns (and shame on you if they are, column names should ideally describe the data they hold) you need to prefix them with the name of the recordset object and a fullstop (or for those speaking English as a foreign language across t'pond:- a period)

    rsDiag.Field1 = Left(LineData, 4)
    '''and so on till all columns populatred
    generally speaking its a good idea to validate the data before inserting, its also a good idea to put in some form of error trapping.. look up VBA error trapping
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2011

    Smile Import data in a .txt file in to a table in Access 2010


    Thanks for your reply.

    No, my columns are called different, but for obvious reasons, I kept them as Field1, Field2 etc.

    I got it working, but needed to do rsDiag.Fields("Field1") = mid(LineData, 4, 19) etc.

    Thank you for your help.


Tags for this Thread

Posting Permissions

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