SQLite DB - How to Make Changes and Additions for Fujitsu CardMinder Software

MY SYSTEM
Windows 7
Fujitsu iX500 Scanner, Model: PA03656-B005
ScanSnap Software, CardMinder v5.3L10

QUESTION
CardMinder generates SQLite databases (I believe). I want to be able to edit the database or merge elements from one database to another. Can you tell me what tool(s) will enable me to perform edit operations?

NOTE
I called Fujitsu support, but they were not able to help with this issue. I tried RazorSQL, but I am not sure how to use it. Note that I have used mySQL for Wordpress using cPanel's phpMyAdmin, but only for lookup and very simple edits. I am not experienced with the Query language instructions or syntax and might need some help with this.
Jerry LOperations ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
There is a PHP program called phpLiteAdmin that you can use like phpMyAdmin to view and edit SQLite databases.

https://www.phpliteadmin.org/
Jerry LOperations ManagerAuthor Commented:
Thanks! I'll try it and report back in a couple days.
Dave BaldwinFixer of ProblemsCommented:
Here's the home page with a lot of more info.  https://www.sqlite.org/
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Joe WinogradDeveloperCommented:
Hi WizeOwl,
Two comments for you. First, it is not a SQLite database. It is a Standard Jet DB. Simply change the DIB extension to MDB and open it in Access — of course, back it up before messing with it! Second, if you ever do need a product for SQLite databases, I like (the free!) DB Browser for SQLite. This EE thread that I'm on talks about it a bit and shows some screenshots. Regards, Joe

P.S. How's it going on the chess puzzle sites?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jerry LOperations ManagerAuthor Commented:
The files are stored in the following structure...

[Folder] My Documents
     [Folder] CardMinder
          [Folder] MyScannedCards.cxdb    << CardMinder opens the database from here.
                 [Folder] Images
                      [file] 1_N1.pdf
                      [file] 1_N2.pdf
                      [file] 2_N1.pdf
                      [file] 2_N2.pdf
                 [file] CardMinder1.sqldb   << Note the extension.

CardMinder shows the text data from the card (as recognized by the Abbyy OCR) in the main window text fields, and the associated card image in an adjacent window.

ScreenshotWhen opened in Notepad++ it displays "SQLite format 3" as readable text, and "VSQLiteQ2" as well. (EE is not allowing me to upload an attachment of the files. I tried renaming the offending extensions and zipping it, didn't work. I have sent a special request to EE support email.)
Joe WinogradDeveloperCommented:
Interesting! My CardMinder is looking for BID files:

CardMinder DB
Maybe we have different versions. Mine came bundled with a Fujitsu ScanSnap S1500 that I bought in 2012. Its Help>About says CardMinder Version 4.1 L50 (Copyright PFU LIMITED 2003-2012). What does yours say?

Maybe yours is a later version and Fujitsu switched from a Standard Jet DB to a SQLite DB. In any case, for SQLite, I stick by my earlier recommendation of (the free) DB Browser for SQLite. Regards, Joe
Jerry LOperations ManagerAuthor Commented:
OBSERVATIONS
It looks like "DB Browser for SQLite" product is going to be a whole lot easier to use than "phpLiteAdmin" since I won't have to run it on an online or desktop web server.

DEFINITIONS
Here is my understanding of vocabulary. Please correct me if I am wrong . . .

Each COLUMN heading indicates a TABLE name.
Each ROW has a unique number and represents the data for one RECORD.

QUESTION
Here are the actions I want to apply to the data, and I would appreciate knowing how to do this . . .

1. Insert (all) data records from DB2 into DB1. (Perhaps this is known as a "merge"?)
2. Insert only some of the data records from DB2 into DB1.
3. QUERY syntax to delete multiple records (adjacent).
4. QUERY syntax to delete multiple records (non-adjacent), eg, 5-10 & 15-20.

Keep in mind that both DB's are created by the same program, so all tables are the same.
Joe WinogradDeveloperCommented:
SQLite is a relational database system. The Wikipedia article on Relational database is a decent place to start for understanding terminology:
https://en.wikipedia.org/wiki/Relational_database

Re your last post:

> Each COLUMN heading indicates a TABLE name.

No. A table is composed of columns (sometimes called fields) and rows (sometimes called records). A database contains one or more tables.

> Each ROW has a unique number and represents the data for one RECORD.

Yes.

> Insert (all) data records from DB2 into DB1. (Perhaps this is known as a "merge"?)

There's probably a way to do this with SQL, but I don't know SQL well enough to advise you. But a lower tech way to do it is in DB Browser for SQLite via File>Export all records from DB2 and then File>Import all of them into DB1.

> Insert only some of the data records from DB2 into DB1.

Once again, there's probably a way to do this with SQL, but I'm not the guy for that job. Also once again, you could export the records from DB2 to a CSV file, then delete from the CSV file whatever records you don't want, and Import the ones you do want into DB1.

> QUERY syntax to delete multiple records (adjacent).

Let's hope a SQL expert jumps in to give you the SQL statement(s) for that, although you can probably achieve it via Export/Import in DB Browser for SQLite.

> QUERY syntax to delete multiple records (non-adjacent), eg, 5-10 & 15-20.

Same comment as above.

Regards, Joe
Dave BaldwinFixer of ProblemsCommented:
> Each ROW has a unique number and represents the data for one RECORD.
It generally should but it doesn't have to.  SQL databases are not spreadsheets.
Dave BaldwinFixer of ProblemsCommented:
QUERY syntax to delete multiple records
Once again, an SQL database is Not a spreadsheet and is Not organized like one.  There is no concept of 'adjacent' or 'non-adjacent' in SQL.  While it is convenient to display records in a table like a spreadsheet, in the database they are 'organized' in a tree structure because that is easier to search than a table.

To DELETE records, you should identify them so you don't accidentally erase your entire database table.  Below is a typical statement to delete all records that have an 'price' column value of less than 22.  There is nothing that requires those records to be 'adjacent' or 'non-adjacent'.
DELETE FROM mytable WHERE price < 22

Open in new window


http://www.w3schools.com/sql/default.asp is a basic tutorial about SQL statements.
Jerry LOperations ManagerAuthor Commented:
Thanks for the help. If I need more help with SQL queries, I'll post another question.
Joe WinogradDeveloperCommented:
You're welcome. Happy to help. Good luck with the project! Regards, Joe
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.