top of page

About the database package

Jul 9, 2024
1 min read

As the title suggests, we will use a database package to output the data stored in a table.


Input files

logic

How to connect to a database

Connection Mode Default

Set the full path of the target file in the connection string.


provider=microsoft.ace.oledb.12.0;data source="full path of the target file";Extended Properties="Excel 12.0;HDR=YES;IMEX=1";



Read or export to data table

Enter the SQL SELECT statement in "SELECT statement".


All Records

SELECT * FROM [Sheet1$$]


When you want to read only specific data

SELECT * FROM [Sheet name$$] WHERE Target column name = $variable name$ (value or variable to search for)


Example EG

SELECT * FROM [Sheet1$$]

SELECT * FROM [Sheet1$$] WHERE age= '48'

SELECT * FROM [Sheet1 $$ ] WHERE age= 45

SELECT * FROM [Sheet1$$] WHERE date= '1985/11/03'

SELECT * FROM [Sheet1$$] WHERE name= 'Taka'


If the above age is registered as a string in Excel, enclose it in quotation marks,

If it is registered as a number, leave it as it is. Also, if you are like me and your PC environment does not use the Japanese format (YYYYMMDD), you need to be careful. It does not read the date in that format, so if you specify the Excel date serial value as the search string, it will read it.


SELECT * FROM [Sheet1 $$ ] WHERE birth = 31162


*The basic syntax of SQL is widely available on the Internet, so please refer to it.




Output result


References






Comments


bottom of page