X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Recurring Data Import & Query That Data, Access 2016    
post Feb 2 2018, 04:54 PM

Posts: 28
Joined: 15-January 18

I have a report that I would like to automate.

I have Imported a CSV file and fixed all the field names to create the structure I would like to import into.

I have created a Query based on that structure that reformats data the way we need it.

My goal is to create an interface where I can ask the user to point to the new monthly CSV file and then have it replace the rows in the structure and allow my query to run on the new data.

I tried Copying a table and then importing into it but I get errors - even though I created it from the exact same table.

Any thoughts?

Go to the top of the page
post Feb 2 2018, 06:26 PM

Posts: 2,421
Joined: 27-February 09

Could you post your database (only the necessary tables - we don't need anything else), and a couple of sample files? Feel free to obfuscate any confidential information first. I don't think your question is answerable without further details, though.

But doing what you're asking should be pretty straightforward. Once I know what the file(s) look like and what the table structure should be, then this should only take a minute.
Go to the top of the page
John Vinson
post Feb 3 2018, 01:42 PM

UtterAccess VIP
Posts: 4,234
Joined: 6-January 07
From: Parma, Idaho, US

Just one comment... usually one would not delete last month's data just in order to produce this month's report. That suggests that you will NEVER need to reprint a report from the past, or compare data now with data from last year. Have you considered keeping all of the monthly data loads in one table? You can easily use a Query to select this month's data - or ANY month's data. The Access tools (forms, queries, and reports) could all be reused, just with different criteria.

John W. Vinson
Wysard of Information
Go to the top of the page
post Feb 8 2018, 03:39 PM

Posts: 28
Joined: 15-January 18

@John Vinson That's a great idea but not for this project. I am mostly using this as a joiner between data downloaded on one system to create data in a format that the other system needs. Right now the individual responsible takes a few hours of search and replace work to get this done. It is mostly reformatting data from one CSV file another. @MadPiet Here's a sample database and 5 sample files to give you an idea of what I'm looking for.

I'm having a few challenges:

1. If I save the import steps in only wants to import that file. So I did it with "Sample 2018-02-04" to create the table in this DB. What if I would rather that it prompt me for the new file and then use that rather than the same one as last time?

2. Once I get it in there I have a few situations where I want to build an expression to reformat the data. For Example the target query needs a column called FirstName and the download has one with the same name. ie) FirstName: StrConv([FirstName],3) does not work so I had to go rename the source field. I was thinking that I can just use some VBA code to rename the field before I work on it but maybe there is easier way?

3. The Attached text files are all the same but show the challenge. I'm going to download a file and need to create a query based on same field.

Attached File(s)
Attached File  SampleData.zip ( 26.1K )Number of downloads: 1
Attached File  SampleData.zip ( 26.1K )Number of downloads: 0
Go to the top of the page
post Feb 8 2018, 05:37 PM

Posts: 28
Joined: 15-January 18

I also was able to get a button with DoCmd.RunSavedImportExport "Import-to Sample Table" to pull a file in. Is there a way to have this prompt for the current file rather than make my user rename to expected value in that saved import?
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    21st June 2018 - 02:33 PM