472,978 Members | 2,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,978 developers and data experts.

A tool which can batch import multiple excel files into mysql/oracle database automatically

1 Bit
# ExcelToDatabase
## Bref: A tool which can batch import multiple excel files into mysql/oracle database automatically.
## Pictures:
## Features:
Batch Automation: Import multiple excel files under directory one time

One-Click: Do not need to do anything until all excel files are imported.

High Speed: Most quickly tools like this around the world.

Inteligent: When come across some durty data or some difference between Excel and Database, tool can deal with it and go on.

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

Free: Most important thing.

## Usage
1.Start Program

Way 1: Command: python D:\Projects\ExcelToDatabase\main.py

Way 2: Send an email to (Moderation - redacted email), you can get an exe program which can directly run on windows.

2.Input Information

Select directory with excel files; Input target database information; Choose import mode.

3.Click Start.

## Tested Environments:
Windows 7+, MySQL 5.6+/Oracle 11g+, Excel 1997+(xls,xlsx,csv)

## Options Detail:

### General:
#### Excel:
Directory: The excel files under this directory would be imported

#### MySQL/Oracle Connection:
options to connect to database
#### Mode:

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

Append: just insert data into table(table needs exist in the database)

### Advanced:
#### CSV Encoding:
Tools can auto-detect encoding of csv files(default), and you can choose or input other value.
#### Replace To NULL:
values populated 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 Column on row:
Set which row as Column name.
#### Skip Blank Rows:
Skip Blank Rows
#### Trim SpaceS:
Trim spaces on data.
#### Skip Blank Sheets:
Ignore if no data.
#### Include Sub Directories
Find all excel files under the directory Include Sub Directories

### Others:
#### How to define table name:
file name + '_' + sheet name(if one excel has multipule sheets)
#### How to define column name:
Default is the first row
#### How to define column type:
Varchar(255) is default. If max length of column more than 255, text will be set.
#### How to deal with mysql error 1366:
Auto Correct
#### How to deal with mysql error 1118:
All column will be create as text

# Author: ryjfgjl
Apr 24 '22 #1
0 9888

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

Similar topics

by: Jen | last post by:
Trying to take one table in access and split it into multiple excel files(using an excel template); and then email based on email addresses in Table2; Of course, I would like to do all of this...
by: hstockbridge5 | last post by:
Hi, I am trying to link many Excel files into a database with no luck. Here is the code: '============================================== Sub LinkExcelFiles() Dim fso 'FileSystemObject Dim...
by: rcollins | last post by:
So I did the searching for someone to already have the answer. I had modified this code for My spreadsheets and database. I have marked the three lines in the code that are bad. Am I missing...
by: theodorej | last post by:
......how do I select multiple Excel files to import into Access? Any insights would be deeply appreciated.
by: ciaran.hudson | last post by:
Hi I have multiple excel files of the same format in a directory. They are called book1.xls, book2.xls, book3.xls and so on. What is the easiest way to import the tab named sheet1 from each of...
by: ivancycheng | last post by:
I have around 400 excel files with same format (multiple sheets) and wants to import few sheets from excel to MS Access (XP version) for further processing. any one can advise me how to do it? (I'm...
by: sumanta123 | last post by:
Dear Sir, How to export/import the excel file in oracle database using sql promt. Please guide me the command(export/import) for the neddful. Thanks in Adavance. Regards Sumanta Panda
by: rhonda6373 | last post by:
Is there a way to use the TransferSpreadsheet to import multiple Excel workbooks from a single directory in VBA at one time?
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...
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.