Access Performing ETL - Part i

Importing a text file requires determining whether the file is a comma separated values (CSV) file, fixed width file or line appended with a carriage return file. This post will focus exclusively on working with a carriage return text file containing embedded DML sql statements. The logic looks a little like this:

·         Open the text file

·         Save the contents of the current line  to appropriate variable(s)

·         Use Access’s DoCmd. RunSQl method to execute the SQL statement found in the current line in the text file

·         Continuously loop through the text file until its end

How to do it

Useful commands file I/O material

Command	Note
Line Input #	Read a line up until the carriage return and assigns it to a variable
Input #	Read CSV data into variable(s)
Input()	Input function returns all of the characters it reads and unitl the EOF –into one variable,
Write #	Data written with Write # is usually read from a file with Input #.; Writes data to a file – separate by commas
Print #	Data written with Print # is usually read from a file with Line Input # or Input
Get #	Data read with Get is usually written to a file with Put
Put #	Writes data from a variable to a disk file.
EOF(n)
Open	Open a file for input or output
Close	Close a file

This input file contains SQL statement that insert data into a table

Figure 1

Figure 2