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

Overview

ExcelToMySQL

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

工具截图:

image

使用方法:

常规界面选择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]

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

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.

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

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

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

A set of Python scripts for finding threats in Office365
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

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

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.
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

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).

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

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).

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

 MM1 and MMC Queue Simulation using python - Results and parameters in excel and csv files
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

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

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. Excel-report-evaluator - A simple Python GUI application to aid with bulk evaluation of Microsoft Excel reports.
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

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

PowerShell module to import/export Excel spreadsheets, without Excel
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

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.

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.
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

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

    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

    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
Releases(ExcelToDatabase4.0)
Owner
Xiaobo Zhang
Xiaobo Zhang
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

Alexander H. 1 Dec 29, 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

Doug Finke 2k Dec 30, 2022
Upload an Excel/CSV file ( < 200 MB) and produce a short summary of the data.

Data-Analysis-Report Deployed App 1. What is this app? Upload an excel/csv file and produce a summary report of the data. 2. Where to upload? How to p

Easwaran T H 0 Feb 26, 2022
Library to create spreadsheet files compatible with MS Excel 97/2000/XP/2003 XLS files, on any platform.

xlwt This is a library for developers to use to generate spreadsheet files compatible with Microsoft Excel versions 95 to 2003. The package itself is

null 1k Dec 24, 2022
ExcelPeek is a tool designed to help investigate potentially malicious Microsoft Excel files.

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

James Slaughter 37 Apr 16, 2022
A Python module for creating Excel XLSX files.

XlsxWriter XlsxWriter is a Python module for writing files in the Excel 2007+ XLSX file format. XlsxWriter can be used to write text, numbers, formula

John McNamara 3.1k Dec 29, 2022
Single API for reading, manipulating and writing data in csv, ods, xls, xlsx and xlsm files

pyexcel - Let you focus on data, instead of file formats Support the project If your company has embedded pyexcel and its components into a revenue ge

null 1.1k Dec 29, 2022
Transpiler for Excel formula like language to Python. Support script and module mode

Transpiler for Excel formula like language to Python. Support script and module mode (formulas are functions).

Edward Villegas-Pulgarin 1 Dec 7, 2021
PyExcelerate - Accelerated Excel XLSX Writing Library for Python 2/3

PyExcelerate Accelerated Excel XLSX writing library for Python master: dev: test coverage: Authors: Kevin Wang and Kevin Zhang Copyright 2015 Kevin Wa

null 448 Dec 28, 2022
Reads Data from given Excel File and exports Single PDFs and a complete PDF grouped by Gateway

E-Shelter Excel2QR Reads Data from given Excel File and exports Single PDFs and a complete PDF grouped by Gateway Features Reads Excel 2021 Export Sin

Stefan Knaak 1 Nov 13, 2021