none
Import multiple text files into single table in access

    Question

  • Hi

    I have made a VBA code where I want to import multiple text files. I have made 8 identical import specifications and only changed the field names. The code is:
    Dim strfilbane As String
        Application.FileDialog (msoFileDialogOpen). Filters.Clear
        Application.FileDialog (msoFileDialogOpen). Filters.Add Text file, "*. txt"
         If Application.FileDialog (msoFileDialogOpen). Show = -1 Then
         strfilbane = Application.FileDialog (msoFileDialogOpen). SelectedItems (1)
         DoCmd.TransferText acImportDelim, "Test", "tbl_Test" strfilbane, False, ""
         DoCmd.TransferText acImportDelim, "Test2", "tbl_Test" strfilbane, False, ""
         DoCmd.TransferText acImportDelim, "test3", "tbl_Test" strfilbane, False, ""
         DoCmd.TransferText acImportDelim, "Test4", "tbl_Test" strfilbane, False, ""
         DoCmd.TransferText acImportDelim, "Test5", "tbl_Test" strfilbane, False, ""
         DoCmd.TransferText acImportDelim, "Test6", "tbl_Test" strfilbane, False, ""
         DoCmd.TransferText acImportDelim, "Test7", "tbl_Test" strfilbane, False, ""
         DoCmd.TransferText acImportDelim, "Test8", "tbl_Test" strfilbane, False, ""
         DoCmd.SetWarnings False
         DoCmd.OpenQuery "Query1"
         DoCmd.OpenQuery "Query2"
         DoCmd.SetWarnings True
         End If

    It did not work out the way I wanted it. I want to have the fields side by side, see picture. Is it an easy way to do this and avoid this problem?

    Friday, September 23, 2011 7:51 AM

Answers

  • One way would be, rather than importing the data, to create links to each of the text files.  You'd then have a number of options:

    1.  Create a UNION ALL query which tacks the result sets of a series of SQL statements together, inserting Nulls at the empty column positions, e.g.

    SELECT Position, MatrixCode,
    NULL AS Position2, NULL AS MatrixCode2,
    NULL AS Position3, NULL AS MatrixCode3,
    NULL AS Position4, NULL AS MatrixCode4,
    NULL AS Position5, NULL AS MatrixCode5,
    NULL AS Position6, NULL AS MatrixCode6,
    NULL AS Position7, NULL AS MatrixCode7,
    NULL AS Position8, NULL AS MatrixCode8
    FROM Test1
    UNION ALL
    SELECT NULL, NULL,
    Position2, MatrixCode2,
    NULL, NULL,
    NULL, NULL,
    NULL, NULL,
    NULL, NULL,
    NULL, NULL,
    NULL, NULL
    FROM Test2
    UNION ALL
    <and so on to>
    SELECT NULL, NULL,
    NULL, NULL,
    NULL, NULL,
    NULL, NULL,
    NULL, NULL,
    NULL, NULL,
    NULL, NULL,
    Position8, MatrixCode8
    FROM Test8;

    The result set of the above could be used in place of a local table if it need not be updatable, in which case the values in the original text files would be reflected if these should change over time.

    For an updatable local table:

    2.  Use the above UNION ALL query as the basis for an 'append' query to insert rows into a pre-designed empty table.

    3.  Use the above UNION ALL query as the basis for a make table query.

    4.  Instead of using a UNION ALL query, execute a set of 8 'append' queries, each base on one of of the linked tables, inserting the values into the relevant columns in each case.

    The links, and SQL statement for a single append query or those for multiple append queries UNION ALL query could of course be built and executed in code on the basis of variables.  Similarly the table into which the rows are to be inserted could also be created in code rather than pre-designed by executing a CREATE TABLE statement.  This would only really be necessary, however, if data is being imported on multiple occasions and the table structure changes each time.

    But, having said that, I would question the design of the ultimate table.  I assume each subset of rows represents something like a different category of position and matrixcode attributes.  If so the table is encoding data as column headings, which is not a good design.  A fundamental principle of the database relational model is the Information Principle.  This requires that all data be stored as values at column positions in rows in tables, and in no other way.  A better design therefore would be to have just two columns for position and matrixcode (which I presume is what you are currently getting) and a third column such as Category, whose values can be inserted as a constant in each part of the single UNION ALL  append 'append' query, or in each of separate 'append' queries.  The former would thus be along these lines:

    INSERT INTO NewTable(Category, Position, MatrixCode)
    SELECT "Category 1", Position, MatrixCode
    FROM Test1
    UNION ALL
    SELECT "Category 2", Position2, MatrixCode2
    FROM Test2
    < and so on to>
    SELECT "Category 8", Position8, MatrixCode8
    FROM Test8;


    Ken Sheridan, Stafford, England
    • Edited by Ken Sheridan Friday, September 23, 2011 12:33 PM Surplus commas removed from SQL statements
    • Marked as answer by Bruce Song Thursday, October 06, 2011 8:42 AM
    Friday, September 23, 2011 12:31 PM