Import text files to access 2

[clifftech]

When I import a text file into an Access table I need to import it into an existing Access table. Is there a way to also import the text file name into the table's description property so I know the name of the text file that was imported?

I have a feeling I am asking the impossible but I thought I would ask.

 

[lupins46]

Not possible in a single step.
You would have to import to a 'holding' table and then append from that table to the final target, during which you could add to other fields.

 

[hermanlaksko]

You can import into what ever table you like, however a few things to take note of:


1. acImportFixed: The type of import you are making
2. YrImport Specification: This import spec must be made by you via the import wizard before the import can happen correctly. (see access hlp file for this)
3. YrTablename: Must exist in your base (As if you did not know this :-D)
4. YrFile: Is the path and file name ie. C:\YrFolder\YrFilename

DoCmd.TransferText acImportFixed, "YrImport Specification", "YrTablename", YrFile

Works like a charm every time


Herman

They say that crime doesn't pay... does that mean my job is a crime?

 

[willir]

clifftech

Cliff, if you are importing as a one-time only, or very infrequent basis, use the File -> Get External Data (I am sure you already know this).

If this is going to be a routine "thing", then you may want to look at creating some VBA code to automate a lot of the tasks. If so, look at the use of ...
DoCmd.TransferText ...

Richard

 

[JerryKlmns]

The TABLE object of a CATALOG has some properties accessible through the ADOX. Check the following :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adoproperties.asp

and jump to The Properties Collection of the Table Object.

I figure that there is no "Description" property to play with, via code.

But since you know the FILE name you are importing from, the TABLE name you are importing to, why dont you keep those names in a table that is updated on every import.

 

[TomCologne]

It is possible to do both at once. The following code works best with *.txt files that do not vary too much.

If the files change often in content or layout I would put the "Property" part in a separate procedure.

'' *******************************************************************
'' 1. Import *.txt file into Access table
''
'' 2. Edit/add the "Description" property to the table
'' ---------------------------------------------------
'' References:
''
'' Microsoft DAO 3.6 Object Library,
'' Microsoft Scripting Runtime
''
'' *******************************************************************

Sub ImprtProp()
On Error GoTo Err_ImprtProp

   Dim dbs As DAO.Database, tdf As DAO.TableDef, prop As DAO.Property
   Dim strTableName As String, strPropName As String, strPropDescription As String
   Dim strMsg As String, strFile As String, strPath As String
   Dim fs As FileSystemObject, f As Object
   Dim strLine As String
   Dim intLineNo As Integer, intLineLen As Integer

   Const ForReading = 1

   strFile = "file.txt"
   strPath = "C:\Documents and Settings\Administrator\Desktop\"
   strTableName = "tblImport"
   strPropName = "Description"
   strPropDescription = "Import: " & strFile

   Set fs = CreateObject("Scripting.FileSystemObject")
   Set f = fs.OpenTextFile(strPath & strFile, ForReading)
      
   Set dbs = CurrentDb

   Set rst = dbs.OpenRecordset(strTableName)
    
        With rst
        
            ' Start reading the file
            Do While f.AtEndOfStream <> True
            
                strLine = Trim(f.ReadLine)
                intLineLen = Len(strLine)
                intLineNo = f.Line
                    
                    ' Specify criteria here,
                    ' eg no import of empty lines
                    
                    If intLineLen > 0 Then
                    
                        .AddNew
                        
                         !ImpText = strLine
                         !ImpNo = intLineNo
                        
                        .Update
                    
                    End If
    
            Loop
            
        End With
        
   rst.Close
   f.Close
   
   ' And now for something completely different:
   
   Set tdf = dbs.TableDefs(strTableName)
   
   ' Check setting: Tools>Options>General>Error Trapping
   ' It should not be set to break on all errors because the
   ' next line will cause an error if the property does not exist!
   
   tdf.Properties(strPropName) = strPropDescription
   On Error GoTo 0
    
Exit_ImprtProp:
       
    Set dbs = Nothing
    Set rst = Nothing
    Set tdf = Nothing
    Set prop = Nothing
    Set fs = Nothing
    Set f = Nothing
    
    Exit Sub

Err_ImprtProp:

    ' Error 3270 = Property not found.
    If DBEngine.Errors(0).Number = 3270 Then
        
        ' User information, possibly input
        
        
         strMsg = Err.Number & vbCrLf & _
                  String(Len(Err.Description), "-") & vbCrLf & _
                  Err.Description & vbCrLf & _
                  String(Len(Err.Description), "-") & vbCrLf & _
                  Err.Source

                    MsgBox strMsg, vbOKOnly, "Error"

                    ' Create property, set its value, and append it to the Properties collection.
                    ' Note: The new property setting may not be visible right away,
                    ' press F5 key in database window
                    
                    Set prop = tdf.CreateProperty(strPropName, 10, strPropDescription)

                    tdf.Properties.Append prop
                    
                    Resume Exit_ImprtProp

    Else
                    ' Other errors

                    MsgBox Err.Description

                    Resume Exit_ImprtProp
    End If


End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''

TomCologne