How to import and link data from a text document to a Microsoft Access database? step by step guide

This program not only allows its users to be able to export the data from the databases to other programs such as Microsoft Word, but also allows them import and link data from a text document to a database in Access. In this way, they can add additional information to your worksheets in spreadsheet software.

To insert data from a file into the program can be done in two different ways, either importing the filethis in case you want a editable copy of the data or simply through bonding for those cases where you only want view the most recent source data and do not want to modify or edit.

Therefore, users have the possibility to perform this procedure of two different wayseach of them will be applied depending on the needs of the same. That is why here we are going to show you how you can start import or link data from a text document to Accessfor this, follow in detail everything that we will teach you below.

What text document formats can I link to or import into an Access database?

In the case of the text files they contain raw readable characters, as they are numbers, special characters What tab stops, line feeds, and letters. In the case of Microsoft Access allows the use of some supported formats with your system as they are: .csv, .tab, .txt, .asc. If what you are looking for is to use a text file as source file to link or import, It is important to take into account the following:

All content found in the source file must be fully organized so that the data import and link wizards can separate the content into record sets and for each of these split records into a collection of fieldsthis means that divide the information into rows and columns. When a Text file it’s found organized properly may belong to the type “Delimited Files” either “Fixed width file”.

So that you can understand this a little more, here we explain each of them:

delimited files

delimited files is where each the registers appears on a different line and fields are separated by a single characterthis is known as delimiter. Note that this can be any character as long as it does not appear in field valuesas they are: semicolon (;), space, tab, comma (,), period (.), among many others.

Fixed Width Files

The fixed width files are those where each record appears on a separate line and the width of each field remains consistent across all records. In order to understand this better, an example would be:

The first field of Registration will always have seven characters longfor the second record length will always be 12 charactersand so on for the other records. If the actual values ​​of longitude vary from record to record then these values they will not reach the required width to be padded with trailing spaces.

What are the most important things to consider before linking data from a text file to Access?

When this type of data imports, Access has as purpose create a copy of the data in the database destination without having to alter the origin. When the procedure is performed you have the opportunity to select which objects you want link or importin this way you can control the way in which it is going to be transfer tables and queries and in the same way indicate whether there are relationships between the tables.

However, these procedures are executed with the purpose of store some data or all the data found in a Text file in a database of Microsoft Access. This process can be done by inserting all this information either to a existing database or a new one. In this way the user can only view the information or even be able to manipulate it by editing its content.

When this procedure is executed, the user can specify how they want it to be arranged. the source fileas well as if you want create a table or append the data to an existing table.

It is also important that when executing this process the following aspects can be taken into account:

• when you make one data binding many of them are usually in a format that Access does not recognize, and such data is very important to use in the program database. If this happens you can export the source data as a text file and then import the content of the text file to an Access table.
• Generally, users make use of Access to manage data, but data in text format are usually received from different programs, for this you must import data at regular intervals and so streamline the import processthis will allow you to save work and time.

Learn step by step how to import and link data from a text document to Microsoft Access

It is important to mention that both the process of import and link are executed differently, both are very simple to perform although it can be considered as a somewhat lengthy procedure. That is why here we are going to teach you step by step how to import or link data from a text document to Microsoft Excel.

To do this, follow each of these methods:

to import

The first method that we are going to teach you is about import data from a text document into Access, note that when importing the user has the opportunity to edit that informationso this method is used when you want to start work such data in this database program.

In order to do this, you need to follow each of these steps:

Prepare the source file

The first step is prepare the source file for this you must open the source text file in a text editor how can he be Memo pad.

Next, you must review the content of the source text file and execute one of the following actions that we will teach you:

• Delimited or fixed width: Here you must make sure that the file consistently respects each of the formats. In the event that it is delimited, you must identify the delimiter. Now if the same has is field of fixed widthyou must make sure that each of the fields have the same width in each record.
• Text qualifiers: In the case of delimited files, they can contain field values ​​that are enclosed in single or double quotes.
• Number of fields: Source field numbers cannot be greater than 255, this is because Microsoft Access does not support more than 255 fields in a table.
• Skip registers and values: It is used when you are interested in only part of the Text file, for this the source file is edited before start the import procedure. Keep in mind that once it has started, it is no longer possible to omit records.
• Strange characters: Here you should review all the data and remove those extra characters like line feeds, carriage returns, and tab stops.
• Blank lines and fields: It is applied in the case that you want delete all unnecessary blank rows in the fileon many occasions blank fields are inserted that are not necessary. To add the records to an existing table, you must make sure that the corresponding field of the board accept new valuesotherwise these cannot be added.
• Type of data: If you want to avoid errors during import, you must be absolutely sure that each source field contains the same data type on each line. The program in these cases examines what they are the first 25 rows of a file in order to determine the type of data contained in the table. Therefore, in the first 25 lines it is recommended not to mix values ​​with different data types in the fields.
• Field names: For the delimited text filesif it does not include what they are the names of the fieldsthen it is a very good alternative to place them in the first row. During the import process the user has the opportunity to specify that Access treats the values ​​in the first row of the fields as the field name.

Once the necessary steps have been carried out at this point, the following will be close the source file if it is open. Yes the source file stays open some may be generated data conversion problems or errors during the procedure.

Start the import process

Once the procedure is prepared, the next thing is to start it, for this you must open the access database where are they going to include the data to be imported, at this point you can use a existing database or simply open a new database to insert them there. Before starting this process it is important that you choose if you want the data to be saved in a new or existing table.

To do this, keep these points in mind:

• Create a new table: When saving the data in a new table, Access creates a table and adds the imported data to that tablein case there is already a table with the name that has been selectedthen the program overwrites the contents of the imported data table.
• Append an existing table: Now if the data is added to a existing table, then the import process appends the data to the specified table.

After this, the operation begins to be carried out, but it is important to bear in mind that on many occasions these procedures fail for different reasons, either because the source data do not match the structure configurations and fields of the target tables.

In order to avoid these possible import errors, you need to review the following aspects of the table in Design view:

• First row: The first thing will be to look at the first row of the source text file, here you must ensure if it does not contain field namesyou should also see that the data types and position of each column match those of the corresponding field in the table. For delimited files, if the first row contains column headersthen it is not necessary that the order of columns and fields match.

However, in cases of the data types and names of each column if they must exactly match those of the corresponding field, this will prevent it from generate some error. When import fixed-width text files, this program of microsoft won’t give them the option use the values ​​of the first row as field names.

Let’s see:

• Additional or missing fields: It is possible that one or several fields do not appear in the destination table, for this you must add them before they are start the import process. Now if the destination table contains fields that do not exist in the source fileyou will not need to remove them from the table as long as null values ​​are accepted in the table.
• Primary key: If the table contains a primary key field, then the source file must have a column with values ​​compatible with the primary key field. Also, these values ​​must be completely unique. In the event that the imported records contain a primary key value that already exists in the destination table, the import procedure will generate an error.
• Indexed fields: If the indexed property of a table field is set to Yes, then the column of the source text file must have unique values.

The location of the wizard linking or importing text is a little different depending on Access version that is being used.

Here you must select the steps that match the version you are using:

• For the versions latest from Accesseither the version of Access Microsoft 365 subscription or Access 2019, you must go to the tab “External data” and there go to the section of “Import and Link” and then select “New data source”, after “From file” Y “Text file” finally.
• Now if you are making use of the versions of Access 2010, Access 2013 or Access 2016, then you should also go to the tab “Data external” and in the section “Import and Link” You must select the option “Text file”.

After all this, Access will open the dialog box “Get external data – text file”.

Then in the dialog “Get external data –text file”, you must go to the box “Filename” and there specify the name of the source file.

Then you must specify how you want the imported data to be saved:

• If you want the data to be stored in a new table, then you must choose the option of “Import the data source into a new table in the current database.”
• To save the data to a already existing tablethen you must select “Append a copy of the records to the table” and then select the table from the drop down list that will appear on the screen.

The next thing is to click on “To accept”, automatically Access Begin to examine file content and recommends how it can be properly organized. If the file uses a delimiter for separate the fields then it is necessary that the delimited option is activated, now if you use fixed width fieldsthen you should select the option “Fixed width”.

After this, click on the “Next” option, the option that will appear here will depend on the option you have selected if it is “Delimited” or “Fixed width”:

• Delimited: If this option is selected, then you must specify which character delimit field values. If the first row of the source file contains field names, then you must select the checkbox “The first row contains field names” and then click on “Following”.
• Fixed width: If the program discovers a column structure in the data, it will place vertical lines in the data to separate it from the fields. Here you should see the structure that the wizard recommends and, if necessary, you should follow the instructions on the wizard page to be able to “Add, remove or adjust the lines” to do so click on “Following”.

If the data is to be imported into a new table, you must click “Following” and there you should check field properties that are displayed in the import wizard. Now you must click on one of the columns in the lower half of the wizard page to be shown on the screen field properties corresponding.

At this point you should review and modify the fields that you think are necessary. In order for an index to be created on the field, you must set “Indexed” in “Yes”, to omit a table column entirely, then click the checkbox “No matter the field” and then the button “Following”.

If the records being added to a new table, the wizard prompts you to specify a primary keythen you must select the option “Allow Access add primary key. In this way the system adds a field of autonumber as first field in destination table automatically populating with IDs and then select “Following”.

After all this, the system shows you the final page of the wizard. If you want to import a record to a new table, you must identify the name for the destination table. This is done from the window “Import table” and there you write the name for it. Now if the table already existsthen a message will appear on the screen asking if you are willing to overwrite the content of the table, click on “Yes” to continue or in “No” to specify another name.

After all this you must select the button “To finalize”, this will allow Access imports all content. Once finished, you just have to open the target table in datasheet view, and there you can compare the data in the table with the data in the source file and then make sure all the data is correct.

Link

The other way to perform this procedure is through bondingin this case it is used for connect to data from another file without having to import it. This means that when performing this procedure the user can see the most recent data from the original file but you will not be able to create a copy of it or modify them.

Therefore, if you only want to see the information contained there and not modify it, you must apply this procedure and for this you must follow each of the steps that we are going to teach you below:

Steps to link a text file to Access

the first thing will be find the file to link and open it in a word processor, how can it be Notepad or Microsoft Word. You must bear in mind that to carry out this procedure you can only link one text at a time, this means that to link several text files you must repeat the whole procedure for each of them.

The next thing will be to review in detail all the content of the source file and then execute the necessary actions as it will be explained below:

• Delimited or fixed width: It is important that the file to be linked correctly has any of these two formats. In the event that it is found bounded, then it is necessary identify the delimiter character. Now if the file has fixed width fields, you must ensure that each of the fields has the same width in all available records.
• Text qualifiers: If the file is bounded, then you might have multiple field values ​​that are between single or double quote how can it be Antonio Alvarez.
• Number of fields: You must bear in mind that the number of source fields cannot exceed 255 fields in a table, as this is not supported by the program.
• Blank fields and lines: All blank lines are removed unnecessary in the file. In the event that blank fields are found, they can be used to add data that is missing in the file.
• Skip records and fields: Certain fields can be omitted, but records cannot be omitted.
• Strange characters: It is important that you review the original file and remove the extra characters What line feeds, carriage returns, and tab stops.
• Type of data: One way to avoid possible errors during the bonding is by making sure that each of the source fields has the same data type on each line.
• field names: In the delimited text filesif it doesn’t include the field names, then a very good alternative is to put them in the first rowthis will allow during bonding it can be specified that Access treats the values ​​in the first row as the names of the fields. In case that fixed width text filesthere is no option to use the fields in the first row as field names.

Next you must close the source file if it is open. Then you must open the database where do you want insert the link, It is important that the database is not read-only, but you have the necessary permissions to make changes to the database. In case you don’t want store any link in any existing database, then you can create it in a blank one.

It should also be mentioned that the location of the Link/Text Import Wizard command will change depending on the version of Access that is being used.

According to the version you are using you must follow this route:

• If you use the most recent version as it is Access 2019 version or Access Microsoft 365 subscriptionthen you need to access the tab of “External data” then in the cluster “Import and Link” You must choose the option “New data source” and then “From file”, finally click “Text file”.
• If you are making use of the versions of Access 2010, Access 2013 or Access 2016, then you should go to the tab “External data”, next to the section “Import and Link” and then click “Text file”.

Once you enter “Text file” the following window will appear on the screen:

in the window of “Get External Data – Text Files” you must specify the text file name containing the data you want to link in the box “Filename”. Next, you must select the option “Link to the data source by creating a linked table” and then click “To accept”. After this will start what is the text linking wizard.

At this point the program starts browse file and recommends how it should be found organized to avoid mistakes. In the case that you use a delimiter character to separate fields, then you must select the option of “Delimited” and if it contains fields of fixed widths then the option “Fixed Width” must be selected. After this click on “Following”. The next page that will appear on the screen will depend on the option you have selected in the previous step, either fixed width or delimited.

According to each of them you must follow the following:

• Delimited files: Here you must choose the character that delimits the values ​​of the field. In the case that the file contains a qualifier textthen in the box “qualifying text” you must select “o” if the first row of the file contains field names, in this way click on the option “The first row contains field names” and then in “Following”.
• Fixed width files: If it’s a fixed Width, then the wizard will show the content of the file, in the case that Access detect a column structure in datayou must place the vertical lines in the data to separate fields. Here make sure to review the structure suggested by the wizard, and if necessary follow each of the instructions in it to add, remove, or adjust lines and finally click on “Following”.

In the next window that appears on the screen, you will be shown the field properties, here you must click on one of the columns of the wizard to show you the properties of the corresponding field. There you must review them and change those that are necessary. And then click “Following”.

Now the final page of the wizard will appear, here you must specify a name for the linked table and click “To finalize”. After that the program creates the linked table. If the whole procedure is carried out correctly, then Access It shows you the following message on the screen “The table has been linked”, The next thing will be to open it and review each of the data and fields so that you can make sure that they are all correct.