473,288 Members | 1,710 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,288 developers and data experts.

ExcelToDatabase: batch import excel files into database

ExcelToDatabase: batch import excel files into database



What is it?
ExcelToDatabase is an automatical tool which can batch import multiple excel files into database(mysql/oracle/sqlserver/hive).It frees your hands when you have many excels need to import into database, or you do not want to deal with any kinds of problem when manually import excel any more

Features
Batch
The best feature is batch import one or more excel one time, but not one to one import like navicate

Easy
Only need you provide the location of excels and the connect information of database, the tool can work until all the excels are imported

Quick:
Batch importting make it faster than manual tool like navicate over the count of your excels times. If you have 10 excels, it is faster 10x

Intelligent:
When you manually import excel into database using tool like navicate, whether if you feel sad when error occurs? Baddly, other error occurs when you just fix one. DO NOT WORRY! The tool can deal with them!

Advanced:
Rich options could be custom choose to make more fuction come true.

Schedule:
You can make a schedule using it on windows/linux.

Where to get it
The source code is on the github.

The packaged executable program(ExcelToDatabase.exe on windows or ExcelToDatabase on linux) is available, you can download it from sourceforge.


Usage
Start Program
* If you hive python environment, you can use command to start:

`python main.py`

* If you get a packaged executable program:

On Windows: ExcelToDatabase.exe

On Linux: ./ExcelToDatabase


Choose and Input

Choose the directory with excel files;
Input target database information;
Choose import mode`

Click Start

Supported Environments:
* Windows/Linux
* MySQL/Oracle/SQLServer/Hive
* Excel(xls,xlsx,xlsm,csv)

Menu
Language
English and Chinese you can choose to display

Database
MySQL/Oracle/SQLServer/Hive, you can choose one database according to your target database

Data Source
* Directory: choose Directory as your data source, in this case, excels under the directory will be imported
* Files: choose Files as your data source, in this case, you can select one or more excel files to import

Options:
In general, you only need to provide information in the section of "General". But if you want to do more, you may need "Advanced" section

General:
Excel:
Choose directory or files as your data source

MySQL/Oracle/SQL Server/Hive Connection:
Input connection information of your target database
Mode:

* Overwrite: drop table first(if exists); create table; insert data.

* Append: just insert data into table(table needs exist in the database), according to table name + column name to match, only import data in matched column to the matched table, unmatched column will be ignored

Advanced:
Encoding of CSV:
Tools can auto-detect encoding of csv files(default),
and you can choose or input other value
Replace Values to Null:
values populated(comma separated) will be replaced to null
Add Table Prefix:
The value populated will be added to table name before
Append All Data to One Exists Table:
Under Append mode, import all data to the table populated
The Header on Row:
Set which row as Column name
Skip Blank Lines:
Skip Blank Rows
Trim Spaces:
Trim spaces around the data
Skip Blank Sheets:
Ignore sheet if there is no data in it
Add a Column, Values is The Table Name:
For imported table, add a column which value is its table name
Recursion of Directories
Recursive directories to find all excel files
Transform Chinese in Table/Column Name to The First Letter
Transform chinese in table name and column name to the first letter of its pinyin
Run Sql Before Starting
When starting import, run sql in the sql file choosed before
Run Sql After Comleting
When complete import, run sql in the sql file choosed after

How the tool works?
Some logic is described below when the tool work
How to define table name:
If only one sheet in excel >> excel name
If multipule sheets in excel >> excel name + '_' + sheet name
Symbol like ',' will be replaced to '_'
If table name is more than the limit of database >> cut off
How to define column name:
Default is the first row, but if the first row is all blank, next row will be used
Symbol like ',' will be replaced to '_'
If column name is blank, 'unnamed' will be set as column name
If column name is repeated, number like '0' will be added as its suffix
How to define column type:
Varchar(255) is default. If max length of column more than 255, text/clob will be set.

Correct Error
mysql error 1118:
utf8mb4 is contained in the data, but the utf8 is the character set of database,
mysql error 1118 occured >> import but utf8mb4 character is ignored
mysql error 1366:
The length of all column is too long or the row size is too large,
mysql error 1366 occured >> text will be set as column type

How To Schedule
The tool can directly run in command line without gui, so you can schedule it on the Windows or Linux. A configuration file(config.ini) is needed to add.

* Python environment:

Windows: python main.py D:/config.ini

Linux: python main.py /home/ryjfgjl/config.ini

* Packaged Program:

Windows: ExcelToDatabase.exe D:/config.ini

Linux: ./ExcelToDatabase /home/ryjfgjl/config.ini

Getting Help
Email: 2577154121@qq.com
Aug 27 '22 #1
0 6521

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Reggae | last post by:
Hello, I am looking for the most efficient way to programmatically batch import excel files into MS Access. The excel files are not lined up in a row and currently I am going to each file so I...
3
by: gdaniels | last post by:
Hi, I need some help. The routine below runs when a command button is clicked. A prompt appears to enter the directory path to the folder containing delimited text files to be imported into an...
2
by: john | last post by:
I have 400 different Excel-spreadsheetfiles, same structure, all with only one record in it, and all residing in the same folder. Every now and then new Excel files are being added. In my Access...
2
by: Riddle | last post by:
Hi, I have found many tutorials showing how to access data in an Excel spreadsheet using a COM object in .net. But, as far as I can tell, this requires Excel to be installed. Is there anyway I...
3
by: laparico2002 | last post by:
I have a Project at hand "Importing Excel files into a VBA" but i dont know how to go about it. Please can someone put me through. Though i tried Importing wizard but am not getting the result i...
18
by: PW | last post by:
Convert them to CSV in Excel, then use TransferText (which does not work correctly and also doesn't accept XLS/Excel files directly) or create a link to an Excel XLS workbook and do an Append Query...
6
by: provor | last post by:
Hello, I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this...
13
by: WU JU | last post by:
Hi. I have thousands of excel files in one directory. I want to build up the Access table from excel files. Each excel file has one worksheet, but I don't need every column and low of the...
0
by: ryjfgjl | last post by:
# ExcelToDatabase ## Bref: A tool which can batch import multiple excel files into mysql/oracle database automatically. ## Pictures: https://github.com/ryjfgjl/ExcelToDatabase ## Features: Batch...
2
by: CD Tom | last post by:
I have a very large excel file runs from A to GV. Is there a way to bring this into my database. I would like to split this into maybe two or three tables. Now the excel file comes from an online...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.