Learn by case

Shortcuts

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 macroEffect 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 file.
One thing you need to know, is a very small base SQL.

Conclusions:
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.

II. Preview performance of the above.

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