Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Dec 18, 2009

Working with external text files in MS Access - Page 2

By Doug Steele

Working with Schema.ini files

Once you've got the Schema.ini file properly defined in the same folder as the text file, you can then take advantage of what's in it when using text files. Why might you want to do this? One big reason would be that some text files don't have headers in them, so there's no easy way of letting Access know what the column names should be. Since the Schema.ini file contains the field names, your linked table will have correct column names, as opposed to the Field1, Field2, Field3 names Access will assign by default.

There are three distinct ways that you can use the Schema.ini file to read data. They are:

  • You can import the data into a table in your application
  • You can create a linked table that retrieves the text data
  • You can create a query that retrieves the data without having to create a table.

For all of the code snippets that follow, assume that strTable contains the name you want to assign to the table or query you're creating (one of the three options above), strFolder contains the full path to the folder where the text file and Schema.ini file exists (without a terminating slash), and strFile contains the name of the text file.

The following code will create a table (named whatever's contained in strTableName) containing all of the data contained in strFile (in folder strFolder):

Dim strSQL As String

  strSQL = "SELECT * INTO [" & _
    strTable & "] FROM " & _
    "[Text; Database=" & _
    strFolder & ";].[" & _
    Replace(strFile, ".", "#") & "]"
  dbCurr.Execute strSQL, dbFailOnError

The reason for the square brackets on either side of strTable is to handle the case where strTable contains blanks in the name (which I always consider to be a bad idea!). Note that the name of the file gets modified so that the period before the file extension is replaced by an octothorp (#).

Importing the data from a text file into your application is often an appropriate thing to do, but if the contents of the text file can change over time, you may wish to link to it, rather than import. I'm using DAO in the following code to create a linked table (again, named whatever's contained in strTable) that's linked to strFile (in folder strFolder).

Set tdfNew = dbCurr.CreateTableDef(strTable)
tdfNew.Connect = "Text;DATABASE=" & strFolder & _
  ";TABLE=" & strFile
tdfNew.SourceTableName = strFile
dbCurr.TableDefs.Append tdfNew

You also have the option of creating a query that retrieves the current values from the text file, although I'm not sure that there's any advantage to doing so instead of creating a linked table. Again, I'll use DAO to create the query (named whatever's contained in strTable) that returns the data from file strFile in folder strFolder:

Set qdfNew = dbCurr.CreateQueryDef(strTable)
qdfNew.SQL = "SELECT * FROM [Text;Database=" & _
  Me.txtFolder & _
  ";].[" & Replace(strFile, ".", "#") & "]"

(Note that if you're using Access 2000 or Access 2002, your database may be missing the necessary reference to the DAO library, so that neither of the previous two snippets will work. Go into the VB Editor and select Tools | References from the menu. If Microsoft DAO 3.6 Object library isn't one of the entries checked at the top of the list, scroll through the list of available references until you find that reference, select it by clicking on the check box then close the dialog)

The sample database that accompanies this article includes eight text files with it:

  • Cats.BadfileName.txt
  • Cats.txt
  • CSVCategoriesHeader.txt
  • CSVCategoriesNoHeader.txt
  • Employees.txt
  • EmployeesExample1.txt
  • TabCategoriesHeader.txt
  • TabCategoriesHeader_Subset.txt

There’s also a Schema.ini file that contains definitions for each of those eight files.

In the sample database, form frmImportUsingSchema (shown in Figure 9) lets you select one of those eight files and then supply either a table name (if you select Import to Table or Link to Table) or query name (if you select Create linked query). When you click on the Import button, the table (or query) will be created for you, and you'll be able to use the data from the text file.

This form allows you to import or link to a file
Figure 9: Form frmImportUsingSchema - This form allows you to import or link to a file.

Exporting data using the Schema.ini file

Schema.ini files can also be used to output data. The easiest way I've found is to use a simple SQL statement. The following code will create a text file (named whatever is contained in strFile in whatever folder is contained in strFolder) containing all of the data contained in strTable:

Dim strSQL As String

  strSQL = "SELECT * INTO " & _
    "[Text;" & _
    "Database=" & srFolder & ";].[" & _
    strFile & "] " & _
    FROM [" & strTable & "]"
  dbCurr.Execute strSQL, dbFailOnError

Form frmExportUsingSchema (shown in Figure 10) lets you export data from either table Categories or Employees to one of the eight files listed above. The problem you’ll encounter demonstrating how this works though, is that since what’s in the Schema.ini file dictates the name of the file to which the data will be exported and frmImportUsingSchema required that the files exist in order to be able to import or link to them, the files to which you’ll be exporting the data already exist in the folder.

This form allows you to export from a table to a file
Figure 10: Form frmExportUsingSchema - This form allows you to export from a table to a file.

My advice is to backup the files once you’ve played with frmImportUsingSchema so that you can then replace those files via frmExportUsingSchema

Using the Schema.ini file with ADO

It's also possible to create an ADO connection that will let you read data from the text file. The files included with the sample database include djsArticle5_WordExample.doc.

If you open that document in Word 2003, you should see a custom command bar (shown in Figure 11) that will let you invoke the code

Custom command bar
Figure 11: Custom command bar – Clicking on the Import Data button will run the code, clicking on the Open VBEditor button will let you see the code.

Depending on your Macro security settings, you may not be able to run the sample code (which means you've got your system protected, a good thing!). If you get the security warning message shown in Figure 12, you can change your settings temporarily to use the sample. Select Tools | Macro | Security from the menu bar (as illustrated in Figure 13), then set the Macro security level to Medium or Low (as illustrated in Figure 14).

Word 2003 Macro Security warning
Figure 12: Word 2003 Macro Security warning – Word is trying to protect you from running unknown code.

Navigating to the Macro Security settings
Figure 13: Navigating to the Macro Security settings – Remember that Word 2003 hides rarely-used menu options. You might have to search for the Menu option under the Tools menu.

Changing the Macro Security level
Figure 14: Changing the Macro Security level – Changing to Low will allow you to run the code (changing to Medium means you'll get a prompt). Remember to change your settings back when you're done!

If you're using Word 2007, the process will be slightly different. When you open the document, you'll likely see a security warning that macros have been disabled just below the ribbon (see Figure 15). Click on the Options button and enable the contents, as shown in Figure 16.

Word 2007 Macro Security warning
Figure 15: Word 2007 Macro Security warning – Security in all the Office products is stronger than previously.

Enabling the contents in Word 2007
Figure 16: Enabling the contents in Word 2007 – This setting will only apply to the current document.

Once that is done, rather than the command bar shown in Figure 11, a new group (Custom Toolbars) appears under the Add-Ins tab, as shown in Figure 17.

Custom Toolbars in Word 2007
Figure 17: Custom Toolbars in Word 2007 – The toolbars appear as groups in the Add-Ins tab!

To connect to the text file using ADO, you use a connection string along the lines of...

Driver={Microsoft Text Driver (*.txt; *.csv)};
Dbq=C:\Folder\;Extensions=asc,csv,tab,txt; 

...and your SQL simply refers to the file name. Just to show that these techniques can be applied to more than simply Access, I've included a simple Word document along with this article to show how you can use this. I'm assuming that the Schema.ini file and the text file Cats.txt exist in the same folder as the Word document. This code reads the contents of the text file and inserts it into a table in the Word document: To see the macro run, you need to ensure that macros are enabled when you open the document. Once that’s done, you need to run the macro ImportData.

Because I'm going to use Late Binding, I define the ADO constants I want to use, as well as a number of variables:

Const adOpenStatic As Long = 3
Const adLockOptimistic As Long = 3
Const adCmdText As Long = &H1

Dim objConnection As Object
Dim objRecordset As Object
Dim lngColumn As Long
Dim lngRow As Long
Dim lngTableNumber As Long
Dim strConnection As String
Dim strMessage As String
Dim strSchema As String
Dim strSQL As String
Dim strTable As String

As I said, the code assumes that both Schema.ini and Cats.txt exist in the same folder as the Word document, so it's a good idea to check that both assumptions are true:

  strSchema = ActiveDocument.Path & "\Schema.ini"
  strTable = ActiveDocument.Path & "\Cats.txt"

  If Len(Dir(strSchema)) = 0 Then
    strMessage = strMessage & _
      strSchema & " does not exist." & vbCrLf
  End If

  If Len(Dir(strTable)) = 0 Then
    strMessage = strMessage & _
      strTable & " does not exist." & vbCrLf
    End If

  If Len(strMessage) > 0 Then
    MsgBox strMessage & _
      "Cannot proceed with the demonstration.", _
      vbOK + vbCritical
  Else

Since I want to be able to refer to the table I'm going to create, it's necessary to know how many tables are already in the document. Once I've done that, I instantiate the ADO Connection object and open it, then instantiate the ADO Recordset and open it:

  lngTableNumber = ActiveDocument.Tables.Count

  Set objConnection = _
    CreateObject("ADODB.Connection")
  strConnection = _
    "Driver={Microsoft Text Driver " & _
      (*.txt; *.csv)};" & _
      "Dbq=" & ActiveDocument.Path & ";" & _
      "Extensions=asc,csv,tab,txt"
  objConnection.Open strConnection

  Set objRecordset = _
    CreateObject("ADODB.Recordset")
  strSQL = "SELECT * FROM Cats.txt"
  objRecordset.Open strSQL, objConnection, _
    adOpenStatic, adLockOptimistic, adCmdText

Now I simply work with the data in the recordset. First, in order to know how many rows to add for the table, I need to get the number of rows in the recordset. To ensure that the RecordCount property returns the correct value, it's necessary to move to the end of the recordset.

  With objRecordset
    If .BOF = False And .EOF = False Then
      .MoveLast
      .MoveFirst

I add the new table:

  ActiveDocument.Tables.Add _
    Range:=Selection.Range, _
    NumRows:=.RecordCount + 1, _
    NumColumns:=.Fields.Count, _
    DefaultTableBehavior:= _
      wdWord9TableBehavior, _
    AutoFitBehavior:=wdAutoFitContent
  lngTableNumber = lngTableNumber + 1
  lngRow = 1

I set the column headings to the names of the fields. (Note that I'm being lazy and simply setting the font to Bold. Realistically, you should be applying a style, not fiddling with the Font properties).

  For lngColumn = 1 To .Fields.Count
    ActiveDocument.Tables(lngTableNumber). _
      Cell(Row:=lngRow, Column:=lngColumn). _
      Range.Text = .Fields(lngColumn - 1).Name
    ActiveDocument.Tables(lngTableNumber). _
      Cell(Row:=lngRow, Column:=lngColumn). _
      Range.Font.Bold = True
  Next lngColumn

Finally, I loop through the recordset, putting values into every cell of the table:

  Do Until objRecordset.EOF
    lngRow = lngRow + 1
    For lngColumn = 1 To (.Fields.Count)
      If IsNull(.Fields(lngColumn).Value) _
        = False Then
        ActiveDocument.Tables(lngTableNumber). _
          Cell(Row:=lngRow, Column:=lngColumn). _
          Range.Text = _
          .Fields(lngColumn - 1).Value
      End If
    Next lngColumn
    .MoveNext
  Loop

Just a little clean-up, and I'm done:

      End If
    End With
  End If

  objRecordset.Close
  Set objRecordset = Nothing
  objConnection.Close
  Set objConnection = Nothing

Conclusion

We've looked at several different ways in which it's possible to interact with text files using SQL techniques that are familiar to us as database programmers. It's really unfortunate that it's not possible to update tables as well, but unfortunately that's a limitation we have to live with.

» See All Articles by Columnist Doug Steele



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM