EXCEL TABLE forces a field to be a date, when I supply a non-date string in SQL INSERT INTO

Question

I am using an Excel (2013) table in one workbook (workbook A) and I am using SQL INSERT INTO to take a whole row of data from Workbook A to an identical Excel table in Workbook B. Workbook B will typically be closed during this operation, so using a db connection (read and write) and SQL are the best route for this. I am using the Native SQL engine within Excel, not an external db engine.

The Excel table in Workbook A has 73 fields, and these contain a mixture of text, numbers and dates (DMYHMS), although the table is mostly set up for General formats. Workbook B is the central database for a collection of Workbook A's which connect to it. Currently, I am only testing one connection from Workbook A to Workbook B.

In Workbook A, when I execute the SQL INSERT INTO I get an error:

“Data type mismatch in criteria expression.” (Err=-2147217913)

Upon a careful process of elimination I can now conclude that there is one field (the 71st field) from Workbook A which is causing the problem, and this is “LASTMOD_BY”, which is a userid, i.e. ‘12345678’, but which, for some unknown reason, Workbook B expects to be a date. This is especially odd as I have a SUB_BY field (Submitted By) which is also a userid ‘12345678’ and this is accepted as a text string. Why does Excel Table then think the same data for LASTMOD_BY should be a date?!

Reading many of the posts here, most users seem to want a string to be a date: I want the string to stay as a string!

For the life of me I have researched and tried everything to get round this problem, but I’m out of ideas!

For the record I have tried the following, but without success:

1. Making sure Workbook A and Workbook B LASTMOD_BY field are set to General
2. Making sure Workbook A and Workbook B LASTMOD_BY field are set to Text
3. Making the data in the LASTMOD_BY field in Workbook A numeric and then, later, a text field (as a number, LASTMOD_BY throws an ‘Overflow’ error as the userid is too big to be a date value)
4. Copying another table field, which is accepted as a text field, into the LASTMOD_BY column and renaming it as LASTMOD_BY, and deleing the old LASTMOD_BY column.
5. Inserting in Workbook A and Workbook B another field called LASTMODBY in the column before LASTMOD_BY and supplying LASTMODBY the userid and omitting LASTMOD_BY from the SQL statement (Excel still expected LASTMODBY to be a date!)
6. Testing all the values in the table cells to ensure the correct data type is detected and then ensuring it is correctly reflected in the SQL syntax (i.e. if a date then format as ‘date’; if a text format as a ‘text’; if a number format as number (no single quotes))
7. Missing out LASTMOD_BY from the INSERT INTO statement (which DID work) and then adding a separate UPDATE statement to set the LASTMOD_BY field (which DIDN’T work).

How do I get the Excel table in Workbook B to accept a userid (‘LASTMOD_BY’) as a text and not a date?

This is driving me nuts and I am beginning to conclude that Excel Tables, when used with the embedded SQL engine, have a bug.

For those who need to see the SQL, here it is:

INSERT INTO [CQDB$] (ProductQ, Version, QID_1, QID_2, QID_3, QID_4, QID_5, QID_6, QID_7, QID_8, QID_9, QID_10, QID_11, QID_12, QID_13, QID_14, QID_15, QID_16, QID_17, QID_18, QID_19, QID_20, QID_21, QID_22, QID_23, QID_24, QID_25, QID_26, QID_27, QID_28, QID_29, QID_30, QID_31, QID_32, QID_33, QID_34, QID_35, QID_36, QID_37, QID_38, QID_39, QID_40, QID_41, QID_42, QID_43, QID_44, QID_45, QID_46, QID_47, QID_48, QID_49, QID_50, QID_51, QID_52, QID_53, QID_54, QID_55, QID_56, QID_57, QID_58, QID_59, QID_60,  QID_61, QID_62, QID_63, QID_64, QID_65, SUB_DMYHM, SUB_BY, LASTMOD_DMYHM, LASTMOD_BY, RECSTATUS, SubMonth)
VALUES('NonBank', 6, 98765432, ‘Mr Smith',12348765,’My Insurance plan','0',’My Local Branch','0','0','29-Sep-16','30-Dec-09’, '0', '0', 'No', '0', '0', '0', '0', '0', '0', '0', 'No', 'Yes', '0', '0', '0', '0', '0', '0', 'N/A', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', 12345678, ‘28-Sep-16 11:09:00', '0', '0', ‘28-Sep-16 11:09:29', 12345678, ‘28-Sep-16 11:09:29', 12345678, 'FAIL: To Follow-up',’01-Sep-16')

FYI the SQL is built up using VBA. What you see above is my handed edited version (to remove any real data). If you should spot a missing/extra comma etc, it’s probably from my manual edit, not the generated SQL!

I really appreciate if anyone can solve this for me!

Cheers

Answer 1

First glance; the delimiters in your data do not seem to be consistent around your text fields... I've not looked any further yet but can you confirm if they are consistent in your SQL prior to your manual edit? Thanks

Answer 2

I didn't find a satisfactory answer to this problem, by which I mean no fault was detected and then corrected. However, I was able to remove the problem.

My conclusion has settled on an Excel Table feature whereby each field in an Excel table can be "determined" by Excel to be a data type based upon the majority of values found within that table.

This article explains this point: ExcelTable determines data types based on first 8 values entered in field

I tried deleting all rows in the table and inserting data anew, but it seemed that theExcel Table had a "memory" of what was there before and converted the new data added to date format.

However, I don't think this article fully explains the problem I was having. Take into account a "bug" in Excel whereby cells are formatted by Excel as date. See this article: Excel formats cells as Date bug

I didn't have the whole workbook formatted as date, but there were elements of this happening in some of my SQL updates. Basically, nothing I did could get Excel to "forget" the previous date format it had auto-selected for the table in Workbook B.

The Solution

1. Insert a brand new column into the table in Workbook B. Call it 'Column 1'. Column 1 was formatted as General.
2. I re-ran my SQL and instead of writing the userid to LASTMOD_BY I changed the SQL to put this in 'Column 1'. It worked! The userid is appearing in 'Column 1' as a userid and not a date.
3. Now, rename LASTMOD_BY to 'Column 2' and rename 'Column 1' to LASTMOD_BY. Change the SQL so that 'Column 1' is now called LASTMOD_BY and re-run. It works and now the userid is appearing in LASTMOD_BY as a userid and not a date.
4. Finally, delete the redundant 'Column 2'

Furthermore, I then formatted the table as Text. I tried the ADO IMEX=1 flag but this did not work so I kept at IMEX=0.

All my SQL queries now work. I have applied conditional formatting on the tables to tidy up display of the entries (not sure if they are making a difference in every case, but belt and braces!).

When I move on to reporting I may need to use Advanced Filters and conversion of text to values/dates etc, but this is easier to handle than the blackbox of SQL.

Not a very satisfactory answer, but I've erased this corruption/anomaly from my workbook.