Importing from Excel to Access
-
For some reason, I'm getting an error on my Employee Table. Its been easy for all my other tables except this one.
Here is the list of data. I also changed my most picky fields, which i assumed were the boolean yes/no fields and date data types to text. I still had errors. I also changed all my required fields to not required. I don't know what else is the problem.
I've been working on this for hours and I don't know what else to do. Putting in the departments as centers was no problem at all. I do notice that it keeps converting my dates into whole numbers. However, it still doesn't work when i remove the date attribute.
Thanks
FirstName LastName SSN CenterID DepartmentID HireDate Employed Hourly Paula J. Adams 7776 8 14 11/9/1994 yes yes Carlene Adams 7777 10 1 6/1/2001 yes yes Pauline Adams 7778 8 7 1/3/2006 yes yes Frances M. Adams 7779 8 11 2/26/1996 yes yes Arhtur E. Adams 7780 8 2 5/8/2001 yes yes Janean W. Adams 7781 8 7 12/20/2004 yes yes Stella R Adams 7782 8 10 6/10/1996 yes yes Wanda M. Adams 7783 8 7 12/16/2004 yes yes Kettrell L. Adams 7784 8 11 10/1/2003 yes yes Katrina A. Adams 7785 8 11 11/20/1991 yes yes Wanda Adams 7786 9 18 2/4/2002 yes yes Monica L. Adams 7787 8 15 5/3/2001 yes yes Connie Adams 7788 8 9 10/3/1994 yes yes Gizelle M. Adams 7789 8 21 10/18/2004 yes yes Bridgett Adams 7790 8 7 1/2/1996 yes yes Wilneke J. Adams 7791 8 7 10/17/2005 yes yes Brenda M. Adams 7792 8 4 8/18/2003 yes yes Brenda K. Adams 7793 8 4 8/1/2005 yes yes Tamara O. Adams 7794 8 4 10/13/2004 yes yes Tamika Adams 7795 1 7 6/13/2005 yes yes Alvra R. Adams 7796 8 21 11/16/1989 yes yes Shemeika L. Adams 7797 4 20 6/18/2003 yes yes Cynthia Adams 7798 8 12 12/5/2005 yes yes Johnnie Adams 7799 8 16 8/8/1988 yes yes Nancy A. Adams 7800 10 7 12/28/1998 yes yes Doris W. Adams 7801 8 9 3/7/2005 yes yes Eva M. Adams 7802 3 7 3/1/2004 yes yes William H. Adams 7803 8 17 2/9/2005 yes yes Jacqueline Adams 7804 10 9 10/27/1992 yes yes Raquel J. Adams 7805 7 7 3/7/2005 yes yes Tanya R. Adams 7806 7 7 5/20/2002 yes yes Sandra Adams 7807 7 7 7/21/2003 yes yes Sheila D. Adams 7808 5 17 5/18/2004 yes yes Valerie D. Adams 7809 8 1 10/4/1990 yes yes Frederica Adams 7810 8 8 10/14/2002 yes yes Voncile P. Adams 7811 7 12 6/3/1991 yes yes Denise D. Adams 7812 7 9 8/20/2001 yes yes Allison E. Adams 7813 7 7 7/16/1992 yes yes Tracy L. Adams 7814 6 7 11/7/2005 yes yes Lydia Adams 7815 8 15 11/17/1997 yes yes Kimberly M. Adams 7816 8 12 1/23/1996 yes yes Shandra Adams 7817 8 4 5/7/2001 yes yes Stephanie Adams 7818 8 12 9/4/2001 yes yes Ashley L. Adams 7819 8 9 3/1/1998 yes yes Sonya C. Adams 7820 8 1 1/15/2004 yes yes Emma D. Adams 7821 3 7 10/26/2001 yes yes Terrie Adams 7822 8 4 9/15/2003 yes yes Laneeka G. Adams 7823 8 9 6/22/1998 yes yes April L. Adams 7824 8 7 8/6/2001 yes yes Jennifer S. Adams 7825 6 1 1/17/2006 yes yes Tyra Y. Adams 7826 5 9 3/15/2004 yes yes Rosia J. Adams 7827 8 8 10/5/1998 yes yes Sharon E. Adams 7828 8 10 11/9/1998 yes yes Mattie C. Adams 7829 7 1 6/9/1997 yes yes Davida E. Adams 7830 7 7 10/16/2000 yes yes Shandestria T. Adams 7831 2 1 6/1/2004 yes yes Ron'Netta T. Adams 7832 8 12 10/20/2003 yes yes Carassie Adams 7833 8 4 7/2/2001 yes yes Lajuana D. Adams 7834 8 8 6/5/2000 yes yes Rose W. Adams 7835 5 1 5/29/2001 yes yes IiiRobert L. Adams 7836 8 3 9/2/1998 yes yes Angela S. Adams 7837 7 7 11/5/2001 yes yes Carla V. Adams 7838 8 12 7/9/2002 yes yes Leona E. Adams 7839 8 12 3/2/1992 yes yes Beverly Adams 7840 8 8 12/29/1993 yes yes Robert R. Adams 7841 8 2 11/6/2000 yes yes Elmarie H. Adams 7842 8 4 10/3/1994 yes yes Jacalyn A. Adams 7843 8 9 9/23/1996 yes yes Kimberly L. Adams 7844 1 1 12/5/2005 yes yes Mildred K. Adams 7845 7 7 2/19/2001 yes yes Ukawia Adams 7846 7 17 9/17/2001 yes yes Ethel Adams 7847 1 7 11/13/2000 yes yes Joi M. Adams 7848 2 1 8/30/2004 yes yes Tommy E. Adams 7849 8 5 7/8/1985 yes yes Jandee R. Adams 7850 8 16 12/6/2004 yes yes Zatavia J. Adams 7851 1 17 7/21/2003 yes yes Francine R. Adams 7852 7 7 6/2/2003 yes yes Mary Adams 7853 8 21 10/21/1993 yes yes Cheryl Adams 7854 3 4 7/2/2001 yes yes Cassandra Adams 7855 1 1 6/13/2005 yes yes Buffy D. Adams 7856 8 21 1/22/2002 yes yes Jessica Adams 7857 8 7 3/14/2006 yes yes Sylvia L. Adams 7858 1 7 1/4/1999 yes yes Patricia Adams 7859 8 21 8/13/1996 yes yes Lasonya M. Adams 7860 8 1 2/28/2005 yes yes Cynthia V. Adams 7861 8 4 2/21/1991 yes yes Lawrencine A Adams 7862 7 9 7/7/2003 yes yes Charletta Adams 7863 8 21 5/16/1989 yes yes Suzette O. Adams 7864 8 7 1/3/2005 yes yes Cornell Adams 7865 5 3 1/22/2002 yes yes Crystal Adams 7866 6 1 12/19/2005 yes yes Christine Adams 7867 9 15 2/4/2002 yes yes Paulette Adams 7868 1 1 3/21/2005 yes yes Latosha J. Adams 7869 1 7 3/7/2005 yes yes Thelma K. Adams 7870 8 1 1/31/1989 yes yes Valerie E. Adams 7871 8 9 5/29/2001 yes yes Lesia M. Adams 7872 8 9 9/14/1998 yes yes Ramona D. Adams 7873 8 12 8/18/2005 yes yes Rikki R. Adams 7874 6 8 6/22/2004 yes yes Nena L. Adams 7875 8 10 10/5/1998 yes yes Erica Adams 7876 8 10 1/3/2006 yes yes Nikki R. Adams 7877 8 8 6/22/2004 yes yes Renee Adams 7878 7 12 9/16/2002 yes yes Minnie Y. Adams 7879 9 15 1/12/2004 yes yes Tarnita Adams 7880 8 4 6/5/2000 yes yes Sharon Adams 7881 8 21 2/14/2006 yes yes Valerie Adams 7882 3 4 7/14/1997 yes yes Iesha M. Adams 7883 7 9 7/21/2003 yes yes Sharon D. Adams 7884 8 4 8/18/2003 yes yes Vicki Adams 7885 6 8 8/18/2005 yes yes Melissa H. Adams 7886 1 9 7/10/2000 yes yes Kimberly R Adams 7887 8 17 6/4/2001 yes yes Lashanna Adams 7888 8 7 2/20/2006 yes yes Marcel D. Adams 7889 9 18 8/16/2004 yes yes Shantee N. Adams 7890 8 7 3/7/2005 yes yes Lagaile Adams 7891 4 11 11/17/2003 yes yes Shantel M. Adams 7892 7 7 12/20/2004 yes yes Torri L. Adams 7893 8 7 2/4/2002 yes yes Carlie J. Adams 7894 6 7 6/6/2005 yes yes Jacinta Adams 7895 8 21 10/18/2005 yes yes Barbara P. Adams 7896 4 20 3/1/1993 yes yes Timaly M. Adams 7897 8 1 11/8/1999 yes yes Catina Adams 7898 8 5 6/15/2006 yes yes Pamylagene S. Adams 7899 10 8 12/1/1994 yes yes Stephanie L. Adams 7900 3 7 7/2/2001 yes yes Alfreda Adams 7901 10 7 6/1/2001 yes yes Leila-Bates Adams 7902 1 9 10/17/2005 yes yes Latanya D. Adams 7903 8 1 12/6/2004 yes yes Keoki M. Adams 7904 9 18 6/15/2000 yes yes Ruby J. Adams 7905 8 7 11/15/1999 yes yes Audrey C. Adams 7906 6 9 8/25/2003 yes yes Irene B. Adams 7907 8 8 6/10/1996 yes yes Annice R. Adams 7908 1 1 5/16/1991 yes yes Tomeka Adams 7909 8 9 11/14/2005 yes yes Shirla Adams 7910 8 4 7/5/1998 yes yes Lisa Adams 7911 7 9 8/26/1991 yes yes Willidene Adams 7912 7 7 7/6/1998 yes yes Athesias Adams 7913 1 12 3/19/2001 yes yes Cynthia Adams 7914 2 7 3/8/2004 yes yes Katrina Adams 7915 8 21 10/16/1992 yes yes Iris R. Adams 7916 8 4 8/30/2004 yes yes Taryn S. Adams 7917 8 9 9/9/1996 yes yes Donna M. Adams 7918 8 15 7/20/1987 yes yes Jessica Adams 7919 8 7 2/6/2006 yes yes Latangula Adams 7920 4 11 4/4/1994 yes yes Sandra L. Adams 7921 8 6 2/24/2001 yes yes Joyice W. Adams 7922 8 9 1/3/2005 yes yes Katina A. Adams 7923 8 10 7/21/2003 yes yes Latashia K. Adams 7924 8 5 1/25/1999 yes yes Kimberly Adams 7925 1 7 12/3/2001 yes yes Phyllis R. Adams 7926 8 5 8/17/1992 yes yes -
Ok, as an alternative, i simply created a new DB using the attribute headers for the column names. This worked and i was able to change the datatype as before. However,
Now I can't enforce Referential integrity!!!!!!!!!!!!!!!It says: "Relationship must be on the same number of fields with the same datatypes."The fields FK id numbers are the same and the datatype is set to number while the PK number is autonumber.
So this way, the info is in the DB but i can't connect the Department and Center tables.
-
Is the data type Long Integer? AutoNumber is always a Long Integer field.
Specifically, what kind of errors were you getting? Access should have automatically created an Errors table when you did the import.
Have you tried using the Import/Export Wizard, and mapping the fields with more precision? There is an option at one point where you can choose the datatype to import to.
Have you double- and triple-checked your data, to make sure there isn't one row with improper data (like a date field with 'TBD')?
-
Mr. Smith is correct. The true anser to your question lies in the errors table that Access generates. It will list line by line what the errors are. It may be off by one row depending upon if your spreadsheet has a header row or not, but the error message will provide the answer. Could be something as simple as a number with a space after it ('3 ', ie a text field with numeric entries) trying to convert to a numeric field in access.
Hope this helps.
Regards,
Matt -
Access is always very picky on imports. So much that it often ends up doing things to you rather than for you. Check the data types on the columns on the spreadsheet to make sure they are matching the equivalent data types in the database.
If that fails, you can import the spreadsheet into a table where all the columns are definded as Text and large enough for the data. Then run a query to move the data to your other table and do the data type conversions necessary. When you're feeling really fancy, you can run a series of quieries in a macro to validate the data before copying it to your production tables.
-
Thanks guys! One problem i solved by putting the info in by hand. the other problem i solved by restarting the computer because of virtual memory problem. After that and maybe a change, I had no other problems. Wierd how everything works better after you reboot.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply