Use the Registry Editor to fix string errors in tables

Context

A string column in an Excel file with mixed data (numbers and text) may not be imported correctly (the text data may not be imported). The text data can be excluded from the import because Excel estimates the data type of the column. Excel bases its estimate on the data of other records. By default, it looks at 8 records; if none of the records contain text data, Excel assumes the column only contains numbers and text data is excluded when the information is imported to Advanced Database Editor (ADE).

Solution

To correct the error, you can increase the number of records that Excel uses to estimate the data type. This value can be edited in the TypeGuessRows dialog box in the Registry Editor. Follow the work flow below to access the Registry Editor.

In this example, an Administrator used the Export to Excel option and changed some of the Descriptions from numbers to text.

1. The Table Edit window shows an error after editing the table in Excel, and importing it back in.

   

2. To clear the table, right-click in the empty area beside the table, and select Reject Changes > All.

   

3. On the Table Edit dialog box pop-up, select Yes to clear the rows in the Table Edit window.

   

4. Open Windows Powershell, enter regedit, and press Enter.

5. Select Yes on the User Account Control dialog box.

   

6. In the Registry Editor window, navigate to the applicable location (depending on the Excel spreadsheet extension):

   • For an .XLS Spreadsheet - HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > Microsoft > Jet > 4.0 > Engines > Excel > TypeGuessRows.

   • For an .XLSX Spreadsheet - HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > Microsoft > Office > 12.0 > Access Connectivity > Engine > Engines > Excel > TypeGuessRows.

   

   

7. Double-click TypeGuessRows to open the Edit DWORD Value dialog box. Edit the Value Data field to reflect the number of rows you want ADE to scan. The default value is set to 8.

   Notes: As long as you make the row number one more than the amount you need, it will scan the rest of the rows. For example, in step 1 the text descriptions start in row 8. If you enter 8 in the Value Data field, it will scan the remaining three rows with text descriptions.
   
   You can also enter 0 as the special value setting. This means that ADE will scan all of the rows. Depending on the size of the table, this option could cause a significant performance impact.

   

8. Select OK.

9. In ADE, from the Table Edit window, navigate to Table > Import from Excel.

10. In the Open dialog box, select the spreadsheet you wish to re-import, and select Open.

    

11. The table with text and number Description fields will import properly into ADE.