Jump to content
Sign in to follow this  
tobject

SQLite DB into SQL Explorer

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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!).


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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 by ProgAndy

*GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×