If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Importing Data
I have two Excel spreadsheets with the same columns (fields) .... one
for 2005 and one for 2006. The 2006 sheet imports nicely into Access but the 2005 sheet does not. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. I have visually compared the sheets and can't see the problem. Any thoughts? |
#2
|
|||
|
|||
Importing Data
Hi.
The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. You'll probably find out what the problem is if you use the Import Text Wizard instead. Please see the following Web page for a link to the article, "Spreadsheet Import Wizard Error": http://www.Access.QBuilt.com/html/articles.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ups.com... I have two Excel spreadsheets with the same columns (fields) .... one for 2005 and one for 2006. The 2006 sheet imports nicely into Access but the 2005 sheet does not. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. I have visually compared the sheets and can't see the problem. Any thoughts? |
#3
|
|||
|
|||
Importing Data
Thanks ... That created an "import errors table" with the reason given
as "type conversion failure". I ensured that the offending columns were formatted correctly but it still wouldn't import as a spreadsheet. What creates that error? '69 Camaro wrote: Hi. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. You'll probably find out what the problem is if you use the Import Text Wizard instead. Please see the following Web page for a link to the article, "Spreadsheet Import Wizard Error": http://www.Access.QBuilt.com/html/articles.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ups.com... I have two Excel spreadsheets with the same columns (fields) .... one for 2005 and one for 2006. The 2006 sheet imports nicely into Access but the 2005 sheet does not. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. I have visually compared the sheets and can't see the problem. Any thoughts? |
#4
|
|||
|
|||
Importing Data
Further to that I have checked each column using TYPE() and each column
uniformly contains the correct data type.?? TeeSee wrote: Thanks ... That created an "import errors table" with the reason given as "type conversion failure". I ensured that the offending columns were formatted correctly but it still wouldn't import as a spreadsheet. What creates that error? '69 Camaro wrote: Hi. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. You'll probably find out what the problem is if you use the Import Text Wizard instead. Please see the following Web page for a link to the article, "Spreadsheet Import Wizard Error": http://www.Access.QBuilt.com/html/articles.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ups.com... I have two Excel spreadsheets with the same columns (fields) .... one for 2005 and one for 2006. The 2006 sheet imports nicely into Access but the 2005 sheet does not. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. I have visually compared the sheets and can't see the problem. Any thoughts? |
#5
|
|||
|
|||
Importing Data
Hi.
Further to that I have checked each column using TYPE() and each column uniformly contains the correct data type.?? Which data type? Date/time commonly gives a type conversion failure when the month/day/year format in the spreadsheet is formatted improperly in some of the records. For example, 18/12/2006 would fail because there isn't an 18th month of the year. Try to match up the record numbers that fail (as listed in the import errors table) with the actual row numbers in the spreadsheet to see what Jet is trying to import. You may also try linking to the spreadsheet. The columns with problems converting the data will show #Num! or #Error! in specific records. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ps.com... Further to that I have checked each column using TYPE() and each column uniformly contains the correct data type.?? TeeSee wrote: Thanks ... That created an "import errors table" with the reason given as "type conversion failure". I ensured that the offending columns were formatted correctly but it still wouldn't import as a spreadsheet. What creates that error? '69 Camaro wrote: Hi. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. You'll probably find out what the problem is if you use the Import Text Wizard instead. Please see the following Web page for a link to the article, "Spreadsheet Import Wizard Error": http://www.Access.QBuilt.com/html/articles.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ups.com... I have two Excel spreadsheets with the same columns (fields) .... one for 2005 and one for 2006. The 2006 sheet imports nicely into Access but the 2005 sheet does not. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. I have visually compared the sheets and can't see the problem. Any thoughts? |
#6
|
|||
|
|||
Importing Data
Thanks again for responding ... I did read the QBuilt article which is
very enlightening. There are no dates whatsoever. The only data types are wunsies and twosies. I'll try the link thing to see what I can glean. Regards '69 Camaro wrote: Hi. Further to that I have checked each column using TYPE() and each column uniformly contains the correct data type.?? Which data type? Date/time commonly gives a type conversion failure when the month/day/year format in the spreadsheet is formatted improperly in some of the records. For example, 18/12/2006 would fail because there isn't an 18th month of the year. Try to match up the record numbers that fail (as listed in the import errors table) with the actual row numbers in the spreadsheet to see what Jet is trying to import. You may also try linking to the spreadsheet. The columns with problems converting the data will show #Num! or #Error! in specific records. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ps.com... Further to that I have checked each column using TYPE() and each column uniformly contains the correct data type.?? TeeSee wrote: Thanks ... That created an "import errors table" with the reason given as "type conversion failure". I ensured that the offending columns were formatted correctly but it still wouldn't import as a spreadsheet. What creates that error? '69 Camaro wrote: Hi. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. You'll probably find out what the problem is if you use the Import Text Wizard instead. Please see the following Web page for a link to the article, "Spreadsheet Import Wizard Error": http://www.Access.QBuilt.com/html/articles.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ups.com... I have two Excel spreadsheets with the same columns (fields) .... one for 2005 and one for 2006. The 2006 sheet imports nicely into Access but the 2005 sheet does not. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. I have visually compared the sheets and can't see the problem. Any thoughts? |
#7
|
|||
|
|||
Importing Data
Well, the linked tables had no #NUM or #ERROR in it but two columns of
data didn't show up. From that I saw that the two column headers had leading spaces in them. I retyped each of the six columns and then it imported as a spreadsheet with no problem. Sorry to waste your time but I did learn quite a bit by this little exercise. Have a great holiday! Best regards TeeSee wrote: Thanks again for responding ... I did read the QBuilt article which is very enlightening. There are no dates whatsoever. The only data types are wunsies and twosies. I'll try the link thing to see what I can glean. Regards '69 Camaro wrote: Hi. Further to that I have checked each column using TYPE() and each column uniformly contains the correct data type.?? Which data type? Date/time commonly gives a type conversion failure when the month/day/year format in the spreadsheet is formatted improperly in some of the records. For example, 18/12/2006 would fail because there isn't an 18th month of the year. Try to match up the record numbers that fail (as listed in the import errors table) with the actual row numbers in the spreadsheet to see what Jet is trying to import. You may also try linking to the spreadsheet. The columns with problems converting the data will show #Num! or #Error! in specific records. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ps.com... Further to that I have checked each column using TYPE() and each column uniformly contains the correct data type.?? TeeSee wrote: Thanks ... That created an "import errors table" with the reason given as "type conversion failure". I ensured that the offending columns were formatted correctly but it still wouldn't import as a spreadsheet. What creates that error? '69 Camaro wrote: Hi. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. You'll probably find out what the problem is if you use the Import Text Wizard instead. Please see the following Web page for a link to the article, "Spreadsheet Import Wizard Error": http://www.Access.QBuilt.com/html/articles.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ups.com... I have two Excel spreadsheets with the same columns (fields) .... one for 2005 and one for 2006. The 2006 sheet imports nicely into Access but the 2005 sheet does not. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. I have visually compared the sheets and can't see the problem. Any thoughts? |
#8
|
|||
|
|||
Importing Data
Hi.
The only data types are wunsies and twosies. Since these are numbers, Jet may be trying to import them as text into number columns in the Jet table due to the formatting in Excel. Another technique in troubleshooting is to convert the Excel spreadsheet to a .txt file, then open the file in Notepad. If you do this, look at the rows that fail to import and see if you see anything funny, i.e., double quotes around the number or a single quote (tick mark) preceding the number in that column. Those won't import as numbers, but a complete text string instead. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ps.com... Thanks again for responding ... I did read the QBuilt article which is very enlightening. There are no dates whatsoever. The only data types are wunsies and twosies. I'll try the link thing to see what I can glean. Regards '69 Camaro wrote: Hi. Further to that I have checked each column using TYPE() and each column uniformly contains the correct data type.?? Which data type? Date/time commonly gives a type conversion failure when the month/day/year format in the spreadsheet is formatted improperly in some of the records. For example, 18/12/2006 would fail because there isn't an 18th month of the year. Try to match up the record numbers that fail (as listed in the import errors table) with the actual row numbers in the spreadsheet to see what Jet is trying to import. You may also try linking to the spreadsheet. The columns with problems converting the data will show #Num! or #Error! in specific records. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ps.com... Further to that I have checked each column using TYPE() and each column uniformly contains the correct data type.?? TeeSee wrote: Thanks ... That created an "import errors table" with the reason given as "type conversion failure". I ensured that the offending columns were formatted correctly but it still wouldn't import as a spreadsheet. What creates that error? '69 Camaro wrote: Hi. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. You'll probably find out what the problem is if you use the Import Text Wizard instead. Please see the following Web page for a link to the article, "Spreadsheet Import Wizard Error": http://www.Access.QBuilt.com/html/articles.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ups.com... I have two Excel spreadsheets with the same columns (fields) .... one for 2005 and one for 2006. The 2006 sheet imports nicely into Access but the 2005 sheet does not. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. I have visually compared the sheets and can't see the problem. Any thoughts? |
#9
|
|||
|
|||
Importing Data
Hi.
I retyped each of the six columns and then it imported as a spreadsheet with no problem. Great! You solved the problem. Sorry to waste your time but I did learn quite a bit by this little exercise. You didn't waste my time. You needed help and asked for it, and in the process solved the problem. Good job! Merry Xmas! Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ups.com... Well, the linked tables had no #NUM or #ERROR in it but two columns of data didn't show up. From that I saw that the two column headers had leading spaces in them. I retyped each of the six columns and then it imported as a spreadsheet with no problem. Sorry to waste your time but I did learn quite a bit by this little exercise. Have a great holiday! Best regards TeeSee wrote: Thanks again for responding ... I did read the QBuilt article which is very enlightening. There are no dates whatsoever. The only data types are wunsies and twosies. I'll try the link thing to see what I can glean. Regards '69 Camaro wrote: Hi. Further to that I have checked each column using TYPE() and each column uniformly contains the correct data type.?? Which data type? Date/time commonly gives a type conversion failure when the month/day/year format in the spreadsheet is formatted improperly in some of the records. For example, 18/12/2006 would fail because there isn't an 18th month of the year. Try to match up the record numbers that fail (as listed in the import errors table) with the actual row numbers in the spreadsheet to see what Jet is trying to import. You may also try linking to the spreadsheet. The columns with problems converting the data will show #Num! or #Error! in specific records. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ps.com... Further to that I have checked each column using TYPE() and each column uniformly contains the correct data type.?? TeeSee wrote: Thanks ... That created an "import errors table" with the reason given as "type conversion failure". I ensured that the offending columns were formatted correctly but it still wouldn't import as a spreadsheet. What creates that error? '69 Camaro wrote: Hi. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. You'll probably find out what the problem is if you use the Import Text Wizard instead. Please see the following Web page for a link to the article, "Spreadsheet Import Wizard Error": http://www.Access.QBuilt.com/html/articles.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. http://www.Access.QBuilt.com/html/ex...ributors2.html for contact info. "TeeSee" wrote in message ups.com... I have two Excel spreadsheets with the same columns (fields) .... one for 2005 and one for 2006. The 2006 sheet imports nicely into Access but the 2005 sheet does not. The import Wizard states an error occurs and so the file is not imported but doesn't explain at all what the error is. I have visually compared the sheets and can't see the problem. Any thoughts? |
Thread Tools | |
Display Modes | |
|
|