Importing Data Form TXT to Access

jsirico · #1 (**permalink**) 01-05-12, 11:52

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

healdem · #2 (**permalink**) 01-05-12, 12:20

so what is the problem you are experiencing?

jsirico · #3 (**permalink**) 01-05-12, 12:31

there's a syntax error, and it seems like the LineData variable is empty(tested it with MSGBOX), but it shouldn't.

healdem · #4 (**permalink**) 01-05-12, 16:11

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.

Sinndho · #5 (**permalink**) 01-05-12, 16:28

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

jsirico · #6 (**permalink**) 01-06-12, 03:31

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

Sinndho · #7 (**permalink**) 01-06-12, 04:36

You're welcome!

jsirico · #8 (**permalink**) 01-06-12, 04: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

Sinndho · #9 (**permalink**) 01-06-12, 05:19

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

jsirico · #10 (**permalink**) 01-06-12, 05:42

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?

Sinndho · #11 (**permalink**) 01-06-12, 05:48

Quote:

Originally Posted by jsirico

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

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?

jsirico · #12 (**permalink**) 01-06-12, 06:13

yes. indeed I actualy don't have a table, i'd like to have access do it for me!!!!!

healdem · #13 (**permalink**) 01-06-12, 06:36

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

Sinndho · #14 (**permalink**) 01-06-12, 07:23

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.

jsirico · #15 (**permalink**) 01-06-12, 08:16

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!!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech