John-
An alternative would be to build an existing table with the correct field
names, but all the data types set to Text. Import the data into that, then
run code on the imported data that examines the data row by row and corrects
it. You could even spit it out into another table with correct data types
after massaging it.
I'm not much of an expert on Excel, but it seems to me you could add code to
the spreadsheet to validate the data as it's entered. Don't allow anything
but a date in a date column, for example.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of dnwinberry
Sent: Wednesday, January 30, 2013 3:53 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Import Excel Spreadsheet into Access 2003 table
John,
I hope others chime in on this, but my idea would be to send the vendors a
locked down database that they can update that contains a procedure to
create the spreadsheet after they make the updates and emails it to your
customer. You could use the free Access runtime so the vendors wouldn't have
to have Access installed on their PC's.
Doyce
--- In MS_Access_Professionals@yahoogroups.com, "jfakes.rm" wrote:
>
> I have a customer that sends an Excel spreadsheet to different vendors for
employee updates. I developed a spreadsheet template that is used to send
to the vendor.
>
> The vendors are supposed to enter their data into the template using the
specified data types. Then, my customer imports the data into a temp table
then using a process I developed (using a form), the customer runs a series
of steps to clean the data then add the data to the "main table."
>
> The problem is, the vendors are rearranging the layout of the template,
entering text in date fields etc. So, when my customer wants to import the
data into the temp table they get errors (sent to paste errors table). They
then say my process doesn't work. When I sit with them and troubleshoot the
issues on the spreadsheet, they exclaim that the automated process takes
almost as much time to complete as it would to manually enter the data. I
agreed that might be true on small vendors, but when you have to update 500+
records, that would take a lot of time.
>
> Can anyone point me to a good source to learn how to automatically
troubleshoot errors on the spreadsheet (i.e. ignore text in date fields
etc.)? I told my customer to lock the formats on the spreadsheet however,
the vendors continually make changes anyway.
>
> Any help would be appreciated.
>
> John F.
>
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic () |
Tidak ada komentar:
Posting Komentar