Text Files

Importing and exporting to text files is quite common, because this is really the one method that nearly all systems support. There are two types of text files that you might have to deal with:

• Fixed width - where the columns of data are aligned to a specific width. With this format you have to know in advance how wide each column is.

• Comma separated values (CSV) - (also known as delimited text files), where the columns are not aligned, but placed next to each other. Each column is separated from the next by a special character. Traditionally this separator character is a comma, but if you're using something else you'll need to tell Access what it is.

  Important 

  You may have heard of something called XML (Extensible Markup Language). This is also a text-based format for exchanging data and one that is fast replacing CSV and fixed-width files as the best way to move data between different systems. We will look at Exporting XML and the ExportXML method after this section, and then later in the book, during the Internet chapter, we'll look at XML in a bit more depth.

To export to fixed-width or CSV files, you use the TransferText command:

 DoCmd.TransferText [TransferType], [SpecificationName],   TableName, FileName, [HasFieldNames],  [HTMLTableName], [CodePage] 

The arguments are fairly obvious, but let's look at them anyway:

Argument	Desciption
TransferType	The type of transfer to be performed. This must be one of: acExportDelim acExportFixed acExportHTML acExportMerge acImportDelim acImportFixed acImportHTML acLinkDelim acLinkFixed acLinkHTML If you leave this blank, the default of acImportDelim is used. Only acImportDelim , acImportFixed , acExportDelim , acExportFixed , or acExportMerge transfer types are supported in a Microsoft Access project ( .adp ).
SpecificationName	The name of the specification. This is required for fixed-width transfers, but isn't always necessary for CSV files. If you are using a CSV file and leave this out then the default values are used. We'll look at specifications next.
TableName	The table or query name to be exported, or the table name to import or link.
FileName	The full name, including the path , of the file to export to or import or link from. If this file does not exist before you use the TransferText command, it will be created automatically. However if it does already exist, then it will be overwritten without warning.
HasFieldNames	When importing or linking, you can set this to True if the source of the data has field names as the first row. The default of False is taken if you leave this argument empty, which assumes that the first row contains data.
HTMLTableName	The name of the table or list in the HTML document that you wish to import or link from. This is determined by the < caption > tag, or by the < title > tag. This argument is ignored unless acImportHTML or acLinkHTML are being used.
CodePage	A number that indicates the character set of the text file. A quick way to determine the number you need for this parameter, is to create a new macro, select the Transfer Text Action, then use the drop-down list for the CodePage property - it will show the number and a text description. It is just the number that you need.

Specifications

When dealing with fixed-width files, or with CSV files with a separator other than a comma, you need to create a specification to tell Access how the text file is structured. This could have all been set by arguments in the TransferText method, but then it would have many more arguments and would be far harder to read. To make things easier, you can create a specification including all of these details. In early versions of Access this had its own menu item, but now it's buried within the import/export area, in a very unfriendly manner. Here's how to do it:

Try It Out-Creating Specifications

1. Make sure you are in the Tables view of the database window, and select tblCompany .

2. From the File menu select Export .

3. On the Export dialog, select Text Files in the Save as type combo box at the bottom of the screen:

   

   Don't worry about the file name, since you're not actually going to export anything, (although if the name in here already exists you will be asked if you want to overwrite it).

4. Click the Save button. This will start the Export Text wizard. Don't run through any of the wizard.

5. Click the Advanced button. This will show the specification box:

   

6. The above picture shows the view for CSV files. The two important items here are the Field Delimiter and Text Qualifier .

   By way of explanation, look at this example text file:

    1,"Fran and Nick's Fruit and Nuts","37 Walnut Grove Nutbush","Tennessee","38053","USA","[phone]","[phone]",,,,1 2,"Candy's Cones","26 Wafer Street Redwood City","California","94056","USA","[phone]","[phone]",,,,1 

   The Field Delimiter is what the fields will be separated by (it's usually a comma, as it is here). The Text Qualifier is what is placed around text fields, and is usually double quotation marks. Anything within these marks is treated as a single field. This means that a field can contain the field delimiter character without causing confusion. For example, if one of our address fields contained a comma, enclosing the field in quotes would mean that the comma in the field wasn't treated as the end of the field.

7. Save this specification by clicking the Save As button. Give it a name of CompanyDelimited .

8. Now change from a delimited file format to a fixed-width format, by selecting the appropriate radio button for the File Format at the top of the screen. Notice that the Field Information section has changed to show the start and width of each column:

   

9. Click the Save As button once more, and save this as CompanyFixed , in the BegAccessVBA2002 directory that you've previously used.

10. Press the OK button to return to the Export Text wizard, and then click on Cancel to close it. This returns you to the database window.

So those are the two types of specification. Let's have a look and see what sort of results we get when importing and exporting data with them.

Try It Out-Export to a Fixed-Width File

1. Open up frmImportExport in design view.

2. Add another button, calling it cmdExportFixed , and give it a caption of Export Company Fixed .

3. In the Click event of the button, add the following code:

      DoCmd.TransferText acExportFixed, "CompanyFixed", "tblCompany", _     "C:\BegAccessVBA2002\CompanyFixed.txt", True     MsgBox "Company details exported"   

4. Flip back to Access, switch the form into Form view, and click the Export Company Fixed button.

5. When the export has finished, take a look at the file it's created:

   

Notice that the first column, CompanyID , is 11 characters wide, as laid out in the specification. Also note that the second column contains no quotes around it, even though it's a text field. That's because we've specified the length of the field, so there's no need to contain text fields in quotes.

You'll also notice something very odd here. Our line of code set the HasFieldNames argument to True , and you can see that the first line it has generated does indeed contain the field names. But (and this is the weird part), the fixed width format isn't used for these field names. We can only assume that the rationale behind this is that the field names themselves don't really fit as part of the specification, and so are output in the CSV default format.

Try It Out-Importing from a Fixed Width File

1. In Access, create a new button on our import and export form. Call this one cmdImportFixed , and caption it Import Company Fixed.

2. Add the following code to the Click event:

      DoCmd.TransferText acImportFixed, "CompanyFixed", "tblCompanyFixed", _     "C:\BegAccessVBA2002\CompanyFixed.txt", True     MsgBox "Company details imported"   

3. Flip back to Access, switch the form into Form mode and press the new import button. This will import the file you've just exported into a new table.

If you look at the list of tables you'll see two new ones - tblCompanyFixed and CompanyFixed_ImportErrors . The first is the new table you imported, and the second is automatically created by Access because there were some import errors. If you open the errors table, you'll see what the problem is:

Yeah, right. That's really useful. What this means is that Access has tried to add some data from the text file into the CompanyID field, but it was not the correct format. But hold on. Hasn't Access just exported this data - how can it be wrong? The answer lies in the record for Flavors Of The World :

Notice that the address field appears on two lines - it has a carriage return character in it. This isn't a problem in Access, but think about how the text file handles this - the carriage return indicates a new line. With fixed-width text files, the import routine assumes that each new line is a new record. So when it imports this text file you get an error, as it is expecting a number (the CompanyID ) at the beginning of the line, not part of the address. That's why we've got a Type Conversion Failure - Access is trying to convert a string into a number. If you open the newly imported table, tblCompanyFixed , you'll see what Access has done:

Notice that there is a record at the top without a CompanyID field - this is the extra record caused by the carriage return in the address field. The record for Flavors Of The World has incorrect information for its address fields, as does the record for Lloyds Luscious Lollies . That's because the record count is now incorrect and Access has difficulty catching up.

This is pretty disastrous, as it might mean a lot of data becomes corrupted. The way to solve this problem is to use delimited files with a text qualifier, as these import and export correctly. That's because the text qualifier marks the start and the end of a text field, so the carriage return is taken to be part of the field because it is within the quotes.

Try It Out-Export to a Comma-Separated File

1. Add another button to your form. Call it cmdExportSeparated and caption it Export Company Separated .

2. Add the following code to the Click event for this button:

      DoCmd.TransferText acExportDelim, "CompanyDelimited", "tblCompany", _     "C:\BegAccessVBA2002\CompanyDelimited.txt", True     MsgBox "Company details exported"   

3. Back in Access, click this button.

4. When done, have a look at the file it's produced:

   

As you can see, now everything is in the same format as the header line. The comma is used to delimit the fields, and quotes are used around all text fields.

Try It Out-Importing from a Comma-Separated File

1. OK, add the last in our little line of buttons . This time call it cmdImportDelimited and caption it Import Company Delimited .

2. Add the following code to the Click event:

      DoCmd.TransferText acImportDelim, "CompanyDelimited", "tblCompanyDelimited", _     "C:\BegAccessVBA2002\CompanyDelimited.txt", True     MsgBox "Company details imported"   

3. Now, back in Access, click this button.

4. When it's done, have a look at the database window:

   

You now have a new table, containing exactly the same details as the company table. Although we used the standard layout with a comma, just changing the specification can let us easily use other characters. (In fact, because we used the default values, we could have quite easily omitted the specification).

A Couple of Gotchas to be aware of when Importing from Text files

If the table that you import into does not already exist, then it will be created automatically. This is fine. However if you run the import again, when the table does exist, the data will be appended to the existing data, and you will get duplicate records. This would not usually be what you or you users want, so make sure that once they have performed the import, they are protected from immediately doing it again by mistake (by disabling the import button, perhaps, or showing a different form).

But wait, you say, I have a primary key in the table, so duplicate records will not be accepted! Which brings me on to my next point: the text files cannot contain any detailed information about the structure of your tables - so information such as primary keys, the size of a text field, or indexes are lost during the export/import you performed above. Take a look at the text sizes for the CompanyName field for example in both the tblCompany and the tblCompanyDelimited tables by opening them up in design view. You will see that the original table had a text size of 50, whereas the imported table has 255 - Access had to take a guess during the import and 255 is the best it could do. Probably of more concern though is the CompanyID field, this was an AutoNumber , but now it is just a plain old Long Integer, and it has lost its status as a primary key. This could of course have ramifications for the rest of system.