资讯

历史

科技

环境与自然

成长

游戏

财经

文学与艺术

美食

健康

家居

文化

情感

汽车

三农

军事

旅行

运动

教育

生活

星座命理

将CSV、Excel、XML文件转换为MySQL数据库

创作时间:

作者:

@小白创作中心

将CSV、Excel、XML文件转换为MySQL数据库

引用

CSDN

https://blog.csdn.net/m0_73500130/article/details/140040374

在数据处理和管理工作中，经常需要将CSV、Excel、XML等格式的文件数据导入到MySQL数据库中。本文将详细介绍如何完成这一转换过程，包括给MySQL数据库赋权、CSV文件转换、Excel文件转换以及XML文件转换等关键步骤。

给MySQL数据库读取文件赋权

在进行转换前需要先给MySQL数据库赋权，因为MySQL服务器通常配置了一个安全选项，限制了LOAD DATA INFILE语句可以加载文件的目录。这个--secure-file-priv选项就是用于限制LOAD DATA INFILE和SELECT ... INTO OUTFILE操作可以访问的文件目录。

赋权需要先输入命令查看--secure-file-priv指定的目录路径，随后使用cp命令将要转换的CSV、Excel(需要转化为CSV文件)文件移动到这个路径下：

SHOW VARIABLES LIKE 'secure_file_priv';

复制文件到查询到的/var/lib/mysql-files/文件夹里（Amazon Dataset.csv是我从kaggle上下载的CSV文件名称）：

sudo cp "/数据文件存放的路径/Amazon Dataset.csv" "/var/lib/mysql-files/"

赋予MySQL读取文件的权利：

sudo chown mysql:mysql "/var/lib/mysql-files/Amazon Dataset.csv"
sudo chmod 640 "/var/lib/mysql-files/Amazon Dataset.csv"

重启MySQL服务器：

sudo systemctl restart mysql

CSV文件转换

接下来就可以创建MySQL数据库了,根据原数据格式来建立

CREATE DATABASE amazon;  
USE amazon;
CREATE TABLE IF NOT EXISTS daily_prices (  
    Date DATE PRIMARY KEY,  
    Open FLOAT,  
    High FLOAT,  
    Low FLOAT,  
    Close FLOAT,  
    Adj_Close FLOAT,  
    Volume BIGINT  
);

接下来进行数据读取和转换：CSV文件以,作为分割，所以使用FIELDS TERMINATED BY ','提示数据库原文件是,分割格式，ENCLOSED BY '"'指定每个字段的值都应该在双引号内，LINES TERMINATED BY '\n'指定每条记录之间的分隔符是换行符（\n），IGNORE 1 ROWS;指定指定导入数据时忽略文件的第一行。

LOAD DATA INFILE '/var/lib/mysql-files/Amazon Dataset.csv'  
INTO TABLE daily_prices  
FIELDS TERMINATED BY ','  
ENCLOSED BY '"'  
LINES TERMINATED BY '\n'  
IGNORE 1 ROWS;

查询下数据库是否转换成功：

SELECT * FROM daily_prices;

Excel文件转换

安装gnumeric将xls或xlsx文件转换为.csv文件

udo apt-get install gnumeric

安装完gnumeric后，使用ssconvert命令来将.xls文件转换为.csv文件：

ssconvert 数据.xls 数据.csv

随后既可以像操作CSV文件一样进行操作。

XML文件转换

我从国家数据官网下载的XML格式数据,官网链接：国家数据官网，在下载完数据后先看一下数据结构

根据数据结构来建立一个数据库：

CREATE DATABASE month_data;  
USE month_data;  
  
CREATE TABLE monthly (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    indicator VARCHAR(255),  
    time VARCHAR(255),  
    data DECIMAL(10, 2)  
);

接下来编写Python脚本，需要提前下载lxml库，lxml库相对于xml库更灵活方便，处理编码方式效果更好：

# import_xml.py
from lxml import etree 
import mysql.connector  
  
conn = mysql.connector.connect(  
    host='主机名',  
    user='用户名',  
    password='用户密码',  
    database='database的名字'  
)  
cursor = conn.cursor()  
tree = etree.parse('network.xml')  
root = tree.getroot() 
  
for record in root.findall('data/record'):  
    indicator = record.find('field[@name="指标"]').text  
    time = record.find('field[@name="时间"]').text  
    data = record.find('field[@name="数据"]').text  
      
    cursor.execute(  
        "INSERT INTO monthly (indicator, time, data) VALUES (%s, %s, %s)",  
        (indicator, time, data)  
    )  
  
conn.commit()  
cursor.close()  
conn.close()

随后在终端运行它：