2006-5-9 从文本文件导出数据到 Access 全实录 By Stabx

最新推荐文章于 2024-07-29 16:41:47 发布

原创最新推荐文章于 2024-07-29 16:41:47 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#access #compiler #interface #network #search #system

MS ASP 同时被 2 个专栏收录

78 篇文章

订阅专栏

MS Access

36 篇文章

订阅专栏

本文介绍如何使用正则表达式批量处理大量文本数据，并将其导入Access数据库的方法。通过文本编辑工具进行数据格式转换，确保数据正确导入。

标题: 2006-5-9 从文本文件导出数据到 Access 全实录 By Stabx

正文:

QUOTE:

主要使用技术为正则表达式.
工具为 (EditPlus 或 Search and Replace) 与 Access 2003.
分为文本操作与数据操作两部分

("[a-zA-Z0-9/-/!]+",")
/n/1

1. 文本操作
2. Access 操作

CODE:

1. 文本操作

要导入的文件是一些 IT 术语文件, 记录确切数不详, 大概有十五万多行, 分为 A-Z 各个文本文件.

一个一个操作文件, 那很麻烦, 我也没耐心.

先把文本文件合并, 打开 CMD 转到当前路径, "copy *.txt glossary.txt",合并文件.

打开文件观察文件规律, 有规律才好办, 没规律可难搞.

文件大致内容:
---/---------------------------------------------------------------------------------
ASPI

{Advanced SCSI Peripheral Interface}

ASPIK

<language, specification> A multiple-style specification
language.

["Algebraic Specifications in an Integrated Software
Development and Verification System", A. Voss, Diss, U
Kaiserslautern, 1985].

(1994-11-30)

Aspirin

<language, tool> A {freeware} language from {MITRE Corp} for
the description of {neural network}s. A compiler, bpmake, is
included. Aspirin is designed for use with the {MIGRAINES}
interface.

{Version: 6.0 (ftp://ftp.cognet.ucla.edu/alexis/)}

(1995-03-08)

ASPLE

<language> A {toy language}.

["A Sampler of Formal Definitions", M. Marcotty et al,
Computing Surveys 8(2):191-276 (Feb 1976)].

(1995-02-08)
---/---------------------------------------------------------------------------------
哦, 很有规律是吧, 那就好办.

我需要的是, 标题, 内容.

格式应该为 "标题","内容 , 公式应该为 "*","

首先应当替换标题, 然后再清除换行符, 再加上术语换行, 文本文件操作为此.

替换标题:
使用 EditPlus , 按CTRL+H
输入正则为: (^[a-zA-Z0-9/-/!]+.*)
替换正则为: "/1","
执行操作

结果:
---/---------------------------------------------------------------------------------
"ASPI","

{Advanced SCSI Peripheral Interface}

"ASPIK","

<language, specification> A multiple-style specification
language.

["Algebraic Specifications in an Integrated Software
Development and Verification System", A. Voss, Diss, U
Kaiserslautern, 1985].

(1994-11-30)

"Aspirin","

<language, tool> A {freeware} language from {MITRE Corp} for
the description of {neural network}s. A compiler, bpmake, is
included. Aspirin is designed for use with the {MIGRAINES}
interface.

{Version: 6.0 (ftp://ftp.cognet.ucla.edu/alexis/)}

(1995-03-08)

"ASPLE","

<language> A {toy language}.

["A Sampler of Formal Definitions", M. Marcotty et al,
Computing Surveys 8(2):191-276 (Feb 1976)].

(1995-02-08)
---/---------------------------------------------------------------------------------
清除换行符, 这主要是为了正确的导入数据到 Access.

输入查找正则: /n
替换内容:

结果:
---/---------------------------------------------------------------------------------
"ASPI"," {Advanced SCSI Peripheral Interface} "ASPIK"," <language, specification> A multiple-style specification language. ["Algebraic Specifications in an Integrated Software Development and Verification System", A. Voss, Diss, U Kaiserslautern, 1985]. (1994-11-30) "Aspirin"," <language, tool> A {freeware} language from {MITRE Corp} for the description of {neural network}s. A compiler, bpmake, is included. Aspirin is designed for use with the {MIGRAINES} interface. {Version: 6.0 (ftp://ftp.cognet.ucla.edu/alexis/)} (1995-03-08) "ASPLE"," <language> A {toy language}. ["A Sampler of Formal Definitions", M. Marcotty et al, Computing Surveys 8(2):191-276 (Feb 1976)]. (1995-02-08)
---/---------------------------------------------------------------------------------
现在应当加上换行符, 每一条数据为一行.

输入查找正则: ("[a-zA-Z0-9/-/!]+",")
替换内容: /n/1

结果:
---/---------------------------------------------------------------------------------
"ASPI"," {Advanced SCSI Peripheral Interface} 
"ASPIK"," <language, specification> A multiple-style specification language. ["Algebraic Specifications in an Integrated Software Development and Verification System", A. Voss, Diss, U Kaiserslautern, 1985]. (1994-11-30) 
"Aspirin"," <language, tool> A {freeware} language from {MITRE Corp} for the description of {neural network}s. A compiler, bpmake, is included. Aspirin is designed for use with the {MIGRAINES} interface. {Version: 6.0 (ftp://ftp.cognet.ucla.edu/alexis/)} (1995-03-08) 
"ASPLE"," <language> A {toy language}. ["A Sampler of Formal Definitions", M. Marcotty et al, Computing Surveys 8(2):191-276 (Feb 1976)]. (1995-02-08)
---/---------------------------------------------------------------------------------

这样就完成了文本操作, 现在差的是导入到 Access 中去.

当然, 这几条数据是很容易很不需要时间就替换好了, 不过十多万行的替换操作有够呛.
且 EditPlus 也不怎么完善, 数据过多, 就无法逐个替换, 只好多执行几次.

事实上我使用 EditPlus 根本无法完成我要的操作, 改成 Search and Replace 后轻松完成.

---/---------------------------------------------------------------------------------

Search and Replace 操作以上操作

1. 替换标题
搜索: ^[a-zA-Z0-9/./-/!]+
替换: "%1%2%3","

2. 清除换行符
搜索: /n
替换:

3. 每条目加上一个换行符
搜索: "[a-zA-Z0-9/./-/!]+"," 
替换: /n"%1%2%3","

最终用 Search And Replace 处理完后是 12739 行
篇数为 7640篇, 真不少, 七千多条术语.

---/---------------------------------------------------------------------------------

2. Access 操作

打开数据库, 空白处右击, 点导入, 选择有 *.txt 项的选项.
如没有编码方面的问题, 一切默认.

导入完成后我打开 Glossary数据库表一看, 有29 个字段, 呵呵, 不过除了字段1 字段2 之外, 其余全是 Null.
删除不必要的字段就剩下了 ID 字段1 字段2 三个字段, 这就是我要的.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' subject : 2006-5-9 从文本文件导出数据到 Access 全实录 By Stabx
'
' writer : Stabx<shawl.qiu@gmail.com>
'
' blog : http://blog.youkuaiyun.com/btbtd / http://btbtd.exblog.jp/
'
' blog/site : Phoenix.GI - P.GI / / 绿色学院 - Green Institute
'
' date : 2006-5-10
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''