如何将 Excel 中的数据粘贴到 pgAdmin 4 中

如何将 Excel 中的数据粘贴到 pgAdmin 4 中
如何将 Excel 中的数据粘贴到 pgAdmin 4 中
Mia Chevalier
2024年7月19日星期五 下午12:05:50

在 pgAdmin 4 中使用 Excel 数据

从 Excel 复制数据并将其直接粘贴到 pgAdmin 4 中以添加新行可能具有挑战性。许多用户在粘贴功能时遇到问题，该功能似乎只能在 pgAdmin 剪贴板中使用。

本文探讨了 pgAdmin 4 粘贴功能的局限性，并提供了使用 pgAdmin 4 成功将 Excel 数据传输到 PostgreSQL 数据库的替代方法。

命令 描述
pd.read_excel() 将 Excel 文件读入 pandas DataFrame。
psycopg2.connect() 建立与 PostgreSQL 数据库的连接。
sql.SQL() 使用 psycopg2 的 SQL 模块以安全的方式构造 SQL 命令。
df.iterrows() 将 DataFrame 行作为（索引，系列）对进行迭代。
cur.execute() 执行数据库操作或查询。
COPY command 将数据从 CSV 文件复制到 PostgreSQL 表中。
CSV HEADER 指定 CSV 文件包含带有列名称的标题行。

将 Excel 数据传输到 PostgreSQL

提供的脚本说明了使用以下两种方法将 Excel 数据传输到 PostgreSQL 数据库： pgAdmin 4。第一个脚本使用 Python 与 pandas 和 psycopg2 图书馆。在此脚本中， pd.read_excel() 命令将 Excel 文件读取到 pandas DataFrame 中，使数据操作更容易。与 PostgreSQL 数据库的连接是使用以下命令建立的 psycopg2.connect()，并创建一个游标对象来执行SQL命令。该脚本构建了一个 insert_query 使用 sql.SQL()，确保安全地构建查询。当它使用迭代 DataFrame 行时 df.iterrows()，它通过执行准备好的 SQL 命令将每一行插入数据库 cur.execute()。最后，提交更改并关闭连接。

第二种方法是将 Excel 数据保存为 CSV 文件，然后使用 SQL 命令将此 CSV 数据导入到 PostgreSQL 表中。首先，该脚本演示了如何使用以下命令在 PostgreSQL 中创建表： CREATE TABLE 命令。接下来，它使用 COPY 命令将数据从 CSV 文件复制到 PostgreSQL 表中。该方法指定使用 DELIMITER 和 CSV HEADER 以确保正确解释 CSV 格式并且标题行用于列名称。这两种方法都提供了将 Excel 数据传输到 PostgreSQL 数据库的有效方法，为用户提供了根据其工作流程和工具首选项的灵活性。

将 Excel 数据导入 pgAdmin 4

将 Python 与 pandas 和 psycopg2 结合使用

import pandas as pd
import psycopg2
from psycopg2 import sql

# Read the Excel file
df = pd.read_excel('data.xlsx')

# Connect to PostgreSQL database
conn = psycopg2.connect(host="localhost", database="yourdb", user="youruser", password="yourpassword")
cur = conn.cursor()

# Create insert query
insert_query = sql.SQL("INSERT INTO your_table (col1, col2, col3) VALUES (%s, %s, %s)")

# Iterate over DataFrame and insert data
for i, row in df.iterrows():
    cur.execute(insert_query, (row['col1'], row['col2'], row['col3']))

# Commit changes and close connection
conn.commit()
cur.close()
conn.close()

使用 SQL 命令将 Excel 数据加载到 PostgreSQL 中

将 SQL COPY 命令与 CSV 中间体结合使用

-- Step 1: Save Excel as CSV
-- Step 2: Use the following SQL commands

-- Create a table in PostgreSQL
CREATE TABLE your_table (
    col1 VARCHAR(255),
    col2 INTEGER,
    col3 DATE
);

-- Copy data from CSV into the table
COPY your_table (col1, col2, col3)
FROM '/path/to/your/data.csv'
DELIMITER ','
CSV HEADER;

PostgreSQL 的有效数据导入技术

使用以下命令将数据从 Excel 导入到 PostgreSQL 时要考虑的另一个方面 pgAdmin 4 是使用 pgAdmin Import/Export tool。该工具提供图形界面，可将各种格式（包括 CSV）的数据直接导入到 PostgreSQL 表中。要使用此功能，您必须首先将 Excel 数据导出为 CSV 文件。获得 CSV 文件后，您可以导航到 Import/Export pgAdmin 中的选项。该工具允许您指定源文件和目标表，以及配置各种选项，如分隔符、引号字符和编码。

此外，确保 CSV 文件中的数据类型与 PostgreSQL 表中的数据类型匹配也很重要。不匹配的数据类型可能会导致导入错误或数据损坏。您还可以在将数据导入数据库之前使用 SQL 脚本来验证和清理数据。这个预处理步骤可以使用类似的工具来完成 pandas 在 Python 中处理缺失值、正确设置日期格式并确保数字字段格式正确。采取这些预防措施有助于维护数据完整性并确保导入过程顺利进行。

关于将数据从 Excel 导入到 PostgreSQL 的常见问题

  1. 我可以将Excel数据直接导入到PostgreSQL中吗？
  2. 不可以，您必须先将 Excel 数据转换为兼容格式（例如 CSV），然后再将其导入 PostgreSQL。
  3. 我可以使用哪些工具将数据导入 PostgreSQL？
  4. 您可以使用类似的工具 pgAdmin Import/Export, pandas 和 psycopg2，以及 COPY 导入数据的命令。
  5. 如何处理大型 Excel 文件？
  6. 将大型 Excel 文件拆分为较小的 CSV 文件，或使用脚本以块的形式读取和插入数据，以避免内存问题。
  7. 如果 CSV 和 PostgreSQL 表之间的数据类型不匹配怎么办？
  8. 确保您的 CSV 数据类型与目标表架构匹配，或在导入前使用数据转换工具调整类型。
  9. 有没有办法自动化数据导入过程？
  10. 是的，您可以使用用 Python 或 bash 编写的处理文件转换和数据库插入的脚本来自动化该过程。
  11. 如何确保导入过程中数据的完整性？
  12. 在导入之前验证并清理数据，确保其与目标表架构匹配并且没有错误。
  13. 我可以在数据导入中使用 Excel 公式吗？
  14. 不可以，在将数据导出为 CSV 以导入 PostgreSQL 之前，需要将 Excel 公式转换为静态值。
  15. 数据导入过程中常见的错误有哪些以及如何避免？
  16. 常见错误包括数据类型不匹配、编码问题和分隔符不匹配。验证您的数据并正确配置导入设置以避免这些错误。

结束数据导入过程

通过将 Excel 文件转换为 CSV 并使用 pgAdmin 的导入/导出工具或使用带有 pandas 和 psycopg2 库的 Python 脚本，可以有效地将数据从 Excel 导入到 pgAdmin 4 中。确保数据类型兼容性和执行数据验证是此过程中的关键步骤。这些方法为将数据传输到 PostgreSQL 提供了可靠且灵活的解决方案，解决了在 pgAdmin 中直接粘贴的限制。

关于数据传输技术的最终想法

使用 pgAdmin 4 成功将 Excel 数据导入 PostgreSQL 需要将数据转换为合适的格式（例如 CSV）或利用 Python 脚本进行自动化。这些方法规避了 pgAdmin 中的剪贴板限制，确保数据完整性和平稳的数据库集成。通过遵循这些方法，用户可以简化数据导入流程，并在 PostgreSQL 数据库中维护准确且一致的数据集。