Articles & Videos‎ > ‎Videos‎ > ‎

Import text via ODBC

I. Problem:
Import text into Excel and/or other database. Generally.
Not because it is difficult  - all the progrmas do that. The problem is that the creator of transactional applications,
from which usually such exports come, they put these texts into a huge amount of unnecessary lines, odd characters, etc.
The basic defect in the import of the full text is that does not fit in a sheet, and further includes those 'junk' lines that Excel has yet to be identified and removed by hand - a nightmare.

How to solve it?

1) The Hopeless Excel's text import option. Not quite that it is wrong, it has been wrong for 20 years, and in all the books is described as an 'access Excel data'. As who knows what and how, he describes.
And who was trying to automate the VBA, knows what are the problems with this.

2) By reading line-by-line by the VBA macro
Effect is better! We read and what we want, insert the worksheet also with full control. But there are problems - remain in the data sheet and it is difficult to do with this 'aggregate database'

3) Via ODBC, such as query
Solutions definitely correct, but the problem is - you need to define your definition of such import, that is, 'schema.ini' - you can do a PhD degree. It is cool, but very complicated.

4) You can also use the special tool for this purpose - AFIN.NET.TextConverter. But, sometimes, we remember that ODBC is just a very quick ...

5) And, finally? - Via ODBC, but multisteply, parametrically, but directly to any reasonable DATABASES, here: into the Access
One thing you need to know, is a very small base SQL.


You can. Pretty easy, but, above all, very fast and easy!
And parametrically - when there is a new text file - it'll go on playing for a moment the database.

But please note, however, certain novelty in this method:
Import text is usually a single step - the file is imported and has either opened in Excel, or reads line by line, or via ODBC, in defining the word schema.ini. Novelty lies in the fact that there is a text file is read by one movement to the external database already filtered, but all further movements: the distinction between the column and all the fixes are done on a table in the database - and there is really a lot of opportunities.
Only at the very end to get the resulting table, or exported to external files.

I do not know a better method.

Preview performance of the above.

Exactly the same film as above, but the text file has more than 2 million lines


The fltered import to the table access = 40 SECONDS
(50,000 lines / sec)
Whole - with all the alterations, copies, calculations - 212 seconds
(Computer: thin - 1.5 GHz, 1 GB RAM)

It's more or less for a big company with 2 year sales with correctness to the simply document = everything.
4 minutes and the problem is over. Not bad.