Importing multiple DAT files into access data base

Question

I have 211 files  in separate folders that I want to import to access. These are DAT files that open with Note Pad.  I want to select the columns I want to be imported as separate fields in Access and I want to save the script so I don't have to redefine that process for each of the 211 folders. Can you show me how to do this?

Answer 1

You can use the MID function, to separate out each character into separate fields, like this:

QUERY DESIGN VIEW:

QUERY DATASHEET VIEW:

MY TEST TABLE FOR THE ABOVE:

Here is the syntax for the MID function (this works the same in Access and Excel, so I copied the quote below from Excel help):

"MID(text, start_num, num_chars)

The MID function syntax has the following arguments:

• Text               Required. The text string containing the characters you want to extract.

• Start_num Required. The position of the first character you want to extract in text. The first character in text has start_num 1, and so on.

• Num_chars   Required. Specifies the number of characters you want MID to return from text."

In case anyone would like to see the same thing done in Excel, here is an example:

Answer 2

The current filed looks like this DCBACAADCAABCCAACBACADDABBABAABADBABAACCD and I have been asked to separate each item into a separate filed.

Answer 3

That sounds like some is thinking in terms of spreadsheets and not relational databases. Can you give an example of the data in that one field that the user wants broken out?

Answer 4

Thanks, that helped. After I completed the table of 4793 records I was asked to separate one of the fields with 40 items into 40 separate fields. Can you suggest an easy way of doing that?

Answer 5

One way to do this, is to save the import steps, like this:

[1]  Import one of the DAT files into Access, using "External Data -> Text File".

[2]  After the import is done, check the box for "Save import steps".

[3]  For additional imports, click on "External Data -> Saved Imports".

Another technique:

[a]  Create a new Macro.

[b]  In Macro Design View, click the "Show All Actions" button.

[c]  In the "Add New Action" box, select "ImportExportText"

[d]  Fill out the required fields.

Yet Another technique:

[i]  Create a new Macro.

[ii]  In Macro Design View, click the "Show All Actions" button.

[iii]  In the "Add New Action" box, select "RunSavedImportExport".

NOTE: An advantage of using a macro instead of "External Data -> Saved Imports" is that the macro uses one less step.  In other words, when you run your macro, it will start the import right away, instead of having to navigate through "External Data -> Saved Imports" each time.