mig1980
asked on
Import Text file to Access
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?
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?
nito8300
Try Macro Scheduler. It's awesome, it can autome any mouse click or keypress and much more. http://www.mjtnet.com/
- 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
- 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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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.
ASKER
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.
I would also not like to create a new table but overwrite the existing table. Let me know thoughts on this.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
A screenshot of your table will be helpful here. Just be sure to not include any privacy information when you post it.
ASKER
Here is a screenshot of the rows mentioned.
Untitled.jpg
Untitled.jpg
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked great. Thank you