腳本之家服務器常用軟件

快捷導航

SQL Server將數(shù)據(jù)導入導出到Excel表格的全過程

更新時間：2024年06月04日 11:47:41 作者：哈茶真的c

這篇文章主要介紹了SQL Server將數(shù)據(jù)導入導出到Excel表格的全過程,文中通過圖文結(jié)合的形式給大家介紹的非常詳細,具有一定的參考價值,需要的朋友可以參考下

最開始，博主介紹一下自己的環(huán)境：SQL Sever 2008 R2

SQL Sever 大致都差不多

1. 通過自帶軟件的方式

首先找到下載SQL Sever中提供的導入導出工具

在這里插入圖片描述

如果開始界面沒有找到自己下載的路徑

C:\Program Files\Microsoft SQL Server\100\DTS\Binn下的DTSWizard.exe文件

在這里插入圖片描述

導出

1.1 打開界面

在這里插入圖片描述

1.2 選擇自己的數(shù)據(jù)源和數(shù)據(jù)庫

在這里插入圖片描述

1.3 選擇導出目標

這里博主導出到Excel文件當中

在這里插入圖片描述

1.4 選擇直接導出數(shù)據(jù)還是進行查詢

在這里插入圖片描述

查詢的話將自己在SSMS上編寫的SQL語句直接復制到框中即可(確保SQL正確，可以進行測試！)
這里博主直接導出表中數(shù)據(jù)

1.5 選擇表目標

在這里插入圖片描述

這里需要切記表的分隔符為：
行：{CR}{LF}
列：制表符
格式不對，可能導出的結(jié)構(gòu)出錯
(也就是不按照行列的方式導入到Excel當中！)

1.6 完成導出

在這里插入圖片描述

1.7 檢查是否導出成功

在這里插入圖片描述

可以看到Excel表格中出現(xiàn)新數(shù)據(jù)！

導入

1.1 打開界面

在這里插入圖片描述

1.2 選擇數(shù)據(jù)源

這里博主選擇的是Excel表格

這里的標題分隔符選{CR}{LF}

這里博主前面有6行垃圾數(shù)據(jù)(所以選擇跳過6行)

在這里插入圖片描述

行分隔符{CR}{LF}

列分隔符制表符

在這里插入圖片描述

1.3 選擇導入目標數(shù)據(jù)庫

選擇自己的服務器和數(shù)據(jù)庫

在這里插入圖片描述

1.4 選擇表

導入的目標表

在這里插入圖片描述

1.5 選擇數(shù)據(jù)類型映射

在這里插入圖片描述

1.6 完成導入

在這里插入圖片描述

1.7 檢查是否導入成功

選擇SSMS工具

在這里插入圖片描述

打開對應的表和數(shù)據(jù)行

在這里插入圖片描述

查看數(shù)據(jù)，可以看到數(shù)據(jù)導入成功！

在這里插入圖片描述

1. SQL Sever 2008 R2 存在的問題：

這是SQLSever2008R2所獨有的，其他版本不清楚，自行了解！
對于還未和SQL Sever數(shù)據(jù)庫建立過鏈接的新建Excel表格無法導入導出數(shù)據(jù)！
所以咱們需要先讓Excel表格和數(shù)據(jù)庫建立連接

1.1 隨便找個表查看表中數(shù)據(jù)

在這里插入圖片描述

1.2 選擇將結(jié)果保存到文件

右鍵SQL語句框出現(xiàn)如下界面

在這里插入圖片描述

1.3 右鍵選擇執(zhí)行

在這里插入圖片描述

1.4 保存結(jié)果

在這里插入圖片描述

1.5 查看文件

在這里插入圖片描述

可以看到Excel文件中出現(xiàn)了數(shù)據(jù)，但是這些數(shù)據(jù)無法分析(無效數(shù)據(jù))，將這些數(shù)據(jù)刪除就可以正常進行導入導出。

2. 通過Pycharm(ODBC)的方式

代碼如下所示：

import pyodbc
import pandas as pd
# 創(chuàng)建連接字符串
conn_str = (
    r'DRIVER={SQL Server Native Client 10.0};'
    r'SERVER=BF-202403241716;'
    r'DATABASE=scott;'
    r'Trusted_Connection=Yes;'
)
# 建立連接
cnxn = pyodbc.connect(conn_str)
# 創(chuàng)建游標對象
cursor = cnxn.cursor()
# 執(zhí)行SQL查詢
query = "SELECT * FROM dbo.salgrade"
cursor.execute(query)
# 獲取查詢結(jié)果
data1 = cursor.fetchall()
print(type(data1))
print(data1)

# 獲取列名
columns1 = [column[0] for column in cursor.description]
print(type(columns1))
print(columns1)

# 將元組列表展開為一維數(shù)組
data1 = [list(item) for item in data1]
print(type(data1))
print(data1)

# 將結(jié)果轉(zhuǎn)換為DataFrame
df1 = pd.DataFrame(data1, columns=columns1)
print(df1)


# 將數(shù)據(jù)寫入Excel文件
df1.to_excel('output.xlsx', index=False)

# 關(guān)閉數(shù)據(jù)庫連接
cursor.close()
cnxn.close()

關(guān)鍵點1：連接方式

數(shù)據(jù)庫是：SQL Sever 2008 R2 所以這里采用的連接方式是SQL Sever Native Client 10.0 如果是更新的版本應該是16或者其他
(可以問問ChartGPT)

# 創(chuàng)建連接字符串
conn_str = (
    r'DRIVER={SQL Server Native Client 10.0};'
    r'SERVER=BF-202403241716;'
    r'DATABASE=scott;'
    r'Trusted_Connection=Yes;'
)

具體的服務器和數(shù)據(jù)庫按照自己的來，這里我SQL Sever通過驗證的方式是Windows驗證，所以這里r'Trusted_Connection=Yes;' 如果有用戶密碼，請使用用戶密碼的方式登錄。

關(guān)鍵點2：元組列表需要轉(zhuǎn)換為一維數(shù)組(???)

# 將元組列表展開為一維數(shù)組
data1 = [list(item) for item in data1]
print(type(data1))
print(data1)

<class 'list'>
[(1, 700, 1200), (2, 1201, 1400), (3, 1401, 2000), (4, 2001, 3000), (5, 3001, 9999)]
<class 'list'>
[[1, 700, 1200], [2, 1201, 1400], [3, 1401, 2000], [4, 2001, 3000], [5, 3001, 9999]]
   grade  losal  hisal
0      1    700   1200
1      2   1201   1400
2      3   1401   2000
3      4   2001   3000
4      5   3001   9999

需要將元組列表展開為一維數(shù)組

原因：data1 是一個包含元組的列表，每個元組都是一個行，但是傳遞給DataFrame的每行數(shù)據(jù)應該是一維的，如果不進行轉(zhuǎn)換，那么傳遞的數(shù)據(jù)就是二維的

在這里插入圖片描述

會出現(xiàn)如下類型不匹配的報錯==(解決了半天，還是有點不理解)==

import pyodbc
import pandas as pd

# 假設(shè)data是cursor.fetchall()返回的結(jié)果，它是一個包含元組的列表
data = [(1, 700, 1200), (2, 1201, 1400), (3, 1401, 2000), (4, 2001, 3000), (5, 3001, 9999)]
print(type(data))
print(data)
# 獲取列名
columns = ['grade', 'losal', 'hisal']  # 確保這些列名與您的表中的列名相匹配
print(type(columns))
print(columns)

# 將結(jié)果轉(zhuǎn)換為DataFrame
df = pd.DataFrame(list(data), columns=columns)
print(df)

在這里插入圖片描述

code2當中代碼如上，同樣還是一個包含元組的列表，但是就是可以轉(zhuǎn)換成DataFrame的形式==(很奇怪啊)==

關(guān)鍵點3：import導包

如果直接從官網(wǎng)進行下載的話，速度可能會很慢，而且有時候還會斷開連接，所以可以選擇一些國內(nèi)的鏡像網(wǎng)站

pip install some-package -i https://pypi.tuna.tsinghua.edu.cn/simple

以下這種方式就很慢：

(.venv) PS D:\code\test_3_29> pip install openpyxl
Collecting openpyxl
  Downloading openpyxl-3.1.2-py2.py3-none-any.whl.metadata (2.5 kB)
Collecting et-xmlfile (from openpyxl)
  Downloading et_xmlfile-1.1.0-py3-none-any.whl.metadata (1.8 kB)
Downloading openpyxl-3.1.2-py2.py3-none-any.whl (249 kB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 250.0/250.0 kB 547.4 kB/s eta 0:00:00
Downloading et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)
Installing collected packages: et-xmlfile, openpyxl
Successfully installed et-xmlfile-1.1.0 openpyxl-3.1.2

成功結(jié)果如下：

在這里插入圖片描述