首页   注册   登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
5ispy
V2EX  ›  程序员

1w 条数据在 excel 里,如何在数据库里利用这些数据查询出想要的数据

  •  
  •   5ispy · 2018-11-01 16:52:06 +08:00 · 4692 次点击
    这是一个创建于 453 天前的主题,其中的信息可能已经有所发展或是发生改变。
    简单来说就是,一个 student 表,里面有学生 id 和分数 score。现在有个 excel 表里有 1w 条学生 id,怎么能查出这些 id 对应的分数?
    67 回复  |  直到 2018-11-03 11:21:38 +08:00
    liprais
        1
    liprais   2018-11-01 16:55:18 +08:00 via iPhone
    拼个 sql 不就行了
    yfl168648
        2
    yfl168648   2018-11-01 16:56:27 +08:00
    建个表,把 ID 搞进去,然后查询呗
    lihongjie0209
        3
    lihongjie0209   2018-11-01 17:03:32 +08:00
    要么全部导入 excel, 要么全部导入 sql, 然后操作
    sambohuang
        4
    sambohuang   2018-11-01 17:14:57 +08:00
    既然已经在 Excel 里面了,还放到数据库里去干嘛?
    Excel 的 Lookup、Vlookup、Index 函数了解一下,轻松解决你的问题
    jasonyang9
        5
    jasonyang9   2018-11-01 17:16:16 +08:00
    数据透视表?
    ioschen
        6
    ioschen   2018-11-01 17:25:31 +08:00
    帮楼主整理下,

    我们学校数据库有个 Student 表,里面存有全校所有学生的 id 和分数 score
    现在老师给我一个 excel,里面有一万个学生 id,没有分数
    我需要到数据库里获取到这一万个学生的分数,请问怎么做?
    lllpla
        7
    lllpla   2018-11-01 17:26:16 +08:00
    java poi 读取 excel 然后写代码操作?
    ioschen
        8
    ioschen   2018-11-01 17:28:48 +08:00
    @ioschen 有两个小办法
    1. 手工复制(快捷键)这些学生 ID,然后 sql 语句查询
    2. 代码获取 excel 学生 ID,然后 sql 查询
    yepinf
        9
    yepinf   2018-11-01 17:29:53 +08:00 via iPhone
    是时候上数据分析 pandas ( python )
    xpresslink
        10
    xpresslink   2018-11-01 17:43:29 +08:00
    把 excel 另存成 CSV,导入数据库表。
    inner join 了解一下。
    CivAx
        11
    CivAx   2018-11-01 17:43:41 +08:00   ♥ 6
    楼上你们搞的越来越复杂了。。。。就是一个查询语句。

    把语句模板写好,select 'id' 'score' from student where id = '$id';
    这个可以直接查询单条结果。

    如果是批量查询,在新的 Excel 文件中粘贴原 Excel 表的 “ id 列” 到第二列;
    在第一列写 select ...... id = '
    在第三列写 ';
    然后各填充 1 万行
    Ctrl + C 复制全表,粘贴到 TXT 文件中,用替换功能去掉空格 / 空白,另存为 *.sql 文件
    NaviCat 连上数据库,直接右键选中库 “运行 SQL 文件”,把查询结果导出为 Excel,这活儿就算结了。

    如果不具备任何专业知识,这个是最简单和通俗的方法
    gaius
        12
    gaius   2018-11-01 17:49:32 +08:00
    人家说的是 excel 有 1W 个学生 id,去数据库查 1W 个分数吧
    alpenstock
        13
    alpenstock   2018-11-01 17:56:29 +08:00
    annielong
        14
    annielong   2018-11-01 17:58:46 +08:00
    最快就是导入数据库,再进行查询
    luozic
        15
    luozic   2018-11-01 18:50:20 +08:00 via iPhone
    倒入数据库,爱做啥做啥
    shmilyin
        16
    shmilyin   2018-11-01 18:53:24 +08:00 via Android
    vlookup,了解一下
    ant2017
        17
    ant2017   2018-11-01 18:56:30 +08:00 via Android   ♥ 1
    直接在 Excel 里面连接数据库查询
    limuyan44
        18
    limuyan44   2018-11-01 19:11:17 +08:00 via Android
    v2 到底有多少正经程序员啊,读 excel 查个分数什么妖魔鬼怪都出来了.
    xavierskip
        19
    xavierskip   2018-11-01 19:17:10 +08:00
    Excel 数据转换成 CSV 格式的数据,一切都好办了。
    shijingshijing
        20
    shijingshijing   2018-11-01 19:19:17 +08:00
    VBA 了解一下~ 复杂点的上 VSTO 直接 C#搞定
    XuanFei990
        21
    XuanFei990   2018-11-01 19:22:57 +08:00
    eeeeee

    是不是可以用 excel VBA 来访问数据库,来查找数据???
    JCZ2MkKb5S8ZX9pq
        22
    JCZ2MkKb5S8ZX9pq   2018-11-01 19:30:04 +08:00
    好一点嘛直接 pandas。

    或者 db 导出 csv,导入 excel,vlookup 还是 match 一下(太久没用忘了)。
    shm7
        23
    shm7   2018-11-01 19:30:36 +08:00 via iPhone
    还好我用 pandas,dataframe[dataframe.id.map(lambda x: x in ids)]
    proudofmyself911
        24
    proudofmyself911   2018-11-01 20:57:36 +08:00 via Android
    直接导出来 vlookup 不就完事了。。。
    一万条而已,不需要搞那么复杂
    lekai63
        25
    lekai63   2018-11-01 21:01:02 +08:00 via iPhone
    1 万条而已
    vlookup 了解下 直接公式解决
    lekai63
        26
    lekai63   2018-11-01 21:03:34 +08:00 via iPhone
    没看仔细 是要去数据库查啊。
    excel 数据选项卡下有数据 from 之类的东西 先用那个看看。不行的话可能要用 powerBI 之类工具
    如果会 vba 的话 写好链接语句,再 select
    明早我找找个代码给你
    Tumblr
        27
    Tumblr   2018-11-01 21:18:57 +08:00
    也许可以帮到你:
    JamesR
        28
    JamesR   2018-11-01 21:24:26 +08:00
    楼上弄 SQL 的都太麻烦了,几万行且简单一次查询而已,把数据全弄到 Excel 里 2 个 sheet,再一个 vlookup 公式搞定,缺点是慢,卡顿。

    如果查询次数多建议用 Access,主要是安装及使用简单,楼主的问题,安装花十分钟安装,操作熟练的话,导入表,拖拽下再查询 2 分钟搞定,比 Excel 要快不少。

    装个 SQLServer 或者 MySQL 什么的还不得半天。
    mikeven
        29
    mikeven   2018-11-01 22:25:18 +08:00 via iPhone
    搞什么 sql 啊,10 分钟学一下 vlookup 函数搞定
    bridgeca0
        30
    bridgeca0   2018-11-01 22:36:06 +08:00
    excel 里面复制 id,用逗号拼接一下 id,select id,score from student where id in ?
    KingEngine
        31
    KingEngine   2018-11-01 22:40:22 +08:00 via Android
    @ioschen id 列复制存在 txt 中,直接遍历 id 查询,然后存成 csv 格式,导入到 excel 中新表中,通过 lookup 在原表中添加对应成绩
    largecat
        32
    largecat   2018-11-01 22:40:34 +08:00 via Android
    关键词最多的就是数据库,

    也就一万条而已,
    一是读到内存里遍历,用第三方程序
    另外一个方法就是直接 excel 里用自带的 vba,
    太简单的东西你们都弄得好复杂,
    对一般用户来说装个数据库程序再配置好花个半天,
    largecat
        33
    largecat   2018-11-01 22:41:59 +08:00 via Android
    其实最简单的不是用第三方程序或者 excel 自带的 vba,
    而且 excel 单元格公式,一秒钟的事,所见即所得
    Myprincess
        34
    Myprincess   2018-11-01 22:43:51 +08:00
    POWER BI 了解一下,超级强大的数据透视工具。
    wqzjk393
        35
    wqzjk393   2018-11-01 22:45:25 +08:00 via iPhone
    几万条 vlookup 轻轻松松的。能不上数据库就别上,主要是因为数据表格式和 Excel 经常有冲突,比如数据表数字很长没问题,但是如果导出来用 Excel 打开不注意格式很容易后面几位数字全部保留成 0 了。大数据量几十上百万的话或者逻辑复杂就用 pandas 吧
    bridgeca0
        36
    bridgeca0   2018-11-01 22:46:36 +08:00
    各位注意审题啊,分数在数据库里面,说什么 lookup、access、vba 都出来了。。。。
    likuku
        37
    likuku   2018-11-01 23:00:22 +08:00
    如今伸手党这么开心啊....超幸福
    zealzz
        38
    zealzz   2018-11-01 23:04:38 +08:00 via Android
    直接放内存里,爱咋搞咋搞
    alcarl
        39
    alcarl   2018-11-02 00:06:17 +08:00 via Android
    。。。。。。这种手动操作,好歹说一下是用的什么库把,sqlserver 的话,只需要建个表,右键点他,编辑前 200 行,然后从 excel 里 ctrl+c,再回到 sqlserver 在右面打开的输入窗口中 ctrl+v 即可,sql 查询走起
    congeec
        40
    congeec   2018-11-02 00:11:09 +08:00
    Excel 现在不是能直接连数据库么...........
    sutra
        41
    sutra   2018-11-02 00:29:46 +08:00
    首先将 Excel 内的 ID 另存为 CSV 之类的纯文本文件,得到 ids.csv 。

    比如:
    $ cat ids.csv
    1
    2
    3

    然后生成 SQL 语句:
    $ cat ids.csv | awk 'BEGIN{ print "select id, score from student where id in("}{print "'\''"$1"'\'',"}END{ print "'\''0'\'');"}' > ids.sql

    生成的 SQL 如下:
    $ cat ids.sql
    select id, score from student where id in(
    '1',
    '2',
    '3',
    '0');

    将该 SQL 去数据库中查询,将查询结果导出成 CSV 格式(不同的数据库引擎或者数据库工具导出方式不一样,比如 psql -t -A -F ",")。
    CEBBCAT
        42
    CEBBCAT   2018-11-02 01:15:46 +08:00
    @likuku #37 这也许算是一个好问题,因为解法多样有挑战性
    PythonAnswer
        43
    PythonAnswer   2018-11-02 02:03:19 +08:00 via iPhone
    才 1w 行。现代 excel 能装 100w 行。
    8e47e42
        44
    8e47e42   2018-11-02 06:03:33 +08:00 via iPhone
    问导师要 20 个人的经费,然后请两个人请他们吃顿饭,让他们手动一个个去数据库里面查了抄进 excel 里。

    这是典型的印度人肉云的运作方式,有技术、有钱赚、有创新精神,最重要的是下次有同样需求的时候可以以人力成本上升为理由成倍涨价
    KingPL
        45
    KingPL   2018-11-02 09:12:29 +08:00
    作业不应该自己完成么...
    5ispy
        46
    5ispy   2018-11-02 10:42:44 +08:00
    @ioschen 对对对 场景是这样的
    5ispy
        47
    5ispy   2018-11-02 10:43:24 +08:00
    @sambohuang 额 我遇到的问题是 6 楼说的那样的,我在帖子里没说清楚。。
    5ispy
        48
    5ispy   2018-11-02 10:51:34 +08:00
    @alcarl 额。。。怪我没讲清楚具体场景。其实这个是工作中遇到到的问题,我只是简化了,真实的是数据库表里有 160w 条数据我需要用 excel 里的 id 去查数据库里的数据。oracle 数据库。
    5ispy
        49
    5ispy   2018-11-02 10:53:32 +08:00
    @8e47e42 你的段位明显超出程序员的范畴了,,,
    5ispy
        50
    5ispy   2018-11-02 10:57:30 +08:00
    @bridgeca0 用 in 的话 速度慢,没敢试怕把机器搞死,而且 in 里面数据量是有限制的,oracle 好像只能 1000 个
    5ispy
        51
    5ispy   2018-11-02 10:58:49 +08:00
    @Tumblr 这个是用 excel 直接连接的数据库?
    5ispy
        52
    5ispy   2018-11-02 11:00:28 +08:00
    @CivAx (⊙o⊙)哦!我去试一下,感谢!
    5ispy
        53
    5ispy   2018-11-02 11:01:23 +08:00
    @sutra 这样也是用的 sql 的 in 吧
    gaius
        54
    gaius   2018-11-02 11:14:52 +08:00
    11L 的方法不错 装个 Navicat
    Tumblr
        55
    Tumblr   2018-11-02 11:17:57 +08:00
    @5ispy #51 是的,Excel 本身就可以直接连接数据库的,装驱动就好,像 MySQL-Connector 啊,ODBC 之类的。
    其实你的这个问题,Google 一下很多的,https://www.google.com/search?q=excel+database+query+based+on+cell+value&newwindow=1
    shangfabao
        56
    shangfabao   2018-11-02 11:30:43 +08:00
    笨的方法,数据导出 excel,两个 excel 内容合并一下,Vlookup 一下,5 分钟之内直接搞定
    Gempty
        57
    Gempty   2018-11-02 11:34:18 +08:00
    连表查询了解一下
    sdalkesioe
        58
    sdalkesioe   2018-11-02 13:13:05 +08:00
    OPENROWSET 可以直接查啊
    windedge
        59
    windedge   2018-11-02 14:12:30 +08:00
    likuku
        60
    likuku   2018-11-02 14:32:14 +08:00
    @5ispy [额。。。怪我没讲清楚具体场景。其实这个是工作中遇到到的问题,我只是简化了,真实的是数据库表里有 160w 条数据我需要用 excel 里的 id 去查数据库里的数据。oracle 数据库。]


    额,不同数量级有时就是有完全不同的玩法(甚至是各自有完全不同的唯一解,同一时代条件),
    否则 大数据相关技术 就真成了个谎言 /笑话。
    allanzhuo
        61
    allanzhuo   2018-11-02 14:41:45 +08:00
    直接 in 一下不就 OK 了,楼上一个比一个复杂。
    1W 条数据直接在编辑工具替换为' xx',的形式,几秒钟就搞定了
    allanzhuo
        62
    allanzhuo   2018-11-02 14:43:13 +08:00
    11 楼就是我说的方法~~~给老铁点个赞
    expkzb
        63
    expkzb   2018-11-02 14:46:25 +08:00
    ID 复制到一个文本里,一行一个。利用 python 去读取 ID,并从数据库里获取相应数据就行了吧。不知道 lz 的代码能力如何。
    Les1ie
        64
    Les1ie   2018-11-02 17:02:31 +08:00
    pandas.read_excel().tosql()
    ccoming
        65
    ccoming   2018-11-02 18:06:04 +08:00
    excel 里面直接就能用 sql,况且还有查找函数、数据透视表、power query 这些功能在,想怎么弄就怎么弄
    saulshao
        66
    saulshao   2018-11-02 22:36:33 +08:00
    最快的做法是导入到数据库里,然后写 SQL 查询。
    楼上有人提到用文本编辑器拼 SQL 也是相当简单实用的办法。
    leavan
        67
    leavan   2018-11-03 11:21:38 +08:00
    postgresql 的 fdw 了解一下?
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1732 人在线   最高记录 5168   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 33ms · UTC 01:55 · PVG 09:55 · LAX 17:55 · JFK 20:55
    ♥ Do have faith in what you're doing.