Inserting to an Access table from an Excel spreadsheet w/ extracting data from a field RRS feed

  • Question

  • Everyone,

      I would like to get input on a process I need to do.

    1.  I have a file that comes in weekly and it is tab delimited.

    2.  I need to insert into a table.

         A.  Need to extract the 1st 10 characters from a field to insert into 1 of the table columns.

         B.  Need to ensure that column 1 and column 2 of the table does not have any dups.

    3.  I would like to write a procedure to do this, so I can change the input file name each time and then run.

      What would be the best way of doing this in MS Access?  Thanx in advance and I look forward to hearing ideas!


    Sunday, November 10, 2019 10:30 PM

All replies

  • Here is my sql:

    INSERT INTO tblModelVin (ModelCode, VIN10)
    SELECT  tblModelVinFull.ModelCode, tblModelVinFull.VIN10
    From tblModelVinFull
    LEFT JOIN tblModelVin on tblModelVinFull.VIN10 = tblModelVin.VIN10
    WHERE tblModelVin.VIN10 is NULL;

      Yet, the first time the table is empty.  So I am now loading the duplicates from

    tblModelVinFull.  The VIN10 value can be duplicates and this is what I am trying to not have duplicates in the other table.  Plz let me know thoughts on what I am doing wrong.  Thanx

    Monday, November 11, 2019 12:10 AM
  • I don't fully understand what the details of your goal are - as some of the description is open to interpretation.  However at a high level I can say that it is very common to have repeating imports from other data sources.  In general I initially import into a local temp table.... then process the records as needed via a sequence of action queries....then append/write into the final table.

    attempting to do everything in a single sql statement is difficult to achieve, and difficult to trouble shoot.....

    Monday, November 11, 2019 10:39 PM
  • yes this is what I need to do.  It can be in several steps and that is not an issue.

    How this should be processing and I am unsure how to do it.

    1.  Input is an Excel spreadsheet.

    2.  Take that spreadsheet and extract the 1st 10 chars from one of the fields and move it into VIN10 of the table.

    3.  Take the ModelCode and load it into another field of the table.  This makes 1 row and insert into a table.

    4.  Take the above code and load all no dups (ModelCode, VIN10) into the end result table.  I tested the code and it works.

      I do not know how to write a script (VBA or Access) and do steps 1 - 3 above.  All of the steps 1 - 4 should be in one job/script to do all of these processes in sequence.  I hope this clarifies what I am trying to do.

    Monday, November 11, 2019 10:47 PM
  • You can easily prevent duplicates in the VIN10 column in tblModelVin by indexing the column uniquely (no duplicates) or by making it the primary key.  When you then run the 'append' query it will import only one row per VIN10 value, rejecting any duplicates either within the set being imported, or if the existing table already includes one or more rows with a VIN10 value being imported.

    You can import only the first 10 characters of each VIN value by means of the LEFT function, so you should be able to do everything with a single 'append' query in which the source table is a linked table to the Excel worksheet:

    INSERT INTO tblModelVin(ModelCode, VIN10)
    SELECT ModelCode, LEFT(VIN,10)
    FROM NameOfLinkedSourceTable;

    In this case the tblModelVin table is fully normalized, though to protect the integrity of the data it would be as well to insert rows into a referenced tblModelCodes table. This would be a single column table, in which the ModelCode column is the primary key, and which is related to tblModelVin on ModelCode, with referential integrity enforced.  To populate this table you'd first insert rows from the Source table, before executing the above query, with:

    INSERT INTO tblModelCodes(ModelCode)
    FROM NameOfLinkedSourceTable;

    Again any duplicates of existing rows will be rejected by virtue of the key violations.

    In more complex situations you should aim to insert the imported data into a set of correctly normalized related tables.  For an illustration of how to do this you might like to take a look at in my public databases folder at:!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file imports data from a simple Excel worksheet into a single 'master' table, and then decomposes this into a set of related tables by executing a series of 'append' queries in a specific order.

    Ken Sheridan, Stafford, England

    Tuesday, November 12, 2019 12:06 AM