Importing Data from a Text File
Sometimes when importing data from a mainframe the only thing we have to work with is a text file. The file can be in the form of tab delimited, comma-separated values (CSV Files), or any text file with separate lines and field data separated by tabs, commas, semicolons, etc. The "separators" between the text determine the placement of the data in the fields that will make up our new database table.
This method of importing to MS Access can be the most problematical, but is commonly the only recourse in sharing data between legacy applications and mainframes, merging of companies, etc. As in importing Excel Spreadsheets, MS Access has an intermediate step where the user can verify data type and format, etc. The screenshots below show the steps for importing text files, as well as the intermediate data design steps.
Migration issue: Large comma or tab separated data files can pose huge compatibility problems for importing to MS Access. Issues such as blank fields, inconsistent data entry, etc., can mean hours of data cleanup before the data is usable. For that reason it is usually advisable to import text files into MS Excel spreadsheets first. A spreadsheet is easier to edit “on the fly" than a database table.
The screenshots below show the intermediate data design steps when importing a text file to MS Access.