【SQL Server】2. 将数据导入导出到Excel表格当中

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介: 【SQL Server】2. 将数据导入导出到Excel表格当中

最开始,博主介绍一下自己的环境:SQL Sever 2008 R2
SQL Sever 大致都差不多

1. 通过自带软件的方式

首先找到下载SQL Sever中提供的导入导出工具
在这里插入图片描述
在这里插入图片描述
如果开始界面没有找到自己下载的路径
C:\Program Files\Microsoft SQL Server\100\DTS\Binn下的DTSWizard.exe文件
在这里插入图片描述

导出

1.1 打开界面

在这里插入图片描述

1.2 选择自己的数据源和数据库

在这里插入图片描述

1.3 选择导出目标

这里博主导出到Excel文件当中
在这里插入图片描述

1.4 选择直接导出数据还是进行查询

在这里插入图片描述
查询的话将自己在SSMS上编写的SQL语句直接复制到框中即可(确保SQL正确,可以进行测试!)
这里博主直接导出表中数据

1.5 选择表目标

在这里插入图片描述
==这里需要切记表的分隔符为:
行:{CR}{LF}
列:制表符==
格式不对,可能导出的结构出错
(也就是不按照行列的方式导入到Excel当中!)

1.6 完成导出

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

1.7 检查是否导出成功

在这里插入图片描述
可以看到Excel表格中出现新数据!

导入

1.1 打开界面

在这里插入图片描述

1.2 选择数据源

这里博主选择的是Excel表格
==这里的标题分隔符选{CR}{LF}==
这里博主前面有6行垃圾数据(所以选择跳过6行)
在这里插入图片描述
行分隔符{CR}{LF}
列分隔符制表符
在这里插入图片描述

1.3 选择导入目标数据库

选择自己的服务器和数据库
在这里插入图片描述

1.4 选择表

导入的目标表
在这里插入图片描述

1.5 选择数据类型映射

在这里插入图片描述

1.6 完成导入

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

1.7 检查是否导入成功

选择SSMS工具
在这里插入图片描述
打开对应的表和数据行
在这里插入图片描述
查看数据,可以看到数据导入成功!
在这里插入图片描述

SQL Sever 2008 R2 存在的问题:

这是SQLSever2008R2所独有的,其他版本不清楚,自行了解!
对于还未和SQL Sever数据库建立过链接的新建Excel表格无法导入导出数据!
所以咱们需要先让Excel表格和数据库建立连接

1.1 随便找个表查看表中数据

在这里插入图片描述
在这里插入图片描述

1.2 选择将结果保存到文件

右键SQL语句框出现如下界面
在这里插入图片描述

1.3 右键选择执行

在这里插入图片描述

1.4 保存结果

在这里插入图片描述

1.5 查看文件

在这里插入图片描述
可以看到Excel文件中出现了数据,但是这些数据无法分析(无效数据),将这些数据删除就可以正常进行导入导出。

2. 通过Pycharm(ODBC)的方式

代码如下所示:

import pyodbc
import pandas as pd
# 创建连接字符串
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)
# 创建游标对象
cursor = cnxn.cursor()
# 执行SQL查询
query = "SELECT * FROM dbo.salgrade"
cursor.execute(query)
# 获取查询结果
data1 = cursor.fetchall()
print(type(data1))
print(data1)

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

# 将元组列表展开为一维数组
data1 = [list(item) for item in data1]
print(type(data1))
print(data1)

# 将结果转换为DataFrame
df1 = pd.DataFrame(data1, columns=columns1)
print(df1)


# 将数据写入Excel文件
df1.to_excel('output.xlsx', index=False)

# 关闭数据库连接
cursor.close()
cnxn.close()

关键点1:连接方式

数据库是:SQL Sever 2008 R2 所以这里采用的连接方式是SQL Sever Native Client 10.0 如果是更新的版本应该是16或者其他
(可以问问ChartGPT)

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

具体的服务器和数据库按照自己的来,这里我SQL Sever通过验证的方式是Windows验证,所以这里r'Trusted_Connection=Yes;' 如果有用户密码,请使用用户密码的方式登录。

关键点2:元组列表需要转换为一维数组(???)

# 将元组列表展开为一维数组
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

需要将元组列表展开为一维数组
原因:data1 是一个包含元组的列表,每个元组都是一个行,但是传递给DataFrame的每行数据应该是一维的,如果不进行转换,那么传递的数据就是二维的
在这里插入图片描述
会出现如下类型不匹配的报错==(解决了半天,还是有点不理解)==

import pyodbc
import pandas as pd

# 假设data是cursor.fetchall()返回的结果,它是一个包含元组的列表
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)

# 将结果转换为DataFrame
df = pd.DataFrame(list(data), columns=columns)
print(df)

在这里插入图片描述
code2当中代码如上,同样还是一个包含元组的列表,但是就是可以转换成DataFrame的形式==(很奇怪啊)==

关键点3:import导包

如果直接从官网进行下载的话,速度可能会很慢,而且有时候还会断开连接,所以可以选择一些国内的镜像网站

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

成功结果如下:
在这里插入图片描述
在这里插入图片描述
方法放在gitee上了,自取哟!

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
1天前
|
Python
Pandas处理日常EXCEL表格的便捷操作
Pandas处理日常EXCEL表格的便捷操作
|
2天前
|
数据安全/隐私保护
杨老师课堂之Excel VBA 程序开发第七讲表格数据高亮显示
杨老师课堂之Excel VBA 程序开发第七讲表格数据高亮显示
8 1
|
3天前
|
easyexcel Java API
SpringBoot集成EasyExcel 3.x:高效实现Excel数据的优雅导入与导出
SpringBoot集成EasyExcel 3.x:高效实现Excel数据的优雅导入与导出
13 1
|
4天前
|
JSON 资源调度 JavaScript
蓝易云 - vue实现导出excel的多种方式
以上两种方式都可以实现在Vue中导出Excel的功能,你可以根据你的需求选择合适的方式。
12 1
|
7天前
|
JavaScript 前端开发
JS导出excel功能
JS导出excel功能
|
16天前
|
前端开发
react框架对Excel文件进行上传和导出
react框架对Excel文件进行上传和导出
|
18天前
|
SQL 存储 数据可视化
excel表格sql数据库
Excel表格和SQL数据库是两种不同的数据管理工具,它们各自有自己的特点和用途。下面我将分别介绍它们,并探讨它们之间的关系和互操作性。 一、Excel表格 Excel是微软公司推出的一款
|
11天前
|
分布式计算 大数据 数据处理
MaxCompute操作报错合集之在本地用tunnel命令上传excel表格到mc遇到报错: tunnel upload C:\Users***\Desktop\a.xlsx mc里的非分区表名 -s false;该怎么办
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。
|
18天前
|
前端开发
React实现一个excel文件导出
React实现一个excel文件导出
21 0
|
1月前
|
前端开发 Java
基于Java爬取微博数据(二) 正文长文本+导出数据Excel
【5月更文挑战第12天】基于Java爬取微博数据,正文长文本+导出数据Excel