A python tool for batch importing excel/csv files into mysql database.

Xiaobo Zhang

Last update: Jan 8, 2023

Related tags

Overview

ExcelToMySQL

简介：一个实现自动化导入excel文件到mysql数据库的工具

工具截图：

使用方法：

常规界面选择excel文件目录，填入目标数据库，选择导入模式，点击开始即可导入目录下所有excel文件。

工具特色：

高速自动化，一键式，无人值守，自动纠错，高级功能可选

主要Python包:

PySimpleGUI numpy pandas pymysql chardet

详细介绍:

如果电脑上有python环境，可以运行如下命令启动：

python E:\Python\Project\python-excelimporter\interface.py

也可以通过Cx-Freeze打包成exe文件：

cmd: cd E:\Python\Project\python-excelimporter

python E:\Python\Project\python-excelimporter\setup.py build.

如果没有python环境，可以联系[email protected]，获取exe文件可以直接运行。

选项介绍:

常规：

Excel文件：

所在文件夹：选择要导入的excel文件所在目录，该目录下所有的excel文件（包括xls、xlsx和csv格式）都将被导入

MySQL连接:

填入要导入的目标数据库连接信息主机: 端口:
用户: 密码: 数据库:

模式:

覆盖模式下，在导入一张表前，工具将先删除同名的表，在创建并导入数据。

追加模式下，工具将直接将数据导入到同名的表

高级：

CSV文件编码：

因为csv格式没有记录文件编码，所以我们不能确定其编码格式如果选择自动，工具将自动猜测其编码格式，如果选择或者填写特定的编码格式，工具将先使用用户提供的编码解码，如果失败，再尝试用常见编码格式解码，如果失败再通过猜测其编码格式如何确定csv文件的编码，可以参考下面文章 http://pandaproject.net/docs/determining-the-encoding-of-a-csv-file.html

将这些值替换为null：

对于常见的excel错误单元格或者某特定的值，填入以逗号分隔，将被替换为null

为创建的表名添加前缀：

可以为工具创建的表名指定前缀，以示区分

删除空行：

如选择，工具将删除所有空行

去除字符前后空格：

若选择，工具将去除字符前后空格

跳过空表：

若选择，如果表格没有数据，工具将不会创建数据库表

其他：

表名的确定：

使用文件名并小写，将非文字字符替换为_。如果一个excel文件包含多个sheet，将采用文件名+_+sheet名。如果表名超过64个字节，自动截断并再前面加上计数如0_表名

列名的确定：

使用第一行作为列名，如果列名全为空，将用下一个非空行作为列名，如果存在列名为空，将用unnamed+计数作为列名，如果列名超过64个字节，自动截断。列名将去除前后空格并将%替换为_

列类型的确定：

工具将计算每列最大长度，如果小于255，将使用varchar(255)，如果大于255，将使用text。

常见错误1366：

如果excel文件包含表情等utf8mb4编码的字符，在utf8编码的表中，如果sql_mode为STRICT_TRANS_TABLES，会报1366错误。工具将暂时设置sql_mode=''，导入会设回默认值

常见错误1118：

对于一行数据的总长度，mysql限制为65535，如果超长，将报1118错误。工具将全部列类型替换为text（text类型一列只占1个字节长度）

作者: ryjfgjl

如需帮助，请联系[email protected]

Comments

error: Missing optional dependency 'fsspec'. Use pip or conda to install fsspec.

Hello,

I just downloaded and tried version 5.1 on one excel file, then I got below error:

Failed Excel: xxxxxx.xlsx, error: Missing optional dependency 'fsspec'. Use pip or conda to install fsspec.

My OS is win11 without python installed.

Could you tell me how to resolve the issue?

Best Regards Wicky Hu

opened by wickyhu 2
Suggestion to support Chinese directory path

Thank you very much for your sharing! It helps a lot. I have some tiny advice here. In file ‘excelimporter.py’, Pandas's read_csv(file_name) funcion does not support Chinese directory path with the default parser engine. So I suggest to set the parameter 'engine' to 'python'. Like this: dataset = pd.read_csv(csv, encoding=encode, dtype=str, keep_default_na=False, engine='python') will work well.

opened by xyliaaaaa 2

According to the received excel file (.xlsx,.xlsm,.xltx,.xltm), it converts to word format with a given table structure and formatting

2 Feb 18, 2022

Python Module for Tabular Datasets in XLS, CSV, JSON, YAML, &c.

Tablib: format-agnostic tabular dataset library _____ ______ ___________ ______ __ /_______ ____ /_ ___ /___(_)___ /_ _ __/_ __ `/__ _

4.2k Dec 30, 2022

A suite of utilities for converting to and working with CSV, the king of tabular file formats.

csvkit is a suite of command-line tools for converting to and working with CSV, the king of tabular file formats. It is inspired by pdftk, GDAL and th

5.2k Dec 31, 2022

Create Open XML PowerPoint documents in Python

python-pptx is a Python library for creating and updating PowerPoint (.pptx) files. A typical use would be generating a customized PowerPoint presenta

1.7k Jan 5, 2023

A set of Python scripts for finding threats in Office365

Py365 A collection of scripts for finding threats in Office365 Risky Rules A tool for finding risky or suspicious inbox rules - more detail in this po

49 May 18, 2022

Created covid data pipeline using PySpark and MySQL that collected data stream from API and do some processing and store it into MYSQL database.

2 Nov 20, 2021

Test app for importing contact information in CSV files.

Contact Import TestApp Test app for importing contact information in CSV files. Explore the docs » · Report Bug · Request Feature Table of Contents Ab

1 Feb 6, 2022

Pandas on AWS - Easy integration with Athena, Glue, Redshift, Timestream, QuickSight, Chime, CloudWatchLogs, DynamoDB, EMR, SecretManager, PostgreSQL, MySQL, SQLServer and S3 (Parquet, CSV, JSON and EXCEL).

AWS Data Wrangler Pandas on AWS Easy integration with Athena, Glue, Redshift, Timestream, QuickSight, Chime, CloudWatchLogs, DynamoDB, EMR, SecretMana

3.3k Jan 4, 2023

3.3k Dec 31, 2022

We’re releasing an open-source tool you can use now, which we developed as a homemade Just-In-Time database access control tool for our sensitive database. This tool syncs with our directory service, slack, SIEM, and finally, our Apache Cassandra database.

Cassandra Access Control By Aner Izraeli - Intezer Security Manager ([email protected]) We’re releasing an open-source tool you can use now, which

6 Mar 31, 2022

MM1 and MMC Queue Simulation using python - Results and parameters in excel and csv files

implementation of MM1 and MMC Queue on randomly generated data and evaluate simulation results then compare with analytical results and draw a plot curve for them, simulate some integrals and compare results and run monte carlo algorithm with them

1 Jan 19, 2022

xlwings is a BSD-licensed Python library that makes it easy to call Python from Excel and vice versa. It works with Microsoft Excel on Windows and macOS. Sign up for the newsletter or follow us on twitter via

xlwings - Make Excel fly with Python! xlwings CE xlwings CE is a BSD-licensed Python library that makes it easy to call Python from Excel and vice ver

2.5k Jan 6, 2023

My sister is a GR of her class. She had to mark attendance of students from screenshots of teams meeting on an excel sheet. I resolved her problem by reading names from screenshots using PyTesseract and marking them present on the excel using Pandas in Python. It took me 1hr to write the code and it is saving half an hour everyday.

auto-team-attandance Don't judge the code, this is not the best way to write code. I was learning tkinter that is why GUI is bad. Here's the Mega link

25 Sep 4, 2022

Excel-report-evaluator - A simple Python GUI application to aid with bulk evaluation of Microsoft Excel reports.

Excel Report Evaluator Simple Python GUI with Tkinter for evaluating Microsoft Excel reports (.xlsx-Files). Usage Start main.py and choose one of the

1 Dec 29, 2021

Script For Importing Image sequences into scrap mechanic via blueprints

To use dowload and extract "video makes.zip" Python has to be installed https://www.python.org/ (may not work on version lower than 3.9) Has to be run

2 Oct 30, 2021

PowerShell module to import/export Excel spreadsheets, without Excel

PowerShell + Excel = Better Together Automate Excel via PowerShell without having Excel installed. Runs on Windows, Linux and MAC. Creating Tables, Pi

2k Dec 30, 2022

Data Utilities e.g. for importing files to onetask

Use this repository to easily convert your source files (csv, txt, excel, json, html) into record-oriented JSON files that can be uploaded into onetask.

1 Jul 18, 2022

A SageMaker Projects template to deploy a model from Model Registry, choosing your preferred method of deployment among async (Asynchronous Inference), batch (Batch Transform), realtime (Real-time Inference Endpoint). More to be added soon!

SageMaker Projects: Multiple Choice Deployment A SageMaker Projects template to deploy a model from Model Registry, choosing your preferred method of

3 Dec 21, 2021

Django-MySQL extends Django's built-in MySQL and MariaDB support their specific features not available on other databases.

Django-MySQL The dolphin-pony - proof that cute + cute = double cute. Django-MySQL extends Django's built-in MySQL and MariaDB support their specific

504 Jan 4, 2023

A python tool for batch importing excel/csv files into mysql database.

Related tags

Overview

ExcelToMySQL

简介：一个实现自动化导入excel文件到mysql数据库的工具

工具截图：

使用方法：

工具特色：

主要Python包:

详细介绍:

选项介绍:

常规：

Excel文件：

MySQL连接:

模式:

高级：

CSV文件编码：

将这些值替换为null：

为创建的表名添加前缀：

删除空行：

去除字符前后空格：

跳过空表：

其他：

表名的确定：

列名的确定：

列类型的确定：

常见错误1366：

常见错误1118：

作者: ryjfgjl

如需帮助，请联系[email protected]

You might also like...

According to the received excel file (.xlsx,.xlsm,.xltx,.xltm), it converts to word format with a given table structure and formatting

Python Module for Tabular Datasets in XLS, CSV, JSON, YAML, &c.

A suite of utilities for converting to and working with CSV, the king of tabular file formats.

Create Open XML PowerPoint documents in Python

A set of Python scripts for finding threats in Office365

Created covid data pipeline using PySpark and MySQL that collected data stream from API and do some processing and store it into MYSQL database.

Test app for importing contact information in CSV files.

Pandas on AWS - Easy integration with Athena, Glue, Redshift, Timestream, QuickSight, Chime, CloudWatchLogs, DynamoDB, EMR, SecretManager, PostgreSQL, MySQL, SQLServer and S3 (Parquet, CSV, JSON and EXCEL).

Pandas on AWS - Easy integration with Athena, Glue, Redshift, Timestream, QuickSight, Chime, CloudWatchLogs, DynamoDB, EMR, SecretManager, PostgreSQL, MySQL, SQLServer and S3 (Parquet, CSV, JSON and EXCEL).

We’re releasing an open-source tool you can use now, which we developed as a homemade Just-In-Time database access control tool for our sensitive database. This tool syncs with our directory service, slack, SIEM, and finally, our Apache Cassandra database.

MM1 and MMC Queue Simulation using python - Results and parameters in excel and csv files

xlwings is a BSD-licensed Python library that makes it easy to call Python from Excel and vice versa. It works with Microsoft Excel on Windows and macOS. Sign up for the newsletter or follow us on twitter via

Excel-report-evaluator - A simple Python GUI application to aid with bulk evaluation of Microsoft Excel reports.

Script For Importing Image sequences into scrap mechanic via blueprints

PowerShell module to import/export Excel spreadsheets, without Excel

Data Utilities e.g. for importing files to onetask

A SageMaker Projects template to deploy a model from Model Registry, choosing your preferred method of deployment among async (Asynchronous Inference), batch (Batch Transform), realtime (Real-time Inference Endpoint). More to be added soon!

Django-MySQL extends Django's built-in MySQL and MariaDB support their specific features not available on other databases.

Comments

error: Missing optional dependency 'fsspec'. Use pip or conda to install fsspec.

Suggestion to support Chinese directory path

Releases(ExcelToDatabase4.0)

ExcelToDatabase4.0(Apr 3, 2022)

excel2mysql3.0(Sep 12, 2021)

excel2mysql(Sep 5, 2021)

Owner

Xiaobo Zhang

Excel-report-evaluator - A simple Python GUI application to aid with bulk evaluation of Microsoft Excel reports.

PowerShell module to import/export Excel spreadsheets, without Excel

Upload an Excel/CSV file ( < 200 MB) and produce a short summary of the data.

Library to create spreadsheet files compatible with MS Excel 97/2000/XP/2003 XLS files, on any platform.

ExcelPeek is a tool designed to help investigate potentially malicious Microsoft Excel files.

A Python module for creating Excel XLSX files.

Single API for reading, manipulating and writing data in csv, ods, xls, xlsx and xlsm files

Transpiler for Excel formula like language to Python. Support script and module mode

PyExcelerate - Accelerated Excel XLSX Writing Library for Python 2/3

Reads Data from given Excel File and exports Single PDFs and a complete PDF grouped by Gateway