Results 1 to 8 of 8
  1. #1
    atomdist is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    3

    Import Multiple XLS into Access table with fields that do not match

    Hi,
    I have an access DB with a table with a set of 60+ fields for my eCommerce platform. I then have multiple xls/csv files which I wish to import into this table. To make things easier I will start with one xls file. These are the field names: Product Number, Manufacturer Number, Category, Manufacturer, Description, EAN and more.

    I have setup the table which has compulsory fields which are set as required, some fields need default data so I have setup this also.



    I need to import the xls file which I currently have linked, into the table for my eCommerce. What I need to figure out is how to import this, map the fields as they do not match between the xls and the table. I also need to be able to fill some fields in with a combination of fields two or more fields and some we need to input some standard text into the fields of the DB table. Please see some examples below:

    XLS Fields DB Table
    EAN (Example: 45678) SKU = EAN The EAN will also include some standard text to end result will look like the example (Example: "ad-sp-45678")

    Manufacturer (Example: Apple) Meta _Keywords = Manufacturer & Category with comma between (example: "Apple, Laptop")
    Category (Example: Laptop)


    I have also attached screen shots of the fields


    Click image for larger version. 

Name:	excel.PNG 
Views:	6 
Size:	11.6 KB 
ID:	29317 Click image for larger version. 

Name:	db.PNG 
Views:	6 
Size:	17.7 KB 
ID:	29316

    Any advice would be mich appreciated.

    Thanks
    Martyn

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Do these files have random names? Or will they have the same name every time?
    if they have the same name, then overwrite the previous linked file, then run the import query.

    why would they have missing,or additional fields? The excel file should be the Same every time.
    map those fields to target a table field in the import query.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    import or link to your excel file

    then create an append query where you can specify which column goes where. You will end up with sql something like

    INSERT INTO myTable (Product,....)
    SELECT SKU,... FROM tblExcel

    you may have to correct for datatype in which case use the 'c' functions (cInt,cLng etc) I don't understand your examples. I'm guessing the one to the left is your excel file.

    Note that you are using non alphanumeric characters in your table names (-) and spaces in field names. These will come back to bite you at some point, as will using reserved words such as Description

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    why would they have missing,or additional fields?
    you can get this with xml files which only contain the data reported. If a user hasn't completed say the second line of an address (because they don't have one) it will not be included in the data unless the xml generator has been configured to include blank fields (in my experience more often not included!). And when included, often at the end of the field collection almost as an afterthought

  5. #5
    atomdist is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    3
    Hi Ajax
    Thanks for replying.
    The examples haven't show formatted as I had entered them. Please see example below:


    XLS Fields DB Table
    EAN (Example: 45678) SKU = EAN The EAN will also include some standard text to end result will look like the example (Example: "ad-sp-45678")
    Manufacturer (Example: Apple)
    Category (Example: Laptop)
    Meta _Keywords = Manufacturer & Category with comma between (example: "Apple, Laptop")


    I see you point with the issues with the field names, the DB table are the ones set by the eCommerce platform, the excel sheet is simply what our supplier sets. I can change the ones in the excel but not the DB table.

    Thanks
    Martyn

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the fields in the magento table look OK and you can change the table name easily enough or use in your query

    SELECT * FROM Magento-Table MT


    or if you want the extra typing

    SELECT * FROM Magento-Table AS MT

    which will alias it to MT going forward

    with regards concatenating fields, you can do this in your append query


    INSERT INTO Magento-Table (SKU,....)
    SELECT "ad-sp-" & SOP.SKU,... FROM spire-online-pricelist SOP

    but still confused by your description. You say

    EAN (Example: 45678)
    SKU = EAN The EAN will also include some standard text to end result will look like the example (Example: "ad-sp-45678")

    but SKU in your table is a number, not text

  7. #7
    atomdist is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    3
    Hi,
    Thanks again.

    I'll see if I can explain my example a bit better!

    You can ignore the number being set for the SKU.

    1st example
    SKU in the Magento Table needs to be populated with the EAN field from the XLS File, but included the follwoing text before the number: "ad-sp-"

    SKU = "ad-sp-"EAN
    So if the EAN in row 2 of the XLS File was 12345 it would be imported into theSKU field in the magento table as "ad-sp-12345"


    2nd Example
    In the XLS File, There are two fields Manufacturer and Category. I want to merge these into one field in the Magento Table - This field is Meta_Keywords.

    Meta_Keywords = Manufacturer & Category Combined - but I wish to insert a comman between each so the end result would be "manufacturer, category"
    Obviously the manufacturer and category would be replaced with the relevant data from each row.

    Thanks
    Martyn

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've already answer the KU question. with regards the other it is pretty much the same


    INSERT INTO Magento-Table (Meta_Keywords,....)
    SELECT SOP.[Manufacturer] & ", " & [Category],... FROM spire-online-pricelist SOP

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 12-07-2016, 07:44 AM
  2. Import multiple Excel Files into 1 access table
    By jurbin in forum Import/Export Data
    Replies: 1
    Last Post: 05-15-2015, 01:45 PM
  3. Replies: 1
    Last Post: 04-25-2015, 01:41 PM
  4. Replies: 1
    Last Post: 03-25-2014, 08:54 PM
  5. Match Table Fields
    By vinsavant in forum Access
    Replies: 1
    Last Post: 01-17-2013, 12:06 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums