Question

Locked

Need help importing text data records into Access

By tonylewis ·
I need to know how, if possible, I can import data into an access table from a text file that is in the form of data records instead of in a column format. Here's an example of 1 record:


event-type , alarm

application-type-id , 15

application-name , AMF

application-version , 1.0.0

name , AMF

number , 1300

description , Service group redundancy.

suppression , allowed


So far I've created a table in Access with the column headings (event-type, application-type-id, etc). However, since the field names are not in columns in the text document, but are the first field of each line, then I can't seem to figure out how to get Access to import just the 2nd field entries under the corresponding columns in the table I've created.

Is what I'm trying to do possible in Access? There are about 100 records in the text file so it's a bit too much to go through and manually reformat everything into columns. Any help would be appreciated.

I forgot to mention that I tried importing into Excel too, but ran into similar issues.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Answers

Collapse -

Import or link data and objects

Important If you link to a file on a local area network, make sure to use a universal naming convention (UNC) (universal naming convention (UNC): A naming convention for files that provides a machine-independent means of locating the file. Rather than specifying a drive letter and path, a UNC name uses the syntax \\server\share\path\filename.) path, instead of relying on the drive letter of a mapped network drive in Microsoft Windows Explorer. A drive letter can vary on a computer or may not always be defined, whereas a UNC path is a reliable and consistent way for Microsoft Access to locate the data source that contains the linked table.

From another Access file


1. Open a database, or switch to the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.) for the open database.
2. Do one of the following:
* To import (import: To copy data from a text file, spreadsheet, or database table into an Access table. You can use the imported data to create a new table, or you can append (add) it to an existing table that has a matching data structure.) tables, on the File menu, point to Get External Data, and then click Import.
* To link tables, on the File menu, point to Get External Data, and then click Link Tables.

3. In the Import (or Link) dialog box, in the Files of type box, make sure that Microsoft Access (*.mdb; *.adp; *.mda; *.mde; *.ade) is selected.
4. Click the arrow to the right of the Look in box, select the drive and folder where the Access file that you want to import or link is located, and then double-click the database's icon.
5. In the Import Objects (or Link Tables) dialog box, click each table that you want to import or link.

If you're importing, and you want to import just the selected tables' definitions (not the data they contain), click Options, and then under Import Tables, click Definition Only.

Note If you import a table that is already linked, then Access does not import the data; instead, it links the table to its data source (in effect, copies the link information).


From a text file

HideImport or link a delimited or fixed-width text file

Important Before you import (import: To copy data from a text file, spreadsheet, or database table into an Access table. You can use the imported data to create a new table, or you can append (add) it to an existing table that has a matching data structure.) or link data from a delimited text file (delimited text file: A file containing data where individual field values are separated by a character, such as a comma or a tab.) or fixed-width text file (fixed-width text file: A file containing data where each field has a fixed width.), make sure that the file has the same type of data in each field and the same fields in every row.

1. Open a database, or switch to the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.) for the open database.
2. Do one of the following:
* To import data, on the File menu, point to Get External Data, and then click Import.
* To link data, on the File menu, point to Get External Data, and then click Link Tables.

3. In the Import (or Link) dialog box, in the Files of type box, select Text Files (*.txt; *.csv; *.tab; *asc).
4. Click the arrow to the right of the Look in box, select the drive and folder where the file is located, and then double-click its icon.
5. Follow the directions in the Import Text Wizard dialog boxes. Click Advanced to create or use an import/export specification (import/export specification: A specification that stores the information that Access needs to import or export a fixed-width or delimited text file.).

To cancel importing, press CTRL+BREAK.

Notes

* You can append the data to an existing table as long as the first row of your text file matches the table's field names.
* If all the records in a fixed-width text file are the same length, an embedded row separator (such as a carriage return and a linefeed) in the middle of a record will not cause unexpected results. However, if the records aren't all the same length, Microsoft Access will treat the embedded row separator as the end of the record.
More info here:
http://office.microsoft.com/en-gb/access/HP051876101033.aspx

Please post back if you have any more problems or questions.

Collapse -

Thanks....However, .....

by tonylewis In reply to Import or link data and o ...

However, those instructions assume that the original text file that I'm importing from have the data fields in columns. My text file has the data formatted as records with the fields identified on each line (see an example of my records in my original question).

In doing further research it seems I may have to do some sort of VBS or SQL in order to import the data properly. Anyone have any suggestions? I'm not really savvy on VB or SQL in Access.

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums