Excel 255 Characters
|Dinesh Pradeep Thondaman|
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).
- Open the generated Excel file.
- In the second row (i.e., below the Header or Database field or column name) enter values other than database field value.
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).
- Repeat Step 2, for as many fields you have in your Excel Workbook that has been generated.
- 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).
- 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.
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 Thondaman working as a Freelancer privately and willing to make this business grow so to start a office myself.
User ReviewsTotal of 5 reviews
Thanks for the solutionsWritten by Thomas Panton on March 1, 2009
Dinesh, Your solution solved one of my problems. I'm having another problem with maintaining cell formatting. My client uses Excel to maintain a list of court cases. In one of the fields, they have the name of the court case which contains a mix ...
Excel datatype problemWritten by Aaeda Boodhun on April 4, 2005
Hi Dinesh, I went through your article and it is just great. I have tried the solution you proposed but unfortunately i am still having problem. I am writing you to get some additional help.Here is my case : I have a generated Excel file (provided ...
RE: RE: Excel datatype problemWritten by Vijay Kumar on February 25, 2005
Hi Dinesh,Thanks for your prompt response. Actually my problem is quite different. There is no direct link/relationship between database datatype & excel datatype. To be clear with, say for example Excel column name Quantity. It might have ...
RE: Excel datatype problemWritten by Dinesh Pradeep on February 25, 2005
Hi Vijay, Thank for your message. I can understand your problem, yes you are right that the users cannot be expected to do this. But you can form the second row of the excel through your coding itself. What you can do is, you can identify the first ...
Excel datatype problemWritten by Vijay Kumar on February 25, 2005
Hi,I went through your article, Thats great. I am facing same problem while retreiving excel data(mixed column data). Is there any other possible solution? Since we cant expect the user to change the excel data accordingly. I would appreciate, if ...