Adlerm
asked on
Importing Excel Spreadsheet into Access 203
Hi
I'm attempting to import a number of spreadsheets into individual MS Access Database tables. Basically I have a spreadsheet for each state which I want to load into its respective state table in Aceess. The format of each spreadsheet is the same. A State column identifies what State the spreadsheet relates to. Each spreadsheet has a Consignment Number field - that consists of 10 numeric numbers - however some Consignment Numbers may have a high order zero which is important to maintain, consequently the cells in the Consignment Number column in the spreadsheet(s) are defined as Text (if they are defined as numeric then Excel drops the high order zero - which I don't want). The Consignment Number column in the Access database tables are also defined as Text.
Because of the need to preserve the high order zeros I've run into a number of problems, specifically,
1) when I look at the Consignment Number data in a number of the spreadsheets any Consignment Number that has a high order zero is flagged with a green triangle in the upper left hand corner of the cell while in other spreadsheets high order zero Consignment Numbers are not flagged.
2) When I import spreadsheet(s) that have the green triangle flag the import procedure rejects these high order zero Consignment numbers with a conversion type error
3) Because the high order zero Consignment Numbers are generally the first entries in the spreadsheet they seem to force the import procedure into thinking the column is defined as numeric so that the high order zero records are imported correctly but the Consignment Number records that don't start with a zero are imported with a Consignment Number formated like scientific notation eg 1.2322E43. The only way I've managed to get around this problem is to sort the Consignment Number column in the spreadsheet in decending sequence but as indicated above this causes the high order zero Consignment Number to be dropped due to a conversion failure.
Can anyone shed some light on what I'm doing wrong but more importantly how I can rectify the problem. Why do some spreadsheets allow high order zero numbers while other spreadsheets flag high order zero numbers with a green triangle even though all spreadsheets define this column as Text.
I'm attempting to import a number of spreadsheets into individual MS Access Database tables. Basically I have a spreadsheet for each state which I want to load into its respective state table in Aceess. The format of each spreadsheet is the same. A State column identifies what State the spreadsheet relates to. Each spreadsheet has a Consignment Number field - that consists of 10 numeric numbers - however some Consignment Numbers may have a high order zero which is important to maintain, consequently the cells in the Consignment Number column in the spreadsheet(s) are defined as Text (if they are defined as numeric then Excel drops the high order zero - which I don't want). The Consignment Number column in the Access database tables are also defined as Text.
Because of the need to preserve the high order zeros I've run into a number of problems, specifically,
1) when I look at the Consignment Number data in a number of the spreadsheets any Consignment Number that has a high order zero is flagged with a green triangle in the upper left hand corner of the cell while in other spreadsheets high order zero Consignment Numbers are not flagged.
2) When I import spreadsheet(s) that have the green triangle flag the import procedure rejects these high order zero Consignment numbers with a conversion type error
3) Because the high order zero Consignment Numbers are generally the first entries in the spreadsheet they seem to force the import procedure into thinking the column is defined as numeric so that the high order zero records are imported correctly but the Consignment Number records that don't start with a zero are imported with a Consignment Number formated like scientific notation eg 1.2322E43. The only way I've managed to get around this problem is to sort the Consignment Number column in the spreadsheet in decending sequence but as indicated above this causes the high order zero Consignment Number to be dropped due to a conversion failure.
Can anyone shed some light on what I'm doing wrong but more importantly how I can rectify the problem. Why do some spreadsheets allow high order zero numbers while other spreadsheets flag high order zero numbers with a green triangle even though all spreadsheets define this column as Text.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sorry typo
replace Sales with cells
Aziz
replace Sales with cells
Aziz
ASKER
Hi
Just a couple of observations
1) There seems to be some confusion about whether Access uses the first row of a spreadsheet or the first 8 rows of a spreadsheet to determine the format of columns.
I tired adding a apostrophe to the first row to define text fields but the error kept occurring. I then repeated the process using the first 8 rows but still kept getting errors.
I eventually added an apostrophe to each cell in the Consignment Number column and guess what - everything worked correctly which leads me to my second observation
2) It appears that the rows in a spreadsheet and not presented to Access during the import phase in the same sequence as they appear in the spreadsheet, One can only assume that the import procedure performs an internal sort on the spreadsheet records prior to presenting them to Access. This may help explain why even adding an apostrophe to the first row or indeed to the first 8 rows does not work as row 1 to row 8 may not be the first row presented to Access. I recall reading something about Rushmore? sort but I'm not sure in what context it related to but it help explain some things.
Just a couple of observations
1) There seems to be some confusion about whether Access uses the first row of a spreadsheet or the first 8 rows of a spreadsheet to determine the format of columns.
I tired adding a apostrophe to the first row to define text fields but the error kept occurring. I then repeated the process using the first 8 rows but still kept getting errors.
I eventually added an apostrophe to each cell in the Consignment Number column and guess what - everything worked correctly which leads me to my second observation
2) It appears that the rows in a spreadsheet and not presented to Access during the import phase in the same sequence as they appear in the spreadsheet, One can only assume that the import procedure performs an internal sort on the spreadsheet records prior to presenting them to Access. This may help explain why even adding an apostrophe to the first row or indeed to the first 8 rows does not work as row 1 to row 8 may not be the first row presented to Access. I recall reading something about Rushmore? sort but I'm not sure in what context it related to but it help explain some things.
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
Hi,
Firstly, thanks for your grading.
When you import the records into your database are you using an existing table that may be indexed on a specific column, or are you importing into a new table?
BFN,
fp.
Firstly, thanks for your grading.
When you import the records into your database are you using an existing table that may be indexed on a specific column, or are you importing into a new table?
BFN,
fp.
ASKER
Hi BFN
I'm actuallly importing to an existing table but there is no index on the Consignmaent Number field that is causing me the heartache. There is an index on another field (Vendor Name - duplicates allowed). Before I acutally import the spreadsheet records I delete the records in the Access table so that the Access table only contains those records imported from the spreadsheet.
I'm actuallly importing to an existing table but there is no index on the Consignmaent Number field that is causing me the heartache. There is an index on another field (Vendor Name - duplicates allowed). Before I acutally import the spreadsheet records I delete the records in the Access table so that the Access table only contains those records imported from the spreadsheet.
Thanks.
Would you mind sorting your worksheet rows into ascending Vendor Name order prior to import, and set the first eight rows as described above, to see if the premise is true?
I'm wondering if the "8 row" rule is actually a rumour started by someone at Microsoft on 1 April...
B)ye F)or N)ow,
f)an p)ages.
Would you mind sorting your worksheet rows into ascending Vendor Name order prior to import, and set the first eight rows as described above, to see if the premise is true?
I'm wondering if the "8 row" rule is actually a rumour started by someone at Microsoft on 1 April...
B)ye F)or N)ow,
f)an p)ages.
FYI:
A different issue, but an interesting discussion of the "8 row" rule:
[ http://support.microsoft.com/default.aspx?scid=kb;en-us;189897 ]
"...The Microsoft Excel ODBC driver will, by default, scan the first 8 rows of your data to determine the type of data in each column.
Even though you can change the Rows To Scan value in the ODBC Microsoft Excel Setup dialog box to something higher than 8 (but not higher than 16) this value is not being used by Excel. The Excel ODBC driver uses the TypeGuessRows DWORD value of one of the following registry key to determine how many rows to scan in your data:
• Excel 97
HKEY_LOCAL_MACHINE\SOFTWAR
• Excel 2000 and later versions
HKEY_LOCAL_MACHINE\SOFTWAR
RESOLUTION
To change the number of rows that the Excel ODBC driver scans to determine what type of data you have in your table, change the setting of the TypeGuessRows DWORD value.
NOTE: The following steps will only work if your source Excel file is saved in the Microsoft Excel Workbook file format..."
On the machine I am presently typing upon, the setting for "Jet / 4.0" for the "TypeGuessRows" is 25, but for "Jet / 3.5" it is 8.
Hence, if your machine is set the same, and you are using Jet 4.0 then changing the first 8 rows will then not be sufficient; the first 25 rows will need to be amended.
BFN,
fp.
A different issue, but an interesting discussion of the "8 row" rule:
[ http://support.microsoft.com/default.aspx?scid=kb;en-us;189897 ]
"...The Microsoft Excel ODBC driver will, by default, scan the first 8 rows of your data to determine the type of data in each column.
Even though you can change the Rows To Scan value in the ODBC Microsoft Excel Setup dialog box to something higher than 8 (but not higher than 16) this value is not being used by Excel. The Excel ODBC driver uses the TypeGuessRows DWORD value of one of the following registry key to determine how many rows to scan in your data:
• Excel 97
HKEY_LOCAL_MACHINE\SOFTWAR
• Excel 2000 and later versions
HKEY_LOCAL_MACHINE\SOFTWAR
RESOLUTION
To change the number of rows that the Excel ODBC driver scans to determine what type of data you have in your table, change the setting of the TypeGuessRows DWORD value.
NOTE: The following steps will only work if your source Excel file is saved in the Microsoft Excel Workbook file format..."
On the machine I am presently typing upon, the setting for "Jet / 4.0" for the "TypeGuessRows" is 25, but for "Jet / 3.5" it is 8.
Hence, if your machine is set the same, and you are using Jet 4.0 then changing the first 8 rows will then not be sufficient; the first 25 rows will need to be amended.
BFN,
fp.
Number Stored as Text
Convert To Number
Help On This Error
Ignore Error -----------------> Click Here
Aziz