Appending an Excel file to an Access table

[adrian.stock22]

Hi, All,


Appending an Excel file to an Access table
------------------------------------------

Just now I tried to convert an Excel file (let's call it source_1.xls) into an Access database (Access 2000). The first line of the Excel file represents the field names in the database.

I opened the Access program, clicked on 'Blank Access database', named the desired Access file, and saved it as fred.mdb

I clicked file | get external data | import |
and selected source_1.xls, then clicked import

The following unhelpful since unspecific error msg came up:
"Wizard unable to access information in file xyz. Check that file exists and is in correct format."

Can anyone please tell me what to do?

--------------

A puzzling aspect of this affair is the following:

A couple of weeks ago when first trying to make this conversion from Excel into an empty database I did succeed, but unfortunately it was so easy that I did not see the need for writing down what exactly I did, and now I cannot reproduce it.

But the files left over from that experiment are puzzling and may shed light on my current problems.

For safety reasons I made, at that time, a copy of file source_1.xls (let's call it source_2.xls) and renamed it. Looking at the two source files I can see no difference between them. Both have the same number of records and fields. Both have the field names in the first row.

Now the miracle:
1 source_2.xls (88 kb) is almost exactly twice as large as source_1.xls (43 kb).
2 source_2.xls imports into Access OK without the above error msg.

What on earth could I have done to the Excel file to double it in size and to make it acceptable to Access for import?

Could it have something to do with delimiters that I have forgotten?

-----------------------------------

Now to the overall context of this problem.

I regularly receive a fairly small Excel file (always in the same format) with addresses and I have to append these addresses to my Access database without fuss and error. Esp. I must avoid records becoming scrambled up (e.g. wrong name joined with wrong address by risky pasting into tables).

The file mentioned above is the first Excel file that arrived. I converted it into Access (but see problems mentioned above), then deleted a number of fields and added a number of others.

I took care not to alter the names of the fields which the Excel table and the Access table are intended to have in common.

In future (once or twice a month) I want to import the future Excel files into the existing database, by appending them. I expect Access to import, for each record, the matching fields in the Excel file, and ignore the non-matching fields (i.e. those which exist only in Excel or only in Access).

btw: It is ***not*** practicable for me to expand the Excel table manually to match the Access table.

Question: I there a utility in Access which could accomplish this.

Or: Is there a stand-alone utility (not too expensive) which I could use to convert the Excel table into a common format from which I can easily import it into Access in the way described.

What else can I do to accomplish what I need?

I have a voluminous book, 'Running MS Access 2000' by John Viescas, but do not know where to look for what I want (if it exists). Please refer me to a page if the answer is there?

If there is a Tutorial on the Internet which describes my specific problem, please refer me to it.


Many thanks for your help.

Adrian

 

[spacepro]

You can use the transferspreadsheet command in access where you can tell access which fields in the excel spreadsheet to import, rather than using the import wizard every time.

Ideally the spreadsheet should be formatted so that the data transfers directly into your table.

There are other methods which are not neccesarily the right thing to do.
For example you could use a temp table, import the data, then create a delete query to remove the data that you are not importing into the table then run an append query.

Some of the functionality can be done through vba code, try doing a search on the forum for 'import excel data' and I am sure you will find some more info on this subject.

Any thoughts? Post back!

Hope this Helps

Andy

 

[adrian.stock22]

You can use the transferspreadsheet command in access where you can tell access which fields in the excel spreadsheet to import, rather than using the import wizard every time.

//snip//

Any thoughts? Post back!

Hope this Helps

Andy

-------------------------------

1 Apr 04 (but serious)

Hi Andy,

Thanks for your suggestions, which I will try out.

I have looked up 'transfer spreadsheet' command in the Access help file, but cannot find any instructions where to trigger it (which menu items to click). Can you help? Then I will see further.

Thanks.

Adrian

 

[spacepro]

Hi Adrian,

You can trigger the transferspreadsheet command by various methods.

1)Create a macro and select the transferspreadsheet command and fill in the criteria, then trigger it on the onclick event of your cmd button

2) Write code in VBA on the onclick event of the cmd button, like:

    DoCmd.TransferSpreadsheet acImport, 8, "Yourtablename", "filepath of spreadsheet", True, "Sheet2!A1:E273"

Enter your tablename where the data is to be imported, where is says "Yourtablename".

Enter the filepath of the spreadsheet.
The true part of the code is if the spreadsheet has field names, otherwise change to false.

Specifiy the range if you want.

When you start to enter the code in VBA it will show you the criteria that needs to be set.

I hope this answers you question.

Andy

 

[adrian.stock22]

Hi Adrian,

You can trigger the transferspreadsheet command by various methods.

1)Create a macro and select the transferspreadsheet command and fill in the criteria, then trigger it on the onclick event of your cmd button

2) Write code in VBA on the onclick event of the cmd button, like:

    DoCmd.TransferSpreadsheet acImport, 8, "Yourtablename", "filepath of spreadsheet", True, "Sheet2!A1:E273"

Enter your tablename where the data is to be imported, where is says "Yourtablename".

Enter the filepath of the spreadsheet.
The true part of the code is if the spreadsheet has field names, otherwise change to false.

Specifiy the range if you want.

When you start to enter the code in VBA it will show you the criteria that needs to be set.

I hope this answers you question.

Andy

---------------------------

1 Apr 2004, 23.13 GB time

Hi Andy,

Thanks for your additional information:

I am NOT a programmer but a webdesigner just USING Access for administration and therefore must avoid the VBA option (can't learn a new language now).

I will study the Macros chapter in my Access book over the weekend and try to fit your suggestions into what I find there.

I will let you know how I fare. Wish me luck.

Thanks again.

Adrian

 

[spacepro]

Hi Adrian,

No worries! Hey don't worry about VBA it is pretty straight forward. You just need to copy my code and edit the criteria selections.

Give me a shout if you struggle with it. Good Luck

Andy

 

[shlokmalhotra]

Just copy the data to a new file

Hi,

I had the same trouble, and although I am into Programming, I understand its always not the right solution to program stuff...anyways. This is what I did and it workes for me.

Open the Excel sheet, Copy all data (Ctrl-A, Ctrl-C), open a new Excel workbook, in the new sheet paste the copied data (Ctrl-V), save this new excel file as whatever, and then go into your MS Access database and import, it will work, all the Best

Regards,
Shlok

 

[ggodwin]

Hi Adrian,

No worries! Hey don't worry about VBA it is pretty straight forward. You just need to copy my code and edit the criteria selections.

Give me a shout if you struggle with it. Good Luck

Andy

Spacepro,
I came accross this thread while trying to fix a current problem.
I am using this line of code to import an excel sheet into Access.

DoCmd.TransferSpreadsheet acImport, 8, "SkpiUpdate", "C:\Documents and Settings\dsggodwin\Desktop\SKPI_UPDATE.xls", True, ""

It works great. However, the Excel file must have the header names changed before the import. (Which eliminates the automation)

The excel file has headers with two words while the Access table has these spaces removed. Also, there are special characters in 2 cells that are not in the Access fields.(".","/"," ") Is there a way I can import the data and exclude the header (A1:W1). However, I don't know the range for each import. In fact it should grow by a couple rows each day. So I am not sure that I can name an exact range to import.

any suggestion.

I actually download the data from a website into Excel with Access VBA. However, I am struggling with controling the downloaded file. Some times I can save it and some times I can't.

Here is the code that downloads the data from the website:

Option Compare Database
Option Explicit
Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Public Function SkpiUpdate()
Dim QPR As Object
Dim lnk As Object
Dim TimeOut As String
Dim frm As Object
Dim Start As Object
Dim Finish As Object
Dim drp2 As Object
Dim drp1 As Object
Dim src1 As Object
Dim p1 As Variant
Dim objWB As Object
Dim objExc As Object
 
Set QPR = CreateObject("InternetExplorer.application")
 
    QPR.Visible = True
    
    QPR.navigate "[URL]https://www.portal.toyotasupplier.com/wps/myportal/[/URL]"
    
  TimeOut = Now + TimeValue("00:00:20")  '-- wait maximum of 20 seconds
   Do While QPR.Busy Or QPR.readyState <> 4
      DoEvents
      If Now > TimeOut Then
        MsgBox "Time Out before Login"
        Exit Function
      End If
   Loop
 
   With QPR.Document.Forms("Login")
      .User.Value = "xxxxxxx"
      .Password.Value = "xxxxxxx"
      .submit
   End With
 
   TimeOut = Now + TimeValue("00:00:40")  '-- wait maximum of 10 seconds
   Do While QPR.Busy Or QPR.readyState <> 4
      DoEvents
      If Now > TimeOut Then
         MsgBox "Time Out after Login"
         Exit Function
      End If
   Loop
    QPR.navigate ("[URL]https://www.portal.toyotasupplier.com/skpi/[/URL]")
    
      TimeOut = Now + TimeValue("00:00:05")  '-- wait 1 second for above navigation to take effect
      Do While Now < TimeOut
         DoEvents
      Loop
    
    TimeOut = Now + TimeValue("00:00:50")  '-- wait maximum of 10 seconds
   Do While QPR.Busy Or QPR.readyState <> 4
      DoEvents
      If Now > TimeOut Then
         MsgBox "Did not navigate to SKPI application"
         Exit Function
      End If
   Loop
     
   Set lnk = QPR.Document.Links(3)   ' 3=TMMK-VEH,4=TMMK-PWT,5=TMMC,6=TMMTX,7=TABC,8=NUMMI,9=TMMI,10=TMMBC,11=TMMAL,12=TMMNK
   lnk.Click
   
   TimeOut = Now + TimeValue("00:00:20")  '-- wait 1 second for above navigation to take effect
      Do While Now < TimeOut
         DoEvents
   Loop
   
   QPR.navigate ("[URL]https://www.portal.toyotasupplier.com/skpi/SkpiGatewayServlet?jadeAction=NCPARTS_SEARCH[/URL]")
   
         TimeOut = Now + TimeValue("00:00:05")  '-- wait 1 second for above navigation to take effect
      Do While Now < TimeOut
         DoEvents
      Loop
   
    Set frm = QPR.Document.Forms("form1")
        
    Set Start = frm.all("SKPI_SEARCH_START_DATE_KEY")
    Start.Value = "01/01/" & Year(Now)
    
    Set Finish = frm.all("SKPI_SEARCH_END_DATE_KEY")
    Finish.Value = Format(Now - 1, "mm/dd/yyyy")
    
    Set drp2 = frm.all("SKPI_SEARCH_NC_TYPE_KEY")
    drp2.Item(1).Selected = True
    
    Set drp1 = frm.all("SKPI_SEARCH_NAMC_KEY")
    drp1.Item(p1).Selected = True
    
    Set src1 = frm.all("Submit")
    
    src1.Click
    
    TimeOut = Now + TimeValue("00:00:05")  '-- wait 1 second for above navigation to take effect
      Do While Now < TimeOut
         DoEvents
    Loop
        
    QPR.navigate ("[URL]https://www.portal.toyotasupplier.com/skpi/DownloadNCPartListServlet[/URL]")
    
    TimeOut = Now + TimeValue("00:03:00")  '-- wait 2 minute for above navigation to take effect
    Do While Now < TimeOut
         DoEvents
    Loop
  
   'QPR.navigate ("[URL]https://www.portal.toyotasupplier.com/public/pr_logout.htm[/URL]")
   
   Const strUrl As String = "[URL]https://www.portal.toyotasupplier.com/skpi/DownloadNCPartListServlet.xls[/URL]"
    Dim strSavePath As String
    strSavePath = "C:\Documents and Settings\dsggodwin\Desktop\SKPI_UPDATE.xls"
    Dim returnValue As Long
    returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)
End Function

VBA code with the import finction:

Private Sub Update_Click()
'Delete old records from SkpiUpdate
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete [SkpiUpdate].* from [SkpiUpdate]"

'Import new records from Excel file into Table1
DoCmd.TransferSpreadsheet acImport, 8, "SkpiUpdate", "C:\Documents and Settings\dsggodwin\Desktop\SKPI_UPDATE.xls", True, ""
DoCmd.OpenQuery "CreateScrapRecordTag"
DoCmd.RunSQL "delete * from SkpiUpdate where [ScrapRecordTag] is Null"
DoCmd.OpenQuery "NewRecords"
DoCmd.OpenQuery "UpdateNewRecords"
DoCmd.RunSQL "delete * from SkpiProblemLog where [ScrapRecordTag] is Null"
DoCmd.OpenQuery "DeletedRecordsQuery"
DoCmd.SetWarnings True
End Sub