Importing and Exporting Text File Data in VBA Code

The main menu of the sample application, External Data.accdb (shown in Figure 10.10), has buttons for opening various forms that import and export data in a variety of file formats. Writing VBA code to do the imports and exports gives you one-click convenience, particularly useful if you have to do an import or export task frequently, for example importing weekly Jobs data downloaded from a mainframe computer or emailed as a fixed-width or comma-delimited text file.

FIGURE 10.10

The main menu of the External Data sample database.

FIGURE 10.10

The main menu of the External Data sample database.

Importing Text Data

The following steps illustrate how to import data from a text file into an Access table, using VBA code running from controls on an Access form (frmlmportTextData, illustrated in Figure 10.11):

1. Selecting the "Import Data from Text Files" option and clicking the button to its left opens the Import Job Data from Text File (frmlmportTextData) form, as shown in Figure 10.11.

If a text file was previously selected, its name is displayed in the textbox to the right of the "Source Text File" button.

2. You can use the selected file (if one is listed), or you can select another by clicking the "Source Text File" button, which opens a File Picker dialog box. The File Picker dialog box is filtered to display either comma-delimited (.csv) or fixed-width (.txt) files, according to the option selected in the Import Text Type option group. In Figure 10.12, I've selected a comma-delimited text file for import.

The Import Job Data from Text File form, as initially opened.

S frmlmportData _ n x

[jj Import Job Data from Text File

Jobs to Inspect:

1 J Source Text File | |

Import Text Type

® Comma-delimited O Fixed-width Clear Imported Jobs Data Save New Jobs to Table

FIGURE 10.12

Selecting a comma-delimited text file for import.

Select text file with job data to import

teal

| « Job Files to Import

" 1 1 1 Seanh

% Organize » j|| Views ~ R New Folder

Date modified 8/Î/2006 2:58 PM 8/2/2006 2:13 PM 8/2/2006 2:13 PM 8/2/2006 2:12 PM

Type

Microsoft Office E... Microsoft Office E... Microsoft Office E... Microsoft Office E...

Favorite Links ^ Default Templates jj Documents

Custom Templates Jli Knitting Patterns

Recent Places H Desktop flip Computer J4 Download Jt Unzip

Office Book More »

Foiders

Name

Filtered ßy Jobs 02-Jobs 03 Jobs 06-Jobs 09-^ Jobs 13-"i^Jobs 16-Jobs 20-ßfcjjobs 23-^ Jobs 27-Jobs 30-

Jobs.csv -Jul-2006.csw Sep2006,csv Au g-2006,csv J u 1-200" •Aug-20 Jut-200

Date modified 8/Î/2006 2:58 PM 8/2/2006 2:13 PM 8/2/2006 2:13 PM 8/2/2006 2:12 PM

Type

Microsoft Office E... Microsoft Office E... Microsoft Office E... Microsoft Office E...

Type: Microsoft Office Excel Comma Separated Values File Size: 542 bytes

Date modified: 8/2/2006 2:12 PM

■Aug-2006,csv Jul-2006,csv Aug-2006,csv J u [-2006.csv

8/2/2006 2:12 PM 8/2/2006 2:13 PM 8/2/2006 2:13 PM 8/2/2006 2:13 PM

Microsoft Office E... Microsoft Office E... Microsoft Office E... Microsoft Office E...

Comma-delimited files {".csv} T [ Open ] [ Cancel

3. After selecting a text file, click the "Inspect New Jobs from Text File" button to import data from the text file to a temporary table, tblNewJobs.

The code running from this button sets the table as the source object of the Jobs to Inspect subform, so you can see the new records before actually appending them to the tblJobs table, as shown in Figure 10.13.

FIGURE 10.13

The new jobs imported from a comma-delimited text file.

fi mini poitTexf Data

[B Import Job Data from Text File

Product Number •

Job Date/Time

- Factory Name

Production Line

Batch Quantity •

F RB-2250899- A

8/21/2006 2:49:00 PM

Nutley

156

12546.9

FRB-1013980-A

8/22/2006 9:37:00 AM

Simms

156

5421.35

FRB-2250039-C

8/25/2006 2:24:00 PM

Nutley

225

2595.25

F RB-1013975-A

8/25/2006 2:43:00 PM

Nutley

114

256.99

FRB-1013979-A

8/25/2006 2:43:00 PM

Amberson

143

2451.18

FRB-2250911-A

8/27/20064:27:00 PM

Oakdale

156

895.36

FRB-1012712-C

7/31/2006 S:23:00 PM

Simms

156

1144.22

FRB-1013943-A

8/1/2006 2:39:00 PM

Nutley

120

234.22

FRB-1013947-A

8/1/2006 2:43:00 PM

Amberson

225

837.22

FRB-225091S-A

8/2/2006 2:44:00 PM

Simms

201

1254.9

FRB-1013955-A

8/2/2006 4:23:00 PM

Amberson

201

3744.28

FRB-2250Q89-B

8/3/2006 6:23:00 PM

Amberson

225

7S8.3

FRB-2250564-E

8/4/20064:43:00 PM

Nutley

201

254.19

FRB-2250050-B

8/6/2006 9:22:00 AM

Oakdale

114

215.17 w

Re

cord: M ¡lof22 |

H H 1 i, . : . r,|Cç4 llsearch

|

[ Source Text File | D:\Documents\JobFilesto lmport\Jobs06-Aug-2006.cs

Import Text Type (â) Comma-delimited ~ Fixed-width

Inspect New Jobs from Text File Clear Imported Jobs Data

Save New Jobs to Table

4. After inspecting the new data, you can either discard the data or import it into the tblJobs table.

Clicking the "Clear Imported Jobs Data" button discards the data (it is not added to tblJobs).

Clicking the "Save New Jobs to Table" button runs an append query that adds the new Jobs data to tblJobs. The code does some data type conversion (as shown in Figure 10.14), because all the fields in the text file are text fields.

An append query that does some data type conversion before appending the newly imported jobs to the main tblJobs table.

jE" qappNewJobs

JobDateTlme

FactoryMame

ProdudionLine

BatchQuantity

Field:

JobDT; CDate([JobDateTime])

Facto ryName

Pro dLine: CStr([Pro du cti o n Li n e]j

BatchQuan: CDbl([BatchQuantity]) ^

Table:

tbINewJobs

Sort

Append To:

JobDateTime

Facto ryName

ProductionLine

BatchQuantity

Criteria:

< H ►

If you clicked the "Save New Jobs to Table" button, the new records that were displayed on the form were added to tblJobs.

The relevant procedures from the form code module are listed next. The "Clear Imported Jobs" command buttons event procedure clears the datasheet, calls the SaveTextFile Sub procedure, and clears the previously selected file name from the textbox:

Private Sub cmdClearData_Click()

On Error Resume Next

Me![subNewJobs].SourceObject = "" Call SaveTextFile("") Me![txtSelectedTextFile].Value = ""

End Sub

Importing data from fixed-width text files has become much more difficult in Access 2007 than in previous versions of Access. Specifications now store the file name internally, and thus you must create a separate specification for each text file you want to create. This means that the flexible method I use, where you can select the file to import, will not work. I recommend sticking to comma-delimited file imports, if possible.

The "Inspect New Jobs from Text File" command buttons event procedure first checks that a text file has been selected and then sets variables with the name of the target table for importing and the import type. Next, a Select Case statement handles the two types of import (comma-delimited and fixed-width) separately, using the TransferText statement with different arguments:

Private Sub cmdInspectJobs_Click()

On Error GoTo ErrorHandler

Dim strText As String

Dim strTitle As String

Dim strPrompt As String

Dim txtData As Access.TextBox

Dim strTextFile As String

Dim strTable As String

Dim strSpec As String

Set txtData = Me![txtSelectedTextFile] strTextFile = Nz(txtData.Value) If strTextFile = "" Then strTitle = "No text file selected"

strPrompt = "Please select a text file"

MsgBox prompt:=strPrompt, Buttons:=vbExclamation _

+ vbOKOnly, title:=strTitle GoTo ErrorHandlerExit Else

Debug.Print "Text file: " & strTextFile End If strTable = "tblNewJobs"

intTextType = Nz(Me![fraTextType].Value, 1) Select Case intTextType Case 1 Comma-delimited:

DoCmd.TransferText transfertype:=acImportDelim, _ TableName:=strTable, _ FileName:=strTextFile, _ hasfieldnames:=True

Case 2 Fixed-width strSpec = "Import-Jobs 13-Aug-2006" 'New style syntax

Application.CurrentProject.ImportExportSpecifications(strSpec). Execute

'Old style syntax causes error

'DoCmd.TransferText transfertype:=acImportFixed, _ specificationname:=strSpec, _ TableName:=strTable, _ FileName:=strTextFile, _ hasfieldnames:=True

End Select

Assign table as the subform's source object.

Me![subNewJobs].SourceObject = "fsubNewJobs"

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

The "Save New Jobs to Table" command buttons event procedure runs an append query that adds the selected jobs to tblNewJobs:

Private Sub cmdSaveJobs_Click()

On Error GoTo ErrorHandler

DoCmd.SetWarnings False DoCmd.OpenQuery "qappNewJobs" strTitle = "Jobs imported"

strPrompt = "New jobs imported into tblJobs from " _

& GetTextFile() MsgBox strPrompt, vblnformation + vbOKOnly, strTitle

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

The "Source Text File" command buttons event procedure opens a File Picker dialog for selecting a text file for importing, filtering for either text or comma-delimited files depending on the option selected on the form:

Private Sub cmdSourceTextFile_Click()

On Error GoTo ErrorHandler

Dim fd As Office.FileDialog Dim txt As Access.TextBox Dim strPath As String Dim strFilter As String

Create a FileDialog object as a File Picker dialog box.

Set fd = Application.FileDialog(msoFileDialogFilePicker) Set txt = Me![txtSelectedTextFile]

Set the initial path to the custom Input Documents path.

strPath = GetInputDocsPath()

intTextType = Nz(Me![fraTextType].Value, 1)

With fd

.title = "Select text file with job data to import" .ButtonName = "Select" .Filters.Clear If intTextType = 1 Then

.Filters.Add "Comma-delimited files", "*.csv" Elself intTextType = 2 Then

.Filters.Add "Fixed-width files", "*.txt" End If

.InitialView = msoFileDialogViewDetails .InitialFileName = strPath If .Show = -1 Then strTextFile = CStr(fd.SelectedItems.Item(1)) Else

Debug.Print "User pressed Cancel" End If End With txt.Value = strTextFile

Save the value to tbllnfo; the form can't be bound to that table because the main menu is bound to it, and thus it is locked.

SaveTextFile (strTextFile)

Me![txtSelectedTextFile].Value = strTextFile

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

The "Import Text Type" option group's event procedure first checks that the selected text file is the right type, and clears it if not, then sets up a Select Case statement to process comma-delimited and text files differently, calling the SaveTextFile Sub:

Private Sub fraTextType_AfterUpdate() On Error GoTo ErrorHandler Dim strExt As String Check that the selected text file is the right type, and clear the file selection if not.

intTextType = Nz(Me![fraTextType].Value, 1) strTextFile = GetTextFile()

If Len(strTextFile) > 4 Then strExt = Right(strTextFile, 3) End If

Select Case intTextType Case 1 Comma-delimited

If strExt = "txt" Then SaveTextFile ("")

Me![txtSelectedTextFile].Value = "" End If

Case 2

Fixed-width

If strExt = "csv" Then SaveTextFile ("")

Me![txtSelectedTextFile].Value = "" End If

Case 3

If strExt <> "csv" And strExt <> "txt" Then SaveTextFile ("")

Me![txtSelectedTextFile].Value = "" End If

End Select

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

Exporting Text Data

When exporting data to text files (say, for import into a mainframe computer program, or another application that can import data from comma-delimited or fixed-width text files), often you need to filter the data, usually by date. The Export Job Data to Text File (frmExportTextData) form has two textboxes bound to Date fields that you can use to select dates for a date range used to filter the records to be exported to a text file.

. i- ^ y- v- -p^. i-j?'. *rJ3 Controls bound to Date fields on an Access 2007 form have a long-awaited feature shown in Figure 10.15: a pop-up calendar to make it easy to select a date.

FIGURE 10.15

The Export Job Data to Text File form, with a pop-up date selector.

FIGURE 10.15

The Export Job Data to Text File form, with a pop-up date selector.

1. To open the "Export Job Data to Text File form (frmExportTextData), select the "Export Data to Text Files" option on the main menu and click the button to its left. This form lets you export a range of records filtered by date to either a comma-delimited or fixed-width text file.

2. After you select the From Date and To Date, either by typing them in or using the date selector pop-up, click the "Inspect New Jobs to Export" button to show the jobs in the selected date range in the Jobs to Inspect (fsubNewJobs) subform, as shown in Figure 10.16.

3. If you don't want to go ahead with the export, use the "Clear Jobs to Export" button to clear the selected jobs.

4. To proceed with the export, use the "Export Jobs to Text File" button to run code using the TransferText method to export the selected date range of jobs to a text file of the type selected in the "Export Text Type" option group. Figure 10.17 shows an exported comma-delimited file opened in Excel (which is the default application for .csv files).

FIGURE 10.16

Inspecting jobs in a given date range to export to a text file.

El frmExportTexlData

Export Job Data to Text File

Export Date Range: Jobs to Inspect:

To Date: 8/4/2006

To Date: 8/4/2006

Product Number *

Job Date/Time

Factory Name

Production Line

Batch Quantity -

FRB-1013917-A

7/13/200611:13:00 AM

Nutley

143

576.38

FRB-1011580-E

7/14/2006 2:23:00 PM

Oakdale

114

224.11

S

FRB-1013920-A

7/14/2006 5:39:00 PM

Simms

156

5788.1

FRB-1013924-B

7/15/200611:33:00 AM

Simms

143

837.22

FRB-1013925-B

7/16/2006 2:34:00 PM

Nutley

149

5582.2

FRB-1013926-A

7/17/2006 3:12:00 AM

Amberson

120

8892.97

FRB-1013927-A

7/18/20064:23:00 PM

Amberson

114

239.27

FRB-1013929-A

7/19/2006 5:43:00 PM

Nutley

143

3579.28

FRB-1013930-A

7/20/2006 2:33:00 PM

Amberson

149

38S.57

FRB-1013930-B

7/21/200610:43:00 AM

Nutley

156

873.39

FRB-10139Q3-A

7/22/2006 9:22:00 AM

Simms

149

223.22

FRB-1013931-A

7/22/2006 9:37:00 AM

Simms

120

3865.12

FRB-1013932-A

7/23/2006 9:44:00 AM

Nutley

143

984.25

FRB-1013933-A

7/24/200610:13:00 A M

Oakdale

120

349.1

Record: w ¡1 of 45 ► M * | | ¡Search |

Export Text Type Comma-delimited i^i Fixed-width

Cl ea r J ob s to Ex p ort Export Jobs to Text File

Export Text Type Comma-delimited i^i Fixed-width

Cl ea r J ob s to Ex p ort Export Jobs to Text File

FIGURE 10.17

An exported comma-delimited text file opened in Excel.

FIGURE 10.17

An exported comma-delimited text file opened in Excel.

: . • r^"-r The new method of dealing with specs works well with fixed-width exports, at least so

|ong as yOU always want to export to the save file name, as I do in the sample code.

The relevant procedures from the form module are listed next. The "Clear Jobs to Export" buttons event procedure clears the datasheet subform of jobs:

Private Sub cmdClearData_Click() On Error Resume Next

Me![subFilteredJobs].SourceObject = "" End Sub

The "Export Jobs to Text File" buttons event procedure sets up a Select Case statement to export the selected jobs, using the TransferText statement with different arguments:

Private Sub cmdExportJobs_Click()

On Error GoTo ErrorHandler

Dim intTextType As Integer Dim strQuery As String Dim strTextFile As String Dim strTitle As String Dim strPrompt As String intTextType = Nz(Me![fraTextType].Value, 1) strQuery = "qryFilteredJobs"

Select Case intTextType

Case 1

Comma-delimited strTextFile = GetOutputDocsPath() _

&"Filtered Jobs.csv" DoCmd.TransferText transfertype:=acExportDelim, _ TableName:=strQuery, _ FileName:=strTextFile, _ hasfieldnames:=True

Case 2

Fixed-width strTextFile = GetOutputDocsPath() _ &"Filtered Jobs.txt" strSpec = "[email protected]@hyFilteredJobs"

strTextFile = GetOutputDocsPath() & "Filtered Jobs.txt" 'New style syntax

Application.CurrentProject.ImportExportSpecifications(strSpec).Ex ecute

'Old style syntax causes error

'DoCmd.TransferText transfertype:=acExportFixed, _ TableName:=strQuery, _ FileName:=strTextFile, _ hasfieldnames:=True

End Select strTitle = "Exported jobs"

strPrompt = "Exported filtered jobs to " & strTextFile MsgBox strPrompt, vblnformation + vbOKOnly, strTitle

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

The "Inspect New Jobs to Export" buttons event procedure sets the form datasheets source object to the subform bound to qryFilteredJobs, to display the selected jobs:

Private Sub cmdInspectNewJobs_Click()

On Error Resume Next

Me![subFilteredJobs].SourceObject = "fsubFilteredJobs"

End Sub

0 0

Post a comment