Avatar of mig1980
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?
Microsoft AccessMicrosoft Office

Avatar of undefined
Last Comment
mig1980

8/22/2022 - Mon
nito8300

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

- 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
SOLUTION
IrogSinta

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mig1980

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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
IrogSinta

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.
mig1980

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.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
IrogSinta

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mig1980

ASKER
Here is a screenshot of the rows mentioned.
Untitled.jpg
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mig1980

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?
IrogSinta

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mig1980

ASKER
Good day. I do not see an image attached to your last response. What image are you referring to?
IrogSinta

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mig1980

ASKER
That worked great. Thank you
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.