Reputation: 61
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:
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
Upvotes: 3
Views: 142
Reputation: 61
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.
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.
Upvotes: 0
Reputation: 11
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
Upvotes: 1