If you trying to retrieve any data from Excel using the Microsoft Excel 97 ODBC Driver, the columns that have more than 255 characters are truncated to maximum of 255 Characters.  Excel Driver scans first eight rows by default (although it can be changed up to 16 by changing system's registry) in order to identify the type of data, so if the first few rows have data that is less than 255 characters then the rest of the rows will be truncated to 255 characters.

Problem reported by – Microsoft. See Microsoft's How-To article Q189897.

Since excel driver scans first eight rows in the Excel file before it is being retrieved, the data is retrieved based on these first eight rows or 16 rows (the number of rows that has to be scanned can be changed by System’s Registry). This problem can be solved by saving the Excel file in Excel Workbook file format rather than Microsoft Excel 97-2000 & 5.0/95 Workbook file format.  Saving the Excel file as Excel Workbook file format alone will solve the problem. These are the steps that have to be followed to avoid the data being truncated to 255 Characters.

Generate the Excel file using your existing code (I have used Asp.Net and VB.Net to Export and Import the Excel files.  I would suggest to use .Net to Export and Import the Excel Files).

1. Open the generated Excel file.
2. In the second row (i.e., below the Header or Database field or column name) enter values other than database field value.
        For eg:
        If your database field of type numeric (int, numeric etc.,) then that particular column should be entered or generated with Numbers upto the maximum length of the database field (say if the field (id) is of type int or numeric and length 8 then enter or generate any 8 different numbers eg: 15124322).
        If your database field of type alphanumeric (varchar, char, nvarchar etc.,) then that particular column should be entered or generated with characters and numbers upto the maximum length of the database field (say if the field (name) is of type nvarchar or varchar and length 25 then enter or generate any 25 characters and numbers eg: asbc213adfadsf324a234saga).
3. Repeat Step 2, for as many fields you have in your Excel Workbook that has been generated.
4. Define the range (Say your data starts from A1 cell to C5 cell, then select all the cell from A1 to C5.) for the Excel file (Click Insert Menu -> Name -> Define.  In the Names in workbook textbox type the name for the range as your wish eg Products).
5. Then, finally save the Excel file in "Excel Workbook" file format.
   
   Note: By following the above mentioned steps the data will not be truncated to 255 Characters.

Datatype

One more thing is that there is another problem while retrieving data from Excel. When data is retrieved from the Excel file, the data does not retrieve the correct/exact data type. The data type for each column is determined based on the values (scanning of rows by excel driver) of the first eight or 16 rows.

The problem is due to the fact that Excel Driver scans the first eight or 16 rows, the format of the data (for each particular column) in the first eight rows should be the same for all the rows. If the data in the first eight rows is numeric or numbers and the next few rows is alphanumeric (characters and numbers) then these few rows would not be retrieved or not retrieved in the correct datatype.

Eg:  Say your database field or column (say weight) that can have values like 78, 78kgs, 100pounds. Then, if the first eight rows has values in numbers (say 87, 35, 74, 75, 64, 70 etc.,) and the following row(s) has values in alphanumeric (say 78kgs, 100pounds etc.,) then, Excel cannot retrieve these row(s) since the values are in alphanumeric and the Excel driver has already scanned the first eight rows and identified the values might be in numeric.

Both the issues can be solved with this solution. i.e., The same solution can be used for both retrieving the data from the Excel file for which data has more than 255 characters in length (Solution : save the excel file in Excel Workbook file format), and also for data that has to be retrieved in the correct/exact data type (Solution : follow the steps as mentioned above).  I think this might be the best possible solution. Any type of comments for this issue is welcome.

Dinesh Pradeep

Dinesh Pradeep Thondaman working as a Freelancer privately and willing to make this business grow so to start a office myself.

See All Postings From Dinesh Pradeep >>

User Reviews