 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / July 2007Missed the Logic on Excel Importing
I guess I missed the boat on the logic for this one. Immediately upon hitting "Import" in an attempt to import an Excel file containing 7 columns of 'txt' formatted data into AXP, I got a "Type mismatch" error. A search of previous posts indicated that Access did not 'like' most data types being imported except for 'txt' files, so I saved the 'xls' as 'txt' and that didn't work either, receiving the same "mismatch" error message. (Crudely, I can always 'cut & paste' my data into a table I guess). My question is if there is a "Type mismatch" on a truly text data type, converted 'xls' file, what is Access 'matching' the data type against since no table (and therefore no data & structure) was ever presented as the import destination? How can something be 'mismatched' if there is nothing (presented) to 'match' it against? Thx... Earl I assume that you are importing the data to a new table; I also assume that the text data in EXCEL contain some alphanumeric strings, and also have some strings with just numbers in them, and that the "just numbers" strings are within the first 25 rows or so of the EXCEL file. If my assumptions are correct, what ACCESS and Jet are doing is assuming that the "text" data actually are numeric data, and thus all your non-numeric text strings are "not matching" to a numeric data type. Is this what you have in your setup?  SignatureKen Snell <MS ACCESS MVP> >I guess I missed the boat on the logic for this one. Immediately upon > hitting "Import" in an [quoted text clipped - 17 lines] > Thx... > Earl On Jul 29, 3:14 pm, "Ken Snell \(MVP\)" <kthsneisll...@ncoomcastt.renaetl> wrote: > I assume that you are importing the data to a new table; I also assume that > the text data in EXCEL contain some alphanumeric strings, and also have some [quoted text clipped - 34 lines] > > - Show quoted text - The problem here may be that Access is assuming too much about your data. It may be looking at a column that has in its first row the value of "07/29/2007". It assumes that column should be formatted as datetime data type. When it encounters a value of "N/A" in the same column, it reports a data type mismatch. The way around this is to specify the data type of each column in the Import Spreadsheet Wizard. Look for where you can define data type in that Wizard. > The way around this is to specify the data type of each column in the > Import Spreadsheet Wizard. Look for where you can define data type in > that Wizard. Only ACCESS 2007 allows you to specify the data types when importing data from a spreadsheet. The posted indicated that ACCESS 2002 (poster called it AXP) is being used in this situation. SignatureKen Snell <MS ACCESS MVP> Ken, Your assessment and assumptions are correct. The Excel table contains a list of Federal, State and local regulations that govern my business. The columns (fields) deal with the domain (Fed or state or local), the title or 'numbered' designation of the regulation', the part of the business affected by the regulation, a summary of the regulation and a hyperlink field to view the regulation (on the web). So I gather that your assessment is if one of the columns has a number in it (i.e. "PATRIOT ACT 2002"), Access considers the "PATRIOT ACT" as not matching the 'number data type "2002" (or vice versa). How do I overcome that...by removing all numeric-looking data since there's a lot less of those than actual text and attempting another import? How can I just tell Access to ignore data types and I will fix it later? Thx... Earl >I assume that you are importing the data to a new table; I also assume that >the text data in EXCEL contain some alphanumeric strings, and also have [quoted text clipped - 29 lines] >> Thx... >> Earl Actually, "PATRIOT ACT 2002" will be viewed by ACCESS as text data type, not numeric -- this is because it contains nonumeric characters. When I refer to numeric, I mean a text string that contains only numbers or that looks like a number: "1003" "1.788" "4.56E3" "-67" and so on. To assist us in debugging the problem that you're seeing, post here the first 30 rows of data that are in the EXCEL file so that we better understand what the data are in your file. SignatureKen Snell <MS ACCESS MVP> > Ken, > [quoted text clipped - 49 lines] >>> Thx... >>> Earl |
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
|
©2009 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.