Link to home
Start Free TrialLog in
Avatar of yoducati
yoducati

asked on

Import CSV and Excel File Data into Existing table in Access

I have a form with a button that when clicked I want the user to be prompted to browse for a file.  When they select the file I want its data to be imported into an existing table in access.  I've seen similar posts but need some more help with the actual implementation and I also need to be able to easily handle importing both file types as they are provided until such time as we import everything from a csv file.
ASKER CERTIFIED SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of yoducati
yoducati

ASKER

The files will both have a set structure that exactly matches the table to which they will be imported.  Right now we manually import an excel file by using the wizard outside of any form.  Initially I'll need to be able to handle both excel, and csv but we will be transitioning to entirely csv in the relatively near future.  Will I need a different procedure for each format or will the specs be the same since the columns and data types will be the same regardless of file format?

Some detail about what I am doing:

The button is used to import electronic detail records of an invoice (excel or csv file).  Those records are going into a staging table at which point a comparison is made to our order details to make sure there is a matching record.  The details from the staging table that have a matching record in the orders table are then uploaded to the invoice details table and marked as approved.  The remaining records in the staging table are the disapproved charges which are referenced in a form letter back to the biller letting them know those charges were disapproved.  

Ill play with this some tomorrow and let you know where I need help.  Im assuming I just need to create a module to store the routine and then call it from the button on the form?  As far as the linked table goes I am actually storing both the approved and disapproved charges from the electronic file in the database once they are uploaded if that makes a difference in the code.
Right now we manually import an excel file by using the wizard outside of any form.
That process gives you an option to save the import spec with a name.
That name is used in

DoCmd.TransferText acLinkDelim, "TheNameOfTheImportSpec", "DataCSV", vrtSelectedItem, False

An Excel spec will not be the same as a CSV spec.
Use the file dialog to allow them to select a file.  Examine the extension of the selected file and depending on what it is, use DoCmd.TransferSpreadsheet .... or DoCmd.TransferText .... to import/link the file.

In either case, you MUST go through the import/link process ONCE manually and save the spec just before the final step by pressing the Advanced button.  Once you have saved the specs, you can reference them in the Transfer methods to automate the task.
Ok, maybe I am making this harder than it needs to be.  I don't mind the "manual" browsing for a file, in fact that's the behavior I want.  I just need it to be within a form.  The file names wont be the same so the users will need to browse for the file as opposed to having the name of the file hard coded.  So here is the behavior I am shooting for behind the button:

'save main invoice record

'prompt user for location of invoice details file to upload

'upload invoice details file to Staging table

'compare details in Staging table to existing Orders and move matching items to the Invoice details table

'unmatched items stay in Staging table  and become the disapproved items for that invoice

'generate and print disapproval letter based upon those items

I want all of that to happen behind that button click.  So in addition to the file type differences, because the names will be different wouldn't it be best to just leave the manual import part and automate the rest of the process for the user?
You only have to do the MANUAL import ONCE!!!!!!  and that is simply to create the import spec.  Sorry, there is no other way to create an import spec than to do it manually ONCE.  After the spec is created, you can automate the process with code.   The user browses to a file and you take the file name and depending on its extension use a Transfer method to open a spreadsheet or a text file.


Were you expecting us to write it for you or did you want help?  Your list of requirements has expanded since the original post.  Can you get through the import process?
Ok, maybe I am making this harder than it needs to be.
It isn't hard, but there's a fair bit involved.
I've posted code for using the FileDialog to select a filename.
You'll need a Reference to Microsoft Office xx.0 Object library to use the code as I have posted it.

I've posted the code needed to programmatically remove a linked table.
I've posted the code to programmatically create a linked table from the filedialog's result.
The rest is stuff I think you have a handle on.

There's details about creating import specs here
https://support.office.com/en-sg/article/Save-the-details-of-an-import-or-export-operation-as-a-specification-6b94e183-2b10-4333-a31a-001fe75321b5?ui=en-US&rs=en-SG&ad=SG

I've warned you about the main gotcha in import specs -- that Access won't respect your decisions about the datatypes of each column if it thinks it is smarter than you, and how to blow up that particular bridge.

So now we are into nuts-and-bolts.
Start with getting an import spec working and saved, and let us know how that goes.
Next is flanging up the FileDialog.
Third is deleting the existing linked table and replacing it.
Finally, there's sewing the whole thing together into a single, bulletproof click.

Lots of scutwork!
Sorry, requirements are the same I was just providing more detail about what I am building as Nick67 requested.  I think some things were getting lost in translation here with regard to "manual" and "automate".  Talking strictly about the importing of the data I WANT the user to have to browse for the file each time.  The files will not have a standard name, nor will they ever be stored in the same place.  I was talking about automating the rest of the process not trying to avoid having to do it manually once to create the spec.  I created the spec which was simple and it imports the data perfectly but it seemed to me like that fixed it so the file name had to be the same, and stored in the same place.  I guess I am understanding now that the "filename" in the transfer method will be specified by the one the user browses to so that makes more sense. I don't understand about deleting and replacing the linked table.  Why do I need to do that if I want to store all the data?  

The import spec works and is saved.
If you try to use the import spec to replace an existing table you are going to get a GRONK!
And since you'll be doing this repeatedly, you are going to want to add linked table after table with new filenames.
So the old linked tables, once you are done with them, get punted.
With a stable linked table name, you can then create append queries or recordset code to do the work of moving the data from the newly linked file to its final locations.

But I do have the impression that these linked files are going to be used once, and then be done with them.  That's correct, isn't it?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@yoducati
The import specs are the hard part -- and much about that process depends upon your Access version.

I think you are working in an A2007+ environment, while I do not develop in A2007+ versions
In my environment, I use the results of the FileDialog to overcopy a dummy file saved in a location that the users neither see nor care about -- and then the filename is always the same, and there's no problems.  That works swimmingly for A2003, A2010 and A2013, as all three of them are presently in use with the same codebase.

I altered my production code to simplify it by leaving the overcopy out.
Perhaps it may be needful still in your case.
Ok, for clarification I had no idea that there was another import method.  I was thinking the "save the import" was the advanced option.  I don't see an advanced button option when I do the manual import but I am in Access 2010 so maybe its different.  As far as the import itself is concerned I never intended for it to replace a table rather append records to it.  Once the import is done the appropriate records would be moved to another table but the rest would remain in the table to which they were imported.  Is that not a sound approach?  The files themselves would not be reused.  Each month we get a new invoice, those records are imported to the staging table,  from there the ones with matching orders would be moved to the table for approved charges and the remaining ones would stay in the staging table forever.  Maybe it would be better to refer to that table as the disapproved charges rather than staging.  At this point I guess I need to figure out how to do the other import method.
I guess we need to be very careful about terminology here.
You have csv and xls files with rows you want to import.
There can be many ways to do that.

On my end, the way I do it is I create a linked table out of the csv file whose data I wish to use.
I then have append queries that take rows from the csv file and place new rows into permanent tables.
When I am done with the linked csv file, the linked table is deleted, and I no longer care about the csv file

So the careful part
As far as the import itself is concerned I never intended for it to replace a table rather append records to it.
The import I speak about creates a linked table out of the csv file.  I replace that linked table over and over again, and append records to a permanent home.

Once the import is done the appropriate records would be moved to another table but the rest would remain in the table to which they were imported.
If you have operational reasons to want to do so, sure, there's nothing wrong with that.  You'll be taking records from linked csv files, first appending them into an 'imported' table, and then appending them to a 'disposed of' location, too.

In Access 2003 -- but there may be changes in A2007+ -- you created the import specs as part of the 'create linked table wizard'
Ok.  I think I am definitely confused about the terminology and "linked" is the biggest confusion for me.  When I think of "linked" I think of a front end table linked to the back end.  That's not what I am doing with the csv files.  When I use the import wizard now it gives me an option to append the records to an existing table.  I want that functionality without the user having to state that they want to append the records and select the table.  I want to automate that part in the sense that they would still be prompted to browse for the file, but my code would handle the appending to the appropriate table.  At that point the rest of the process would continue behind the scenes to move the appropriate records and generate the letter etc.  I thought this would be much more straightforward since there is a wizard for it already.  Could you tell me what code I would need in the module, and the code behind the button?  I'll be able to get the queries and report to do their thing once I know where to insert that part.  Right now Im having a hard time understanding how to get the import functionality but I think once I see that part I'll be able to work out the rest of it.  Thanks for all the help.  Sorry this is getting so convoluted.
When I think of "linked" I think of a front end table linked to the back end.
That's one kind of linked table.  There are many others.
You can create linked tables to other Access backends, SQL Server, Oracle, Excel, csv and textfiles, and at one time even Exchange and Outlook files.

That's not what I am doing with the csv files.
Not presently, but the fully automated solution I am suggesting will require it.
@PatHartman has indicated that the Saved Import process you are presently using only permits the exact file to be re-imported.
Not that that is a deal-breaker.
You could use the FileDialog's result to overcopy the file expected by the Saved Import -- but many folks have had grief with getting that process fully automated, because MS hasn't really provided the tools in VBA to do so.

but my code would handle the appending to the appropriate table.
Indeed.  My suggestion is that the csv be made into a linked table, and then you can do whatever you like.
The Saved Import wizard has the advantage of taking the csv data and immediately permitting the append, and cleaning up after itself -- but you cannot fully automate it.

I thought this would be much more straightforward since there is a wizard for it already.
You've run across another situation where MS is creating nice shiny baubles for the end-user and giving no joy to the devs.

This is the process you need to go through to use and reuse a named spec in
 DoCmd.TransferText acLinkDelim, "TheNameOfTheImportSpec", "DataCSV", vrtSelectedItem, False
https://support.office.com/en-sg/article/Import-or-link-to-data-in-a-text-file-d6973101-9547-4315-a8f8-02911b549306#bmltf
The Excel Import does not offer an Advanced option and it also cannot use a saved spec.  So, just use the TransferSpreadsheet method.  If you use the method you are using, you are forced to always use the same file name and location.  With DoCmd.TransferSpreadsheet..... you can specify the file name at runtime that you got from the textbox used in the browse.

The Text import does offer the Advanced button.  Just be sure to press it on the last screen so you can save your work.  You then use the saved spec in the TransferText method.

Mike is suggesting that you use links.  That is fine and that is what I usually do.  The problem is that if you delete the linked tables, you can break any querydefs that reference the deleted table.  But as long as you are careful to not delete the link, you'll be fine and it is certainly better since linking won't bloat the database as much as importing all the data.


User generated image
Im probably doing something wrong.  I put the code for the file dialog in a module and call it from the button on the form.  When I click the button I get an error saying "invalid outside procedure" on this line

Set fd = Application.FileDialog(msoFileDialogFilePicker)
Does the code compile?
you will need a reference to the appropriate MS Office xx.0 Object libraryUser generated image
The reference I have is Microsoft Office 14.0 Object Library

When I compile the code it now says "user defined type not defined" on this line

Dim fd As FileDialog
Try this one.
Does it work?
FD.mdb
Yep
Ok, so check the references in the sample, and then in your project and check that all the ones in the sample are in your project, too, and compile.
There were two missing references.

Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library

I added the ActiveX reference successfully, but when I try to add the other one I get an error message that says

Name conflicts with existing module, project, or object library.
That's ok,
You are likely dealing with an accdb and DAO has been renumber/renamed to
Microsoft Office 14.0 Access database engine Object Library.
Ensure that Microsoft ActiveX Data Objects 2.1 Library is lower in the list than it.
Then try to compile
When I compile the code I still get this error "user defined type not defined" on this line

 Dim fd As FileDialog
Damnable version changes!
Look here for A2007+ syntax
http://stackoverflow.com/questions/1091484/how-to-show-open-file-dialog-in-access-2007-vba

Looks like
Dim fdAs Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

is what's needed in an accdb -- because you said my sample worked fine.
Could I be missing something somewhere else?  I still get the same error.
Here is what my references look like...
Capture.JPG
You don't have it added yet!
It's NOT the MS Access 14.0 Object Library
You need that one, but you need to add
The Microsoft Office 14.0 Object Library
Not sure if this matters but when I type

Dim fd As

and the helper window pops up to help auto complete the code "office" is not available.
Lol.  Oh.
Its working now.  So when we start using csv files I just need to switch the method from the transferspreadsheet to the transfer text method?
I'd probably build and test the conditional stuff right now.
It's easy enough to go

Select Case Right(WhateverTheUserSelectedForAFilePath,4)
     Case ".csv"
              'your csv code options
     Case ".xls"
             'your old-school Excel file options
     Case "xlsx"
             'New School Excel
     Case else
             Msgbox "Forget it, pick as proper file."
             'bailing out code
End Select

.csv uses DoCmd.Transfertext and .xls uses DoCmd.TransferSpreadsheet
They each have wrinkles particular to themselves in terms of options.
Ok. So do both .xls and .xlsx use the docmd.transferspreadsheet or is there another one for .xlsx?
No, they both use it, but there'd be a different value of AcSpreadSheetType  for each one
https://msdn.microsoft.com/en-us/library/office/ff196017.aspx
You need acSpreadsheetTypeExcel9  for .xls and probably acSpreadsheetTypeExcel12 for .xlsx
There's other options to that command, too.
https://msdn.microsoft.com/en-us/library/office/ff844793.aspx
Ok it sounds like I need to get us switched over to just a csv file sooner rather than later.  Thanks for all the help!  This is working so I will close this out.  I thought of another question I am going to likely have when I get to another piece of the automation so I will post a new one with a link to this when I do.  Thanks again!
As a quick follow up, is there a way to trap the error about key violations and suppress it or provide my own?  I get the error message that says something along the lines of "x number of records weren't imported and x number were lost due to key violations do you wish to continue etc".  I get the error even though the number for both is "0" and all the records are imported perfectly.  I added some code to compare the sum of the invoice details imported to the parent invoice total to make sure they match just in case the user imported the wrong file.  If they don't match the code should delete the records that were just imported.  I think I have that written correctly in VBA but it doesn't even run because of this other error that stops the rest of my code after the records are imported.
You can turn warnings off but you MUST be certain to turn them back on.  This is so important that I use a macro because I always want to do two steps.  The first macro turns the warnings Off and it turns the hourglass on.  Then the other macro turns the warnings back on and the hourglass off.  The visual clue of the hourglass prevents me from accidentally leaving the warnings off if I break in to the code during testing and it doesn't get to the end of the procedure.  If you leave the warnings off, Access will not warn you if you make design changes to an object and forget to save it before closing the object.  It will silently discard the design changes which could be very bad for you.
I think...
That what you are seeing are the SQL action query warnings
They can be toggled

DoCmd.SetWarnings False
DoCmd.SetWarnings True

*****************************************

Unusually important warning!

*****************************************

Using SetWarnings has broad effect.
You really, REALLY, REALLY want to ensure that False is only in play for the minimum amount of time required.

Code like this
        'kill existing records in tblWeldProcData
        DoCmd.SetWarnings False
        db.Execute "DELETE tblWeldProcData.* FROM tblWeldProcData;", dbSeeChanges
        'run the append query
        Set qdf1 = db.QueryDefs("qryAppendWeldProcData")
        qdf1(0) = Me.Parent.JobID
        qdf1.Execute
        DoCmd.SetWarnings True
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblWeldProcData", "c:\prod\tblWeldProcData.xls", True

As soon as you don't need to suppress the warnings, turn them back on.
And in any procedure where you use it, make sure that they are turned on again in your error handler.
Much grief can be avoided (like whole tables deleted by accident) by NOT mucking with that setting until you need to, and reverting to default immediately.
Thanks for the help guys.  Good to know about the risks as well!  I have only one line that runs in between turning the warnings off and back on again.  The rest of my procedure works great now that the popup window about the key violations doesn't show up.