Understanding Excel Limitations for Data Integration

Introduction

Every Excel user should have some understanding of how it stores data. These are simple concepts, but they are often overlooked due to negligence, not put into practice, or intentionally misused due to lack of time or simply because the data "looks nicer" with these bad practices.

Additionally, users should also be aware of the numerous "glitches" and strange behaviors of this Spreadsheet software.

These bad practices, along with Excel's "glitches" and strange behaviors, combined with users lacking mathematical or IT knowledge, often lead to frequent discussions and problems in understanding results and data analysis.

Therefore, this chapter was created to "educate" the vast majority of users who believe they hold the absolute truth because they have a series of very nice numbers stored in "an Excel database" (a phrase that is laughable, yes, but users say it believing themselves to be the very Bill Gates). A Spreadsheet is NOT a Database. You can search for both concepts on the internet.

Of course, this is not an Excel course. It will simply describe a series of necessary concepts so that data loading works as conveniently as possible. Additionally, certain bugs and strange behaviors are described to understand what happens when certain errors occur during data upload to a database, regardless of the loader you choose to use.

Cell Formatting

One of the most critical challenges is Excel's 'weakly typed' nature. Unlike SQL databases that enforce strict schemas, Excel allows mixing numerical values, text strings, and formula errors within the same column. This lack of structural validation leads to integrity failures during migration, as fields defined as INTEGER or DECIMAL in the destination will reject any cell containing incidental text or inconsistent formatting.

The first concept to understand is the Cell Format. Excel has a series of predefined formats that can be applied to the cells of an Excel Book, and it also allows users to create custom formats. These formats are not just for presentation; they also influence how Excel stores the data internally and how it should be interpreted.

The selection of the "Cell Format" for a data greatly influences how Excel stores and presents a given data. We refer to the "Number" tab within the dialog box that appears when formatting a cell.

Starting from a Windows 10 operating system with a regional setting for Spanish (Spain). This implies that it defines as "Decimal Symbol" the comma "," and as "Thousands Separator Symbol" the period ".". Exactly the opposite of what is defined in Anglo-Saxon cultures, and which is the source of numerous errors, either by users or by Microsoft, as described in "The Strange Case of OLEDB with Decimal and Currency". This configuration also defines a default short date format: "dd/MM/yyyy" (day/month/year).
We also start with an Office 2019 in Spanish (Spain) installed on the same operating system.

As a radical example of the influence of this format, if a date is noted in a cell with "General" format, for example, 02/01/2023, Excel takes it as a date type data, and automatically changes the cell format to "Date" and internally stores the number 44928. If this cell format is changed to "Text", that number appears, which may seem illogical to the user.
Obviously, these kinds of details must be taken into account when generating Excel Books automatically or programmatically.

Excel To Database works with the data stored internally and the cell format to deduce how to interpret them correctly. Thus, in the previous case, the data 44928 will only be interpreted as a date if the cell format is "Date", and its interpretation will be unquestionable. If the format is other than "Date", for example "Text", it will be interpreted as a string of characters.
If, on the other hand, the data "02/01/2023" (without the double quotes) is stored internally, it is reasonable to assume that it corresponds to a cell whose format is "Text", meaning the data is a pure and simple text. If this string needs to be loaded into a date-type column, a text-to-date conversion will be required. However, the cell format does not inform how the data should be interpreted, that is, it is unknown whether the 02 corresponds to the month, day, or year. This will result in data loading errors due to the imprecision in data interpretation, usually caused by the user's negligence or bad practices or the program generating the Excel Book.

With the "Decimal Symbol" and the "Thousands Separator Symbol", even stranger things happen, which would require a dedicated treatise.
For cells with numerical data and "General" or "Number" cell format, for example, Excel displays the data according to the regional configuration defined for the operating system. However, internally, the format in which the data is stored does not have to match the presentation in the cell, as is the case with dates. This means that changing the cell format causes things similar to those described for dates to occur.

As a general rule, it is recommended that the user be especially careful with the format assigned to the cells of the Excel Book they want to load into the database. Negligence and bad practices have no place with this loader, and probably with any other.

Excel Precision

Another problem Excel has is the precision or accuracy of the data it stores. The details of the problem are described in: Floating-point arithmetic may give inaccurate results in Excel - Office | Microsoft Learn and Numeric precision in Microsoft Excel - Wikipedia.
Without delving into the topic, and to get a small idea of the problems detailed in those articles, let's think about what happens with numbers that in base 2 have an infinite number of digits (in base 10 it could be 1/3 = 0.333333333333....). Simplifying the problem and how Excel works, we find that Excel stores the first 15 significant digits of these numbers. The consequences of this fact are unexpected, and among them is the loading of this type of data with certain data providers (OLE DB, ODBC, ...) and/or drivers (drivers for MS Access, SQL Server, ...).

This problem has a difficult solution. Microsoft provides a solution for performing calculations: Floating-point arithmetic may give inaccurate results in Excel: Method 2: Precision as displayed, How to Set Rounding Precision In Excel and in What does 'Set precision as displayed' do in Excel?.
However, this solution does not help with the problem posed during data loading.

Normally in these cases, the data provider rounds or truncates the data to be inserted into the database. However, depending on the driver and the database data type, the rounding applied may not be the same. In some cases, it will be MidpointRounding.AwayFromZero, while in others it may be MidpointRounding.ToEven. See MidpointRounding Enumeration.
When Excel To Database applies rounding, it will always be of the type MidpointRounding.AwayFromZero.

It should also be noted that Excel To Database will attempt to load the data contained in the Cell into one of the .NET Data Types. This will create two different precisions: Excel's on one hand, and .NET's on the other.
In future versions, this functionality will change to load the data into the appropriate data structures for each database.

In Excel specifications and limits the limits of Excel are specified.
In this information, we are puzzled by what the "Number allowed by formula" is and why it is "larger" than what Excel allows. Additionally, we wonder how that number is handled.
We assume that these numbers refer to the precision that Excel internally handles for performing calculations, but they are not values stored in the cells. Thus, these limits will not be considered in Excel To Database.

In the Summary of Data Types (Visual Basic) the limits of the .NET data types are specified.
These are the actual limits that Excel To Database has, since the values stored in Excel cells must be loaded into these data types.
The limits of the "Double" data type are very similar to Excel's limits for "Number allowed by formula". That's why the assumption in the previous paragraph was deduced.

As can be seen, the limits accepted by Excel To Database are greater than those allowed by Excel. Thus, if it is desired for Excel to hold data (numerical or time) larger than allowed, they must be placed in cells with "Text" Cell Format, considering that Excel To Database recognizes the period "." as the "Decimal separator" and the comma "," as the "Thousand separator" (the convention defined in Anglo-Saxon cultures).

Excel Dates

Dates and Excel do not get along very well. Due to decisions made in the early days of Excel development, it is quite complicated to understand what happens with dates. It is recommended to read the following articles:

• My First BillG Review
• Dates And Times In Excel
• Eric's Complete Guide To VT_DATE
• Differences between the 1900 and 1904 date systems in Excel Excel To Database will not work correctly with the 1904 option. This functionality will be added in future versions.
• Excel incorrectly assumes the year 1900 is a leap year

Besides the problems mentioned above, the behavior of Excel is completely different from that of VBA (Visual Basic for Applications). As an example, you can consult the following article: Integers and dates map differently in VBA than in the Worksheet

To all these problems, we must also add the problems that data providers have:

• .NET (System.Data.SqlClient, Microsoft.Data.SqlClient, Oracle Data Provider for .NET, ...)
• OLE DB (Microsoft.ACE.OLEDB.16.0, Microsoft.ACE.OLEDB.12.0, Microsoft.Jet.OLEDB.4.0, MSDAORA, SQLOLEDB, MSOLEDBSQL, MSOLEDBSQL19, SQLNCLI11, OraOLEDB.Oracle, ...)
• ODBC ({Microsoft Access Driver (*.mdb, *.accdb)}, {ODBC Driver 18 for SQL Server}, {ODBC Driver 17 for SQL Server}, {SQL Server}, {SQL Server Native Client 11.0}, SQORA32.dll, ...)
• And the problems of the .NET classes associated with data providers OLE DB and ODBC (System.Data.OleDb, System.Data.Odbc)

the mess with the dates is completely guaranteed.

Lastly, it should be noted that the Cell Formats for Date that begin with an "*" are formats dependent on the regional date and time settings specified in the operating system. Since the tests were conducted with an operating system configured with the "Spanish (Spain)" regional setting, the Short Date format is: "dd/MM/yyyy" (day/month/year). This is the format that Excel will default to for cells with the "Date" Cell Format.

Date format ambiguity represents a silent risk. A value like '01/02/2026' is technically valid for both DD/MM/YYYY and MM/DD/YYYY formats. Without clear pre-processing logic, the system has no way to discern the user's intent, potentially leading to a database with corrupted timelines that are extremely difficult to audit after the fact.

• Time Data. Reference Date: 12/30/1899.

  When time data is entered in Excel in "Time," "Custom," or "Text" format, the reference date 12/30/1899 is added by default. However, when the "YEAR" function is applied, it shows the year 1900.
  The "YEAR" function does not work for dates before January 1, 1900. Quite a slip-up, Microsoft.

  In MS Access, the reference date 12/30/1899 is also used to display only "Time" data.

  When importing time data from Excel to MS Access, SQL Server, or Oracle with Excel To Database, the import behavior depends on the Cell Format and the data type of the column where the data is to be loaded.
  For MS Access, when Excel To Database interprets a value as time, it automatically adds the reference date (12/30/1899), even though another date is stored internally in Excel.
  For SQL Server and Oracle, the field must accept "time only" data to load Excel data interpreted as time.

• 1900 The Year That Wasn't a Leap Year.

  It may seem that the major error included in Excel for the year 1900 also exists for years ending in "00" that are not divisible by 400 (which were not leap years: 1800, 1700, ...). The year 2000 was a leap year since it is divisible by 400.

  "Apparently," Excel accepts these dates without problems. It does not show any error for February 29 of the years 1900, 1800, 1700, ... However, there is a subtle difference: 02/29/1900 is stored in Excel as a number, which means it is interpreted as a correct date (dates in Excel are stored as numbers). Simply change the cell format to "General" or "Number," and the number 60 will appear (number of days elapsed from the reference date 12/31/1899 to 02/29/1900 if it had existed). This does not happen for 02/29/1800, 1700, ..., indicating that these are not valid dates for Excel.
  This behavior was fixed in the early days of Excel development, aiming for compatibility with Lotus 1-2-3 spreadsheets that dominated the market. Microsoft's different strategies to maximize profitability over quality are well known.

  Excel To Database does not contain any specific programming for date calculation but uses the method DateTime.FromOADate(Double) for date calculation with a correction for the days from January 1, 1900, to March 1, 1900. Excel assumes that the year 1900 was a leap year when it was not, and this correction is necessary when interpreting the data. See: Converting Double to DateTime?
  Thus, Excel To Database will translate 02/29/1900 as 02/28/1900 and that will be the date inserted into the database.

  In MS Access Tests: Dates, you can see Excel's behavior for these dates (range "B2:D2"). While the data contained in the range "B2:C2" cannot be converted to numbers, they are not valid dates for Excel, but the content of cell D2 is considered a valid date.
  Excel should indicate somehow that the data entered in the cell is not valid date data. This way, the manual procedure of changing the Cell Format to "Text" to check if the date is valid or not would not be necessary.

• Excel To Database Date Processing

  When Excel To Database needs to load a value into a Date and/or Time field, the following steps are performed:

  • Excel recognizes the value as a Date

    1. Information is obtained from the Cell Format to refine the data: It is only a Date, only a Time, or a Date and Time.
    2. The value is converted to the appropriate .NET data type (DateTime, DateTimeOffset, or TimeSpan).
    3. If the value meets the restrictions imposed by the Database and the Driver (For example, see: ODBC and Anglo-Saxon Chauvinism: 1753):
       • The value is assigned to the DataTable (structure that holds the data for subsequent loading into the database).
       • Data loading is performed.
    4. If step 2 or 3 fails:
       • "No conversion errors" option NOT checked: the string representing the date is directly assigned to the DataTable.
         It is very likely that a Type Conversion Error will occur, preventing the data loading phase from being initialized.
       • "No conversion errors" option checked: the default value specified in the Mappings Panel is assigned to the DataTable.
       • Data reading continues, and if no errors occur, the data loading phase begins.

    Throughout this process, unexpected errors may occur, potentially stopping the data reading or data loading phase. Excel To Database will inform you accordingly.

  • Excel does NOT recognize the value as a Date

    1. Information is obtained from the Cell Format to refine the data: It is only a Date, only a Time, a Date and Time, or no information is available.
    2. An attempt is made to convert the value to one of the .NET data types (DateTime, DateTimeOffset, or TimeSpan).
    3. If the conversion is successful, continue as indicated in step 3 of the previous subsection.
    4. If the conversion is NOT successful:
       • If this process occurs for the first time, Excel To Database will inform you of the formats it has applied to attempt the conversion. In subsequent occurrences, this information is not displayed due to its length.
         The first formats that Excel To Database applies are those corresponding to a series of defined formats that Microsoft calls Invariant Culture, and then the formats defined in the Regional Settings of the Windows Control Panel.
       • Continue as indicated in step 4 of the previous subsection.

    Throughout this process, unexpected errors may occur, potentially stopping the data reading or data loading phase. Excel To Database will inform you accordingly.

Frequently Asked Questions

Why do my Excel data change type when imported?

Excel doesn't enforce data types per column. If a cell has a different format (for example, text in a numeric column), the database will reject it to protect schema integrity.

How to avoid date errors when loading an Excel file?

The most common error is the ambiguity between regional formats (UK vs US). It is vital to standardize to ISO format (YYYY-MM-DD) before loading to avoid misinterpretations.

What happens if my Excel has empty cells or nulls?

In databases, a blank space is not the same as a NULL. Excel To Database handles these discrepancies to ensure that your database logic remains consistent.