Page 1 of 4 123 ... LastLast
Results 1 to 15 of 54
  1. #1
    Join Date
    Jan 2012
    Posts
    54

    Question Unanswered: Importing Data Form TXT to Access

    Hi to everybody
    I'm copletely a newbie of both Access and VBA
    but still I have to import data stored in a txt to MS Access using compulsory VBA code.
    The file is coded as a Fixed-lenght txt, in which every field has a certain number of characters.

    My firt linecode is something similar to this:
    107CNCLAPPL213220110718
    surfing on web I 've been arragend to write a code to do this importation, but it doest't works. This is the code:
    Function ImportTextFile()
    Dim LineData As String
    Dim CODE1 As Integer ' Holder for Account Number in text file
    Dim COOMPANIE As String ' Holder for the Cusip Number or Ticker Symbol
    Dim TYPEOP As String ' Holder for the number of shares held in account
    Dim MOUNANT As String ' Holder for Long or Short Position
    Dim TYPEMOU As String ' Holder for value of security
    Dim DATE1 As String ' Holder for Net Asset Value
    Dim PRO As String

    ' Open the text file
    Open "C:\Users\gsirico\Documents\prova.txt" For Input As #1
    Do Until EOF(1)
    ' Open the table to insert the text file into
    DoCmd.OpenTable "Table1", acNormal, acEdit

    'Do While Not EOF(1)
    ' Read a line of data.

    Line Input #1, LineData

    COMPANIE = Mid(LineData, 3, 1)
    TYPEOP = Mid(LineData, 4, 4)
    MOUNTANT = Mid(LineData, 8, 4)
    TYPEMOU = Mid(LineData, 12, 4)
    DATE1 = Mid(LineData, 16, 4)
    PRO = Mid(LineData, 20, 4)
    Table1.Fields(1) = COMPANIE
    Table1.Fields(2) = TYPEOP
    Table1.Fields(3) = MOUNTANT
    Table1.Fields(4) = TYPEOP
    Table1.Fields(5) = DATE1
    Table1.Fields(6) = PRO
    Loop

    ' Close the data file.
    End Function
    I'd like to know if thare are correction,suggestion, or likely re-writing for this code!
    Thanx a lot to Everybody

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is the problem you are experiencing?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2012
    Posts
    54
    there's a syntax error, and it seems like the LineData variable is empty(tested it with MSGBOX), but it shouldn't.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    syntax error on which line?

    put in some error handling
    check the length of the string is long enough

    Code:
    if strlen(LineData)>=24 then
      COMPANIE = Mid(LineData, 3, 1)
      TYPEOP = Mid(LineData, 4, 4)
      MOUNTANT = Mid(LineData, 8, 4)
      TYPEMOU = Mid(LineData, 12, 4)
      DATE1 = Mid(LineData, 16, 4)
      PRO = Mid(LineData, 20, 4)
    ' im pretty certain you need to issue an edit command, update the data then save it.
      Table1.Fields(1) = COMPANIE
      Table1.Fields(2) = TYPEOP
      Table1.Fields(3) = MOUNTANT
      Table1.Fields(4) = TYPEOP
      Table1.Fields(5) = DATE1
      Table1.Fields(6) = PRO
    'insert the save command.. forget what it is
    Loop
    change the date column datatype from string to datetime
    chaneg any numeric columns to a suitable numeric value
    validate the date is good
    validate any numerics are as expected.
    check all values are 'sane', ie reasonable

    NEVER EVE$R trust the outside world, never trust a file, even if you wrote the application that generated the file.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as I know, you cannot manipulate the data of a table that you open like this:
    Code:
    DoCmd.OpenTable "Table1", acNormal, acEdit
    You need to open a RecordSet on the table, then as healdem pointed out, you need to use the AddNew method of the recordset, assign values to the fields, then commit the changes with the Update method:
    Code:
    Dim rst as DAO.Recordset
    
    Set rst = CurrentDb.OpenRecordSet("Table1", dbOpenDynaset)
    '
    ' For each line to add to the table:
    '
    With rst
        .AddNew
        !Field1 = Value1
        !Field2 = Value2
        ...
        .Update
    End With
    '
    ' When all lines are added:
    '
    rst.Close
    Set rst = Nothing
    An alternative method consist in issuing SQL INSERT statements to the CurrentDb object:
    Code:
    Dim strSQL as String
    '
    ' For each line to add to the table:
    '
    strSQL = "INSERT INTO Table1 ( Field1, Field2,... ) VALUES ( Value1, Value2,... );"
    CurrentDb.Execute strSQL, dbFailOnError
    Have a nice day!

  6. #6
    Join Date
    Jan 2012
    Posts
    54
    Thank you all for the Answare!!!!!!!
    I'm going to implement your suggestion and correction, I'll let you Know as soon as possible how if it 'll work or not

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  8. #8
    Join Date
    Jan 2012
    Posts
    54
    bad news:
    I'v got an error even if I 've tryed to implement yours suggestions;
    but whatever;
    here is the code:
    ub importtxt()
    Dim rst As DAO.Recordset

    Dim LineData As String
    Dim CODE1 As Integer ' Holder for Account Number in text file
    Dim COOMPANIE As String ' Holder for the Cusip Number or Ticker Symbol
    Dim TYPEOP As String ' Holder for the number of shares held in account
    Dim MOUNANT As String ' Holder for Long or Short Position
    Dim TYPEMOU As String ' Holder for value of security
    Dim DATE1 As String ' Holder for Net Asset Value
    Dim PRO As String

    ' Open the text file
    Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
    Open "C:\Users\gsirico\Documents\prova.txt" For Input As #1
    Do Until EOF(1)


    Line Input #1, LineData
    CODE1 = Left(LineData, 2)
    COMPANIE = Mid(LineData, 3, 1)
    TYPEOP = Mid(LineData, 4, 4)
    MOUNTANT = Mid(LineData, 8, 4)
    TYPEMOU = Mid(LineData, 12, 4)
    DATE1 = Mid(LineData, 16, 4)
    PRO = Mid(LineData, 20, 4)
    With rst
    .AddNew

    Table1.Fields(1) = CODE1
    Table1.Fields(2) = COMPANIE
    Table1.Fields(3) = TYPEOP
    Table1.Fields(4) = MOUNTANT
    Table1.Fields(5) = TYPEOP
    Table1.Fields(6) = DATE1
    Table1.Fields(7) = PRO
    .Update
    End With
    Loop
    ' Close the data file.
    End Sub
    and here is the error :
    object required
    code error 424
    on the yellow line

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That's not what I suggested. You still try to address 'Table1' instead of using the recordset:
    Code:
    Table1.Fields(1) = CODE1
    While you should use:
    Code:
    rst.Fields(1) = CODE1
    See: DAO Recordset or: DAO Object Model: The Definitive Reference: Chapter 8: Recordsets Collection and Recordset Object
    Have a nice day!

  10. #10
    Join Date
    Jan 2012
    Posts
    54
    I'm sorry to have disappointed you; but i'm totally a newbie!!!!!
    what if build a table with all the fields before running this part of the code?
    how can i build that?

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by jsirico View Post
    I'm sorry to have disappointed you; but i'm totally a newbie!!!!!
    You did not: that's your database, not mine

    Quote Originally Posted by jsirico View Post
    what if build a table with all the fields before running this part of the code?
    how can i build that?
    Do you mean that the table does not exist before you try to import the text file into it?
    Have a nice day!

  12. #12
    Join Date
    Jan 2012
    Posts
    54
    yes. indeed I actualy don't have a table, i'd like to have access do it for me!!!!!

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You could create the table as part of the import process, by using SQL, the DDL (Data Definition Language) flavour. such as
    Code:
    CREATE myTable....
    But I suspect if you do you will be tempted to go down a bad physical design tht will lead to problems down the road.

    design your table(s) and columns first
    as said before use the appropriate datatype (numeric types such as integer, long, int for integer values, double or single or currency for decimal; datetime for date of time columns; string for character columns). not using the appropriate datatype means you cannot take advantage of the inbuilt function in Access especially for date time columns

    don't use spaces in column or table names, do use underscore or camelcase
    eg
    an_example_of_an_acceptable_name
    or
    AnExampleOfAnAcceptableName

    don't use any of the reserved words in Access

    do develop a naming convention / strategy so you sue common abbreviations for co0lumn / table names. less important in table names, but column names can quickly exceed the relevant length if you don't use abbreviations (which I think is around 32 characters). choose names that are descriptive and self documenting. a convention is to use ID for numeric primary keys or TYPE/CODE for alphanumeric. but don't use these if there is a natural 'better' name eg use ProductNo (or ProdNo) NOT ID. if you use an autonumber column then ID is a good candidate. but don't use the table name in the column name eg there is no point in declaring a column in table products called ProdID, call it ID, but declare it in other tables as ProdID so its immediately clear that that column refers to the ID in Products...
    eg
    No instead of Number in say PhoneNo, FaxNo
    Desc instead of Description (but note that Desc is a reserved word so it can't be used on its own)
    ..but use your style, there is stuff all point in adopting someone else's "rules" if you don't fully understand them

    but most of all be consistent and develop your own approach which works for you
    Last edited by healdem; 01-06-12 at 07:42.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution:
    Code:
    Option Compare Database
    Option Explicit
    
    Sub CreateTable(ByVal TableName As String, Optional ByVal DropExisting As Boolean)
    
        Const c_SQL = "CREATE TABLE @T (CODE1 Long, COMPANIE Text(1), MOUNTANT Text(4), TYPEOP Text(4), DATE1 Text(4), PRO Text(4));"
        
        Dim tdf As DAO.TableDef
        
        For Each tdf In CurrentDb.TableDefs
            If tdf.Name = TableName Then
                If DropExisting = True Then
                    CurrentDb.Execute "DROP TABLE " & TableName
                Else
                    MsgBox "Table " & TableName & " already exists.", vbInformation, "CreateTable"
                    Exit Sub
                End If
                Exit For
            End If
        Next tdf
        Set tdf = Nothing
        CurrentDb.Execute Replace(c_SQL, "@T", TableName), dbFailOnError
    
    End Sub
    
    Sub ImportTextFile()
    
        Dim strLineData As String
        Dim intHandle As Integer
    
        CreateTable "Table1", True
        intHandle = FreeFile
        Open "C:\Users\gsirico\Documents\prova.txt" For Input As #intHandle
        Do Until EOF(intHandle)
            Line Input #intHandle, strLineData
            strLineData = Trim(strLineData)
            If Len(strLineData) >= 19 Then
                InsertRow "Table1", strLineData
            Else
                MsgBox "The last line read from the file has not the proper format", vbInformation, "ImportTextFile"
            End If
        Loop
        Close #intHandle
        
    End Sub
    
    Sub InsertRow(ByVal TableName As String, ByVal LineData As String)
    
        Const c_SQL As String = "INSERT INTO @T ( CODE1, COMPANIE, TYPEOP, MOUNTANT, DATE1, PRO ) " & _
                                "VALUES ( @C, '@O', '@Y', '@M', '@D', '@P' );"
                                
        Dim strSQL As String
        
        strSQL = Replace(c_SQL, "@T", TableName)
        strSQL = Replace(strSQL, "@C", Left(LineData, 2))
        strSQL = Replace(strSQL, "@O", Mid(LineData, 3, 1))
        strSQL = Replace(strSQL, "@Y", Mid(LineData, 4, 4))
        strSQL = Replace(strSQL, "@M", Mid(LineData, 8, 4))
        strSQL = Replace(strSQL, "@D", Mid(LineData, 16, 4))
        strSQL = Replace(strSQL, "@P", Mid(LineData, 20, 4))
        CurrentDb.Execute strSQL, dbFailOnError
        
    End Sub
    Side note: You should seriously consider enforcing the declaration of variables (in the VBA Editor, open the "Tools" menu and select "Options...". In the "Code Settings" section of the "Editor" tab, check the "Require Variable Declaration" checkbox. This will insert the "Option Explicit" directive in every new module and will spare you countless hours of debugging in the future.
    Have a nice day!

  15. #15
    Join Date
    Jan 2012
    Posts
    54
    thanks! I'm tryng to understed what you have just posted. I don't know how to thank you all!!!!!
    I'll keep you informed!!!!

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
  •