Results 1 to 12 of 12
  1. #1
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239

    Import table data from Excel with different field names - DoCmd.TransferSpreadsheet method

    Hi,

    I want to Import data from Excel worksheet into Access table. I'm using DoCmd.TransferSpreadsheet method, in which I create new Table "SourceTable", and from that table I wish to import into different table in Access.

    Problem is that field names are not same in Access and Excel. Here's what I tried so far:

    Code:
    Private Sub cmdImport_Click()
    
    
    Dim Filepath As String
    Dim SQL As String
    
    
    Me.txtImport.SetFocus
    Filepath = txtImport.Text
    
    
    If FileExists(Filepath) Then       ' FileExists is a function to check if file exits
    
    
    DoCmd.TransferSpreadsheet acImport, , "SourceTable", Filepath, True
    
    
    Else
    MsgBox "File not found. Check file name or It's location !"
    
    
    End If
    
    
    SQL = "INSERT INTO DestinationTable" & _
    "SELECT SourceTableField1,SourceTableField2" & _
    "FROM SourceTable" & _
    "WHERE NOT EXISTS(SELECT * FROM DestinationTable" & _
                     "WHERE (SourceTable.SourceTableField1=DestinationTable.DestinationTableField1 AND SourceTable.SourceTableField2=DestinationTable.DestinationTableField2))"
    
    
    
    
        DoCmd.RunSQL SQL
    
    
    End Sub
    This code return me error "the INSERT INTO contains the following unknown field name", so looks like there is still some name issue.


    Question :

    1. Is there any other way of DoCmd.TransferSpreadsheet method to Import into tables with different field names ?

    2. What is correct SQL to IMPORT without duplicates, or what Is wrong with my posted SQL ?

    Any help much appreciated !!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Save the XL file as a generic filename, import.xls.
    link it as a table in access.
    build a query using this table to append it to the target table.
    put this in a macro.

    now the steps are:
    save XL workbook to import.xls
    run import macro.
    done.

  3. #3
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    Hi ranman,

    thanks for response. I'm stuck with 1st step, I can't save workbook as .xls. Here's what I tried :

    Code:
    Private Sub Command5_Click()
    
    
    Dim XcelFile As Excel.Application
    
    
    Set XcelFile = CreateObject("Excel.Application")
    XcelFile.workbooks.Open "C:\Users\MJohn\Downloads\Import.xlsx", True
    XcelFile.Visible = True
    
    
    XcelFile.SaveAs Filename:="C:\Users\MJohn\Downloads\Import.xlsx", FileFormat:=56
    
    
    
    
    Set XcelFile = Nothing
    
    
    End Sub
    Code opens Excel workbook, but then I keep receiving error "Object doesn't support this method or property", in line "XcelFile.SaveAs Filename:="C:\Users\MJohn\Downloads\Import.xlsx", FileFormat:=56"

  4. #4
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    No response, great

    For all others.... Linking Excel file to Access tables and then append - It doesn't work when you wish to Import into table where you have anything else than Text field.

    So only way that It works for me is to create another table (with same data type fields), and then import data into It. After that, you must run query to execute final Import into destination table.

    Beside that, Access is behaving very badly with a little bit complex structured SQL Selects. I had to try a billion things before correct Import was done - where there were no problems in Oracle or MySql, Access didn't execute nothing or results were completely different

    I still haven't found how to save Excel file to .xls from Access, please help. So far It's the only format that Import without duplicates works for me !

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are missing an argument in the "TransferSpreadsheet" command.
    You have
    Code:
    DoCmd.TransferSpreadsheet acImport, , "SourceTable", Filepath, True
    If you want to import from an Excel 97 or 2000 format spreadsheet, try
    Code:
    DoCmd.TransferSpreadsheet acImport, 8, "SourceTable", Filepath, True
    or
    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SourceTable", Filepath, True
    For Excel 2010 format, try
    Code:
    DoCmd.TransferSpreadsheet acImport, 10, "SourceTable", Filepath, True
    or
    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "SourceTable", Filepath, True

    As for your code, I would first get the transferspreadsheet command working. (I modified your code a little)
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cmdImport_Click()
        Dim Filepath As String
        Dim SQL As String
    
    
        Me.txtImport.SetFocus
        Filepath = txtImport.Text
    
    
        If FileExists(Filepath) Then       ' FileExists is a function to check if file exits
    
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "SourceTable", Filepath, True
    
            DoEvents
    
            '        SQL = "INSERT INTO DestinationTable" & _
                     '              "SELECT SourceTableField1,SourceTableField2" & _
                     '              "FROM SourceTable" & _
                     '              "WHERE NOT EXISTS(SELECT * FROM DestinationTable" & _
                     '              "WHERE (SourceTable.SourceTableField1=DestinationTable.DestinationTableField1 AND SourceTable.SourceTableField2=DestinationTable.DestinationTableField2))"
            '
            '
            '        DoCmd.RunSQL SQL
    
        Else
            MsgBox "File not found. Check file name or It's location !"
        End If
    
    End Sub
    See if the new table is created.

    Once the new table is created with data, un-comment the SQL and execute it.

  6. #6
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    ssanfu,
    thanks for response.

    Your code works with .xlsx too, that's fine. What about Importing with no duplicates just with TransferSpreadsheet method, is that possible ?

    Method I'm using now is by building query, as you see. in this code :

    Code:
    DoCmd.TransferSpreadsheet acImport, , "SourceTable", Filepath, True
    "Source table" is not source table but a table that need to be pre-created with same data fields, otherwise It's not working. It just creates a new table with same name If It doesn't exist, and It adds only Text field to a table, so Import to destination table doesn't work. So I would rather just import to destination table right away, but without duplicates...

    Problem is that this method produces me some errors, let me explain. Basically I'm Importing 2 fields - number field and text field. Without duplicates means that no row is Imported, If one of the fields allready exists in destination table. In my case, when I run Import code for first time, It inserts all data correctly, but If I run It again, It still wants to import another row that is allready in - this row has 1 column empty and other not.

    In MySQL this Query works fine, but not in Access (this query is different that in post #1):

    Code:
    SQL = "INSERT INTO DestinationTable (DestinationTableField1,DestinationTable.DestinationTableField2) " & _
    "SELECT DISTINCT SourceTableField1,SourceTableField2" & _
    "FROM SourceTable" & _
    "WHERE NOT EXISTS(SELECT * FROM DestinationTable" & _
                     "WHERE (SourceTable.SourceTableField1=DestinationTable.DestinationTableField1 AND SourceTable.SourceTableField2=DestinationTable.DestinationTableField2))" & _
    " AND NOT (SourceTable.SourceTableField1 IS NULL AND SourceTable.SourceTableField2)"
    I've tried many things to fix this, but cannot make It correct. Do you have any suggestion on how to fix Query ??

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    Question

    Maybe this?
    Code:
    INSERT INTO 
       DestinationTable ( Field1, Field2 )
           SELECT distinct 
               SourceTable.Field1, SourceTable.Field2
           FROM 
               (
                 SELECT 
                    SourceTable.Field1, SourceTable.Field2
                 FROM 
                     SourceTable LEFT JOIN DestinationTable ON SourceTable.Field1 = DestinationTable.Field1 AND SourceTable.Field2 = DestinationTable.Field2
                 WHERE DestinationTable.Field1 Is Null
               );

  8. #8
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    Noi, unfortunatelly not. I think I have to do 2 SQL statements in one shot. First Import into table, from which I'll Import into destination table, and then another SQL that will UPDATE or MERGE rows that allready exists, but misses one of the field data. I'm just not sure yet on how that part will be done...

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Now I am confused.....

    First Import into table,
    You are using the Transferspreadsheet command to get the data from Excel to an Access table.


    from which I'll Import into destination table
    You are using an append query to append records from the source table if they do not exist in the destination table.
    (I tested the query I posted in Post #7 and it seemed to work.)


    and then another SQL that will UPDATE or MERGE rows
    What is this query for?



    Try this:
    Code:
    Private Sub cmdImport_Click()
        Dim Filepath As String
        Dim SQL As String
    
        Me.txtImport.SetFocus
        Filepath = txtImport.Text
    
    
        If FileExists(Filepath) Then       ' FileExists is a function to check if file exits
    
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "SourceTable", Filepath, True
    
            'give time for the Transferspreadsheet command to complete
            DoEvents
    
            SQL = " INSERT INTO DestinationTable ( Field1, Field2 )"
            SQL = SQL & " SELECT distinct SourceTable.Field1 , SourceTable.Field2"
            SQL = SQL & " FROM ( SELECT SourceTable.Field1 , SourceTable.Field2"
            SQL = SQL & " FROM SourceTable LEFT JOIN DestinationTable ON SourceTable.Field1 = DestinationTable.Field1 AND SourceTable.Field2 = DestinationTable.Field2"
            SQL = SQL & " WHERE DestinationTable.Field1 Is Null);"
    
            CurrentDb.Execute SQL, dbFailOnError
            '        DoCmd.RunSQL SQL
    
        Else
            MsgBox "File not found. Check file name or It's location !"
        End If
    
    End Sub

  10. #10
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    Now I am confused.....
    Let me explain, exactly...First of all, meanwhile this thread I made this one working:

    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "SourceTable", FilePath, True
    
    SQL = " INSERT INTO DestinationTable (Field1, Field2)" & _
    '" SELECT DISTINCT Field1,Field2" & _
    '" FROM SourceTable" & _
    '" WHERE NOT EXISTS(SELECT * FROM DestinationTable WHERE (Source.Field1 = Destination.Field1 OR Source.Field2 = DestinationTable.Field2 ))" & _
    '" AND NOT (Source.Field1 IS NULL AND Source.Field2 IS NULL)"
    This code successfully Imports all Excel data from Excel into "SourceTable". That data is then Imported into Destination table with no duplicates, and nothing is Imported If both fields are empty - also successfully.

    But :

    My actual fields data type in Destination table is Number (Field1) and Text (Field2). Same is in Excel file, differences are only in column names. Both fields together represent a row - let's say car Model no. and It's Name of Brand.
    Now, this code successfully Imports everything as I said, but there is one more thing missing - If source table has data for both fields (that's one row), and destination table has only one, that missing field in destination table needs to be inserted (or merged, updated). That's my only problem now.


    I'm struggling now with MERGE, but I think Access doesn't support this. And I also think that this Query command couldn't be inserted into upper code, I think I have to do another separately to update missing field.

    I hope you understand now.

    By the way, your code doesn't do nothing in my case, It just imports to source table, but nothing into destination table.

  11. #11
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    I Just don't get this Acces, even a simple UPDATE is not working in my DB.

    This is working :

    Code:
     SQL = " UPDATE DestinationTable" & _
              " SET Field1='111111'" & _
             " WHERE Field1='2222222'"
    But this not - synthax error (missing operator)

    Code:
    SQL = " UPDATE DestinationTable" & _
          " SET DestinationTable.Field1 = SourceTable.Field1" & _
          " FROM DestinationTable, SourceTable" & _
          " WHERE DestinationTable.Field2 = 'Honda' "
    What is wrong, maybe something with my DB ???

  12. #12
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    I finally figured this one out. Correct synthax is:

    Code:
    SQL = " UPDATE DestinationTable" & _
          " INNER JOIN SourceTable" & _
          " ON SourceTable.Field1=DestinationTable.Field2 OR SourceTable.Field2=DestinationTable.Field2" & _
          " SET DestinationTable.Field1=SourceTable.Field1, DestinationTable.Field2,SourceTable.Field2"
    This is part of code where update is done via VBA. First part - Import stays as in post #10. Thanks for help ssanfu.

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

Similar Threads

  1. Replies: 14
    Last Post: 06-29-2015, 06:29 PM
  2. Replies: 12
    Last Post: 10-08-2014, 02:35 PM
  3. Importing Excel file: ADODB, DAO or DoCmd.TransferSpreadsheet
    By Monterey_Manzer in forum Import/Export Data
    Replies: 3
    Last Post: 08-13-2013, 11:34 AM
  4. TransferSpreadsheet - Use first row as field names
    By jgelpi16 in forum Programming
    Replies: 1
    Last Post: 02-23-2011, 11:41 AM
  5. Replies: 2
    Last Post: 02-27-2010, 06:53 AM

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