SQLite DB into SQL Explorer

[tobject]

I just started to look at SQLite

question1: can I access SQLite database via SQL Explorer (from Borland Delphi) is there an ODBC driver?

question2: What the easiest way to port data from CSV to SQLite table?

[PsaltyDS]

It doesn't come from SQLite.org itself, but there is an SQLite ODBC Driver by Christian Werner.

[jchd]

Answer 1: our MV Penguin already did.

Answer 2: it all depends on your context and the type of CVS your talking about.

For a one-time import, you may be happy by using the sqlite3.exe utility, IFF the .CSV format is compatible with what the utility is able to understand.

Some third-party SQLite management tools may have a compatible import feature working for you. I know that SQLite Expert has it, but the feature is part of the paid version.

For any regular use, you should consider writing your own tool or using a paid version of Expert, which will ease your work with SQLite tremendously.

The problem with .CVS is that there are so many "versions" that writing a tool which will decode correctly every .CSV around is close to impossible.

[tobject]

Thanks, I'll check it out!

>decode correctly every .CSV around is close to impossible.

Nothing is impossible, Excel can do it so can we

I've seen some 3rd party CSV apps on Delphi back in my days, I'm sure VB has the same

Consider calling Excel COM Obj directly and grabbing data from each cell for example (or into array)

Almost all WinPCs have some version of MS Office istalled

[jchd]

I should have bet 1M€ on that...

Excel can do it so can we

Excel? Ha ha. It's about the worst tool for doing that!

That isn't the topic we're talking about, so let's just say that regarding decoding every variation of .CSV you just don't have _any_ idea of what you're going to deal with (don't take offence!).

[ProgAndy]

I did not find a CSV-file not working with this UDF yet, but it is quite slow and it is bound to the regex-limitations (that means fields must not be larger than the regex pattern limit)

PS: Excel is really bad here. OpenOffice Calc does a much better job on importing CSV data, especially when using multiline-cells.

Edited June 29, 2010 by ProgAndy

[jchd]

There are unfortunately more "interesting" variations like [multiline] comments, ending separators, etc. If you add the possible need to differentiate between "123" as text and 123 as integer, between back to back separators meaning <null> and the like, the safest is to rely on an ad hoc version for each source. I confess that many "variations" are not conformant to the RFC but they do exist and you have little choice when you depend on importing them.

I agree about OpenOffice being much better than Excel at this game.