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

Jerry L
Jerry L used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014
Commented:
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 Manager

Author

Commented:
Thanks! I'll try it and report back in a couple days.
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
Here's the home page with a lot of more info.  https://www.sqlite.org/
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Developer
Fellow 2017
Most Valuable Expert 2018
Commented:
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?
Jerry LOperations Manager

Author

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 WinogradDeveloper
Fellow 2017
Most Valuable Expert 2018

Commented:
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 Manager

Author

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 WinogradDeveloper
Fellow 2017
Most Valuable Expert 2018

Commented:
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 Problems
Most Valuable Expert 2014

Commented:
> 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 Problems
Most Valuable Expert 2014
Commented:
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 Manager

Author

Commented:
Thanks for the help. If I need more help with SQL queries, I'll post another question.
Joe WinogradDeveloper
Fellow 2017
Most Valuable Expert 2018

Commented:
You're welcome. Happy to help. Good luck with the project! Regards, Joe

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial