Import Excel cells to a table but field names are different in both

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • neelsfer
    Contributor
    • Oct 2010
    • 547

    #1

    Import Excel cells to a table but field names are different in both

    Is there a way to import and "hard code" data from Excel where the cell(Excel column) and Access field names are different for the same data.

    In Excel i have the following columns(cells)
    Nickname;Surnam e; DOB; Identity_No
    In the Access 2007 table(tblCyclis ts) the same fields in this sequence are called:
    First_Name; Surname;Birthda te;IdNo.

    I want to import the data from the "sheet 1" in the Excel spreadsheet (Cyclist.xls, into an Access table called tblCyclists. (the Excel column names will always stay the same and can be used as such in VBA)
    I use this code but the field names must be the same then.
    Code:
    Dim strPath As String
    
        With Me
             strPath = Left(.lblFile.Caption, InStrRev(.lblFile.Caption, "\"))
            strPath = FSBrowse(strStart:=strPath, _
                               lngType:=msoFileDialogFilePicker, _
                               strPattern:="MS Excel,*.xls")
    If strPath > "" Then
                 .lblFile.Caption = strPath
                 MsgBox "The data hase been successfully imported ", vbInformation, "Data imported"
               Call DoCmd.SetWarnings(False)
                  
                  Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, "tblCyclist", strPath, True, "")
                  
             
             
                Call DoCmd.SetWarnings(True)
                 
    End If
         End With
    Any suggestions please!
  • zmbd
    Recognized Expert Moderator Expert
    • Mar 2012
    • 5501

    #2
    There are a lot of ways to handle getting the data from excel into access. The biggest issue discussed when this comes up is not so much the fetching of the data in as much as it is the validation of the imported data against datatype and so forth.

    So if you'll stipulate that there are some intrinsic hazards with data validation:

    Transferworkshe et method. Use this method to bring the data into a temporary table and then use and append
    (http://bytes.com/topic/access/answer...te-access-info) see my post ... #4? I've also made a dozen posts going over this method in various other threads.


    Another method (and I've also used this method; however, I now use the transfer worksheet more often):

    1) Using the external data, create a linked table to the excel
    2) Create an append query

    So for example (I'm using really generic names here):

    Excel workbook "Book1.xls" in "My documents"
    Code:
    Sheet 1, starting in Row 1 Column A
    zt1	zt2	zt3
    1	a	app
    2	b	bck
    3	c	cmd
    (...)
    Create a link to this table and the sheet (I just used the External data ribbon)

    Say in our "Database1" we have "Table1" with:
    [ID] (auto number - pk)
    [boo] (just a text field)
    [money] (just another text field)

    Now create your append query

    Code:
    INSERT INTO Table1 ( ID, boo, [money] )
       SELECT linked_Sheet1.zt1 
              ,linked_Sheet1.zt2 
              ,linked_Sheet1.zt3
       FROM linked_Sheet1;
    Run your query.

    So long as "zt1" column data is numeric and doesn't repeat you can append/insert to your heart’s content.
    You should be able to create the same link and append query within a VBA.
    Using either method, you can always place constraints within the append SQL.
    Last edited by zmbd; May 27 '13, 08:21 PM.

    Comment

    • Seth Schrock
      Recognized Expert Specialist
      • Dec 2010
      • 2965

      #3
      Try setting the Has Field Names argument to False and set your range to not include the top row of data (which is where I assume your field names are located).

      Edit: Cross posted with Z. Try his first as he has more experience with this sort of thing.
      Last edited by Seth Schrock; May 27 '13, 08:21 PM. Reason: added edit

      Comment

      • neelsfer
        Contributor
        • Oct 2010
        • 547

        #4
        I think the way to go as mentioned above, is to import the data to a "temp table",change some of the field types (txt to numbers as required - RaceNo1: IIf([RaceNo] Like "#*",Val([RaceNo]),[RaceNo])) in a query and then append it into the main table.

        Comment

        Working...