468,740 Members | 1,900 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,740 developers. It's quick & easy.

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

547 512MB
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;Surname; DOB; Identity_No
In the Access 2007 table(tblCyclists) the same fields in this sequence are called:
First_Name; Surname;Birthdate;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.
Expand|Select|Wrap|Line Numbers
  1. Dim strPath As String
  2.  
  3.     With Me
  4.          strPath = Left(.lblFile.Caption, InStrRev(.lblFile.Caption, "\"))
  5.         strPath = FSBrowse(strStart:=strPath, _
  6.                            lngType:=msoFileDialogFilePicker, _
  7.                            strPattern:="MS Excel,*.xls")
  8. If strPath > "" Then
  9.              .lblFile.Caption = strPath
  10.              MsgBox "The data hase been successfully imported ", vbInformation, "Data imported"
  11.            Call DoCmd.SetWarnings(False)
  12.  
  13.               Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, "tblCyclist", strPath, True, "")
  14.  
  15.  
  16.  
  17.             Call DoCmd.SetWarnings(True)
  18.  
  19. End If
  20.      End With
Any suggestions please!
May 27 '13 #1

✓ answered by zmbd

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:

Transferworksheet 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"
Expand|Select|Wrap|Line Numbers
  1. Sheet 1, starting in Row 1 Column A
  2. zt1    zt2    zt3
  3. 1    a    app
  4. 2    b    bck
  5. 3    c    cmd
  6. (...)
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

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Table1 ( ID, boo, [money] )
  2.    SELECT linked_Sheet1.zt1 
  3.           ,linked_Sheet1.zt2 
  4.           ,linked_Sheet1.zt3
  5.    FROM linked_Sheet1;
  6.  
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.

3 7310
zmbd
5,400 Expert Mod 4TB
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:

Transferworksheet 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"
Expand|Select|Wrap|Line Numbers
  1. Sheet 1, starting in Row 1 Column A
  2. zt1    zt2    zt3
  3. 1    a    app
  4. 2    b    bck
  5. 3    c    cmd
  6. (...)
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

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Table1 ( ID, boo, [money] )
  2.    SELECT linked_Sheet1.zt1 
  3.           ,linked_Sheet1.zt2 
  4.           ,linked_Sheet1.zt3
  5.    FROM linked_Sheet1;
  6.  
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.
May 27 '13 #2
Seth Schrock
2,956 Expert 2GB
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.
May 27 '13 #3
neelsfer
547 512MB
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.
May 28 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Tom | last post: by
1 post views Thread by Bob | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.