Open excel file and "clean it up" then import (1 Viewer)

tmyers

Well-known member
Local time
Today, 11:09
Joined
Sep 8, 2020
Messages
1,090
Why not send the users a properly formatted template? If you allow them to simply do their own thing, you will never have a reliable process since they will simply keep making different errors.
I did think about that. I had a nice little "read me" that I was going to send everyone that outlined various processes of the app. One of them was how to import.

It was a goal of mine to eventually get it all to work in the backend so that way I more or less knew the data coming in was only what needed to be brought in. One because if the code did it, there were less chance of errors from them missing something accidentally. Which may say me time in having to trap various errors.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:09
Joined
Feb 19, 2002
Messages
42,970
Your most productive move is to define the layout clearly. Give them a sample to follow. Then check the input file to ensure it conforms. If it doesn't conform, refuse to import it. You are never, ever going to be able to anticipate what stupid things they might do next week.
 

tmyers

Well-known member
Local time
Today, 11:09
Joined
Sep 8, 2020
Messages
1,090
Your most productive move is to define the layout clearly. Give them a sample to follow. Then check the input file to ensure it conforms. If it doesn't conform, refuse to import it. You are never, ever going to be able to anticipate what stupid things they might do next week.
I see what you are saying. Add a validation to it to verify only good data is being brought in. How would I do that?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:09
Joined
Feb 19, 2002
Messages
42,970
How would I do that?
Rather than importing directly, link to the spreadsheet and do the validation in your append query. If it is more complicated, use VBA code. When I'm doing this type of validation, I generally do the validation in a query and use another query to count the valid records. If 100% of the records are valid, I then use an append query to append the data.

In your situation, you might decide to allow good records to be appended but if you allow partial files to be added, you will have a lot of trouble with duplicates and missing data if they don't actually fix the bad records. All or nothing is the best choice. Then you don't have the complication of dealing with partial record sets.
 

Users who are viewing this thread

Top Bottom