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.
- The Excel file I actually used is over 200mb by itself, It took a few minutes just to open it. )
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
- To be able to handle even low-speed PCs such as office PCs Do not attach a sheet of referrer data for Vlook that has tens of MB
2: Do not use add-ons such as Excel ADO and OBDC
- Do not use add-ons so that even clerks who do not have PC knowledge can use it.
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
● 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.
- First, in the text file with the name "Sample.db" in the path where the conversion destination DB is placed Create a DB file for storing the data frame by changing the extension.
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.
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
Excel file preparation 2: Search window and name definition
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.
Recommended Posts