 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / September 2006Issues getting data from Excel to Access (2002)
Hello, I have several Excel spreadsheets. One column is numeric. I have set it to numeric through the "Format Cells...". When I try to import it, either from the excel workbook or a tab delimited file, the numeric values are eith: 1. arbitrarily excluded (some get in, others don't) 2. arbitrarily quoted (same thing as above) It needs to be numeric. That is why the number keeys were used to enter the data and why the poor users chose to use Excel. Questions - 1. How can we get Excel to recognize number as number? 2. How can we get Access to recognize numbers as numbers? Now that I have asked questions for which no useful answer will likely be forthcoming (in at least a timely manner), might I offer a few comments? This is a straight forward business function. I have been working with computers for a long long time. Some of the worst times have been spent working with Microsoft solutions for users (the dev tools are mostly pretty good). I have to wonder why these problems are found in Release code? Why am I sitting here, looking at a column of numbers that are not being treated like numbers? One approach might be to import whatever you can get into a temporary table in Access, then run queries that "coerce" the data into a final data type/format in more permanent tables. I use "tables" because Excel data is usually "flat", where a well-normalized Access application uses relational data (table) structures. Regards Jeff Boyce Microsoft Office/Access MVP > Hello, > [quoted text clipped - 28 lines] > treated > like numbers? Actually, that was pretty close. I change the column to Text and loaded the rows. Used a Find and replace to remove quotation marks ( one at a time. The command would not get both in the same 'cell'. Had to redefine the data type a couple of times because Access kept truncating the cents. I conditioned the Excel data into a nicely normalized table on each sheet and followed what anyone would have called due diligence in preparing the sheets for import. The frustrating thing was the indeterminate behavior of Access. There was absolutely no condition that was useful in predicting or controlling the behavior. That may be acceptable for some types of fantasy AI but NOT for a database or spreadsheet application. > One approach might be to import whatever you can get into a temporary table > in Access, then run queries that "coerce" the data into a final data [quoted text clipped - 39 lines] > > treated > > like numbers? Actually, I was recommending doing the work inside of Access, importing all the "raw" Excel data first. Regards Jeff Boyce Microsoft Office/Access MVP > Actually, that was pretty close. I change the column to Text and loaded > the [quoted text clipped - 65 lines] >> > treated >> > like numbers? BTW - Thanks for your response. > One approach might be to import whatever you can get into a temporary table > in Access, then run queries that "coerce" the data into a final data [quoted text clipped - 39 lines] > > treated > > like numbers? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.