DB read of Sqlite3 from Excel (without ODBC) and suggestion search

Introduction

Probably, in a large company, the company's inventory quantity, inventory value, and shipment quantity statistics Each business sells terminal software that allows you to instantly see the number of rotation days, gross profit, etc. I think it is being used.

Since I work for a small business, I don't introduce such a convenient system. Various types that are regularly spit out by client software from the core Oracle RDBMS I sorted and processed the CSV data and referred to the required data.

It is also necessary to browse, organize, and process various data in separate CSVs. It is inefficient, and you can paste CSV of various data for each sheet in Excel and use Vlook. As for the reference, the memory is wasted when the amount of data increases, and the Excel function It happens that it just works and waits for a few minutes.

What you want to do

1: Read SQlite3 database in Excel, no ODBC

2: Implementation of list reading from DQ file and suggestion search like Google by partial match

3: Refer to the values ​​(price, inventory value, etc.) in other columns of the same list based on the suggestion search.

Example: Candidates can be selected by using partial match keywords such as part of the product name. Also read the list of referrers from the SQlite3 database

Specific method

1: Convert the CSV data that is the reference source to the DB in SQlite3 format

2: Do not use add-ons such as Excel ADO and OBDC

Referenced

● Reference: Operate SQLite from Excel without ODBC (Settings)   https://qiita.com/hisayuki/items/9b42624790ba74a2fb35

● Reference: Operate SQLite from Excel without ODBC (Practice 1)   https://qiita.com/hisayuki/items/cd1b6d7bd1a8293647c8

● Reference: Input like Google Suggest in Excel   http://suugleblog.blogspot.com/2012/02/blog-post_4988.html

Excel library to use (although it is a library or macro)

●SQLite For Excel Version 1.0   https://github.com/govert/SQLiteForExcel

● Latest version   https://github.com/govert/SQLiteForExcel/releases/tag/1.0

● The above license   https://github.com/govert/SQLiteForExcel/blob/master/License.txt

Operating environment

Current usage environment   WINDOWS10 64BitPro   Office2019 64Bit   Python3.8

Preparation

● File tree structure See below for required file structure

SQLiteForExcel
 │   ChangeLog.txt
 │   sqlite3.dll
 │   SQLite3_StdCall.dll
 │   SQLiteForExcel_64.xlsm
 │  
 ├─DataBase (I put the DB file of SQlite3 here.)
 │ Sample.db
 │
 ├─x64_64BitOS_Library(According to the type of OS, "sqlite3" at the top of the hierarchy.Replace "dll")
 │ sqlite3.dll
 │
 └─x86_32BitOS_Library(According to the type of OS, "sqlite3" at the top of the hierarchy.Replace "dll")
    sqlite3.dll

Database source material

Convert the following secondary array data to SQlite3 DB Test product master.xlsx 01.jpg

● Convert the above Excel to SQlite3 database with the following code Excel file of the above original material in Python code in SQlite3 database Convert to a file.

Excel2SQlite3.py


import sqlite3
import pandas as pd

print("● Start converting the Excel file to SQLDB.")
dbpath = 'D:/Sample.db'
conn = sqlite3.connect(dbpath)

print("● Start conversion of product master")
df = pd.read_excel('D:/Test product master.xlsx',encoding="cp932",dtype = 'object')


#Add search key
df.insert(0, 'search key', df['Product name'] + ':' + df['Product code'])
print(df)

#Add table
df.to_sql("Master", conn, if_exists="replace")
del df

conn.close()

●Sample.db The search key is now in the first column. Suggest candidates when this search key is partially matched (JAN code or part of product name) Create a macro like this in Excel. Also, regarding "Sample.db" stored here, Arrange the files as shown in the tree above. 02.jpg

Excel file preparation 1: Tree structure

Create a macro in an excel file so that it has the following tree. ● Sheet type Sheet1 (search) <Sheet for pull-down menu operation to search the search key Sheet2 (for list) <Working sheet for turning the list of search-matched candidates ● Standard module Sqlite3 <This opens the above SQLite For Excel V1.0 and opens the Excel file inside please use it.

● Actual Excel VBA Explorer view tree 03.jpg

Excel file preparation 2: Search window and name definition

04.jpg

Excel file preparation 3: Macro description

Write the following macro for Sheet1 (search).

Private Sub Worksheet_Change.vba



Private Sub Worksheet_Change(ByVal Target As Range) Application.Volatile 'Run only if the value changes

Dim testFile As String
Dim RetVal As Long
Dim myDbHandle As LongPtr
Dim myStmtHandle As LongPtr
Dim InitReturn As Long

'Variable for input of SQL execution Dim SqlOrderSet As String

'Variables for each SQL specification Dim SqlTableOrder As String Dim SqlRecordOrder As String Dim SqlSearchKey As String

'For specifying worksheets in the search candidate list Dim LWs As Worksheet

Select Case Target.Address Case "$B$3"

'Turn off drawing and calculation Application.EnableEvents = False Application.Calculation = xlCalculationManual Application.ScreenUpdating = False

Range("B3").NumberFormatLocal = "@"

'Update start when a specific part is changed

If Intersect(Target, Range("B3")) Is Nothing Then
    Exit Sub
Else

    Worksheets("For lists").Cells.ClearContents

'DB initialization InitReturn = SQLite3Initialize

    If InitReturn <> SQLITE_INIT_OK Then
        Debug.Print "Error Initializing SQLite. Error: " & Err.LastDllError
        Exit Sub
    End If

'Specifying the path testFile = ActiveWorkbook.path & "\DataBase" & "Sample.db"

'Connection to DB RetVal = SQLite3Open(testFile, myDbHandle) Debug.Print "SQLite3Open returned " & RetVal

'Set of SQL syntax SqlTableOrder = "Master" SqlRecordOrder = "search key" SqlSearchKey = Range("B3") SqlOrderSet = ("SELECT * FROM " & SqlTableOrder & " WHERE " & SqlRecordOrder & " LIKE '%" & SqlSearchKey & "%'")

    RetVal = SQLite3PrepareV2(myDbHandle, SqlOrderSet, myStmtHandle)
    Debug.Print "SQLite3PrepareV2 returned " & RetVal

'Execution of SQL instructions RetVal = SQLite3Step(myStmtHandle) Debug.Print "SQLite3Step returned " & RetVal

'Extract by specifying the data column rows for the selected number of items r = 1 Do While RetVal <> SQLITE_DONE

        Worksheets("For lists").Cells(r, 1).Value = SQLite3ColumnText(myStmtHandle, 1)
        Worksheets("For lists").Cells(r, 2).Value = CStr(SQLite3ColumnText(myStmtHandle, 3))
        
        RetVal = SQLite3Step(myStmtHandle)
        r = r + 1
    Loop
 
    RetVal = SQLite3Finalize(myStmtHandle)
    Debug.Print "SQLite3Finalize returned " & RetVal
 
    RetVal = SQLite3Close(myDbHandle)

'Redefining names for pull-down menus Set LWs = Worksheets("For lists") LWs.Range(LWs.Cells(1, 1), LWs.Cells(LWs.Cells(Rows.Count, 1).End(xlUp).row, 1)).Name = "list"

End If

'Turn on drawing and calculation Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True

'Pull down the list. Worksheets("Search").Range("B3").Select SendKeys "%{DOWN}"

End Select

End Sub


 * If you want to increase the number of search windows and files to be searched, increase the number of "Case" above.


# How to use
 If you enter a part of the keyword in the search window, the search key that partially matches the condition will be automatically picked up.
 It looks like the following.
 ![05.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/244225/0afecdb6-be5f-9a13-b7d5-b6c88589082a.jpeg)



Recommended Posts

DB read of Sqlite3 from Excel (without ODBC) and suggestion search
Search and save images of Chino Kafu from Twitter
Read the function name from the DB and execute it dynamically
Mathematical explanation of binary search and ternary search and implementation method without bugs
Read and use Python files from Python
Graph display of AIX and Linux nmon data without using MS Excel