Import Text file to Access

mig1980
mig1980 used Ask the Experts™
on
Good day everyone. I have an Access database that contains user account information but it is static and once a month I run a few reports from this database.

The way I update it currently is I log into the website that contains this information (third party company), I export the data (username, email, last login date, etc) from the site into a .CSV file (the file also gets populated with time stamp, and some other information at the very top...column titles and date does not start until the 8th row), I then open Access...go into the table and copy each column over one at a time manually from the CSV file to the Access table.

Any idea how I can automate this process?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try Macro Scheduler. It's awesome, it can autome any mouse click or keypress and much more. http://www.mjtnet.com/
Top Expert 2011

Commented:
- here is another freeware tool that you can automate the process of updating the csv file into access: http://www.flowheater.net/examples/access/automatic-csv-import-to-ms-access.htm

- the procedure to create the csv file however you will still to do it the same as you do it now. use the same file name and folder location whenever you generate the csv file so that the tools will pickup the same cvs filename and location for the automation process
Are you talking about automating the process of importing the CSV file into your Access table or automating everything including logging into the website and exporting the data to a CSV file?

If you mean the former (importing CSV into Access) then probably the simplest way for you to do it would be to create a Saved Import (if you have Access 2007/2010).  Just click Text File in the Import section of the External Data tab of the ribbon.  When you complete the import wizard it will ask you if you want to save the import steps.  This Saved Import will now be listed as  a task you could run whenever you click Saved Imports in the Import section of the External Data tab.

RibbonIf you want you can call this Saved Import from code using this line:
DoCmd.RunSavedImportExport "Name Of Your Saved Import"

You then could probably use a delete query to remove the lines that contain the non-pertinent info.

As for logging into the website and exporting the info, you could maybe use the Internet Browser ActiveX but that would take a bit more coding elbow grease.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
I don't mind logging into the website and pulling down the CSV file. I was looking to automate the import process since I currently copy the data a column at a time and overwrite what is there.

Would someone be able to point me to exactly what I would need to do as I am fairly new to this?
Why don't you try my suggestion on creating the Saved Import and then let's see what the imported table looks like so we can remove the invalid records.

Author

Commented:
Ok. I attempted to run the Import and create a new table and Save the Import. The table was created and it looks like the top 8 rows would need to be deleted. The data starts after that. There are 10 fields total that I imported (left one out as I did not need it).

I would also not like to create a new table but overwrite the existing table. Let me know thoughts on this.
If you run the Saved Import again it will ask if you want to overwrite the existing table.
If you do this from code using, it will overwrite it without asking.

Can you post a screenshot of the table it created that shows the top rows?
You could create a button on a form that would run your Saved Import process and then delete the other records that are not part of your data.  

A screenshot of your table will be helpful here.  Just be sure to not include any privacy information when you post it.

Author

Commented:
Here is a screenshot of the rows mentioned.
Untitled.jpg
Assuming all your rows contain a UserId you could create a button on a form and add this to it's OnClick event:

'run Saved Import process
DoCmd.RunSavedImportExport "Name Of Your Saved Import"

'delete non-pertinent rows
DoCmd.RunSQL "Delete * From [NameOfYourTable] Where IsNull(UserId)"

Author

Commented:
I would prefer not to create a form for this. Is there a way to just add a macro to the "Run Macro" list in Access 2007?
There probably is but I don't have access to that version right now.  Why not play around with creating a macro and I'll get back with you later if you still don't have it.  Or maybe another expert can chime in.
Okay create a macro and add these steps as shown in the following image.  Be sure that Show All Actions and Arguments are selected in the Ribbon bar.

Author

Commented:
Good day. I do not see an image attached to your last response. What image are you referring to?
Oops sorry about that.  I don't have access to it right now.  I'll post the image up in a few hours.
Okay now you can create the macro.  Be sure that Show All Actions and Arguments are selected in the Ribbon bar.
Macro

Author

Commented:
That worked great. Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial