How to solve the problem of mixed data in a column when importing Excel to SQL?

Question

big data

Hello! There is a task to write a program for importing data from Excel to SQL. There is a detailed guide on the MSDN website with no difficulties. Complexity arises if the column contains mixed data. Excel checks the first 8 rows (by default) and decides which type to put on them. For the first type, which prevails in the number of records, the data is inserted as it should, for the other type, NULL is entered in all cells. Here, I think, America was not discovered for anyone. On the Microsoft site on this issue there is a recommendation to use IMEX=1 in Extended Properties. But in this case, Excel starts putting down the string type for all data, after which SQL cannot convert the resulting strings into its own format (the table has different types of columns, not just strings). If the entire SQL table is made up of string columns,
At the moment, the following algorithm is used:
- first, a request is made for the types of columns in the SQL table;
- after which the DataTable columns are configured for these types;
- data is read by OleDbDataReader line by line;
- are written to the resulting DataTable one cell at a time;
- some columns are "cleared" of various characters for numeric values ​​(for example, "%" signs);
- the resulting DataTable is sent for writing to SQL.
Such an algorithm works well, with the exception of one BUT, which discourages any desire to use this algorithm: the data loading speed has increased by almost 4 times (to be precise, for 700k lines, 7 minutes of the old algorithm versus 40 minutes described above).
The amount of data ranges from 300k lines to several million.
Oledb driver is used - Microsoft.ACE.OLEDB.12.0. Excel format - xlsx.
Actually a question: whether it is possible to win such behavior somehow? Keep the speed of the original algorithm and solve the problem of mixed data in a column when importing from Excel to SQL.

Answer 1

Export to csv and import?