用EXCEL解析集装箱装载EDI数据文件
朱育良
摘要：本文介绍利用MICROSOFT OFFICE中EXCEL电子表格程序从EDI文件中解析提取有关集装箱有关信息，并进行分类汇总。
关键词：EXCEL电子表格 EDI数据 数据解析 VBA SQL语句 分类汇总
0 引言
随着集装箱运输行业发展，并持续稳定增长，集装箱国际贸易进出口总额不断增加，随之而来的是作为国际贸易货物运输的主要方式之一的集装箱运输量的不断增加。为了抓住机遇并且迎接挑战，各航运公司加速发展集装箱运输船队，并逐步向大型化发展，为了应尽可能地提高集装箱运输中每个环节的效率。本文以此为契机，研究由口岸、代理、公司提供给船方的集装箱电子数据文件（EDI），通过解析集装箱电子数据文件（EDI），直接提取集装箱相关信息，包括积载位置、装卸港、货物重量和货物的一些特殊信息（如危险品、冷藏箱、超尺寸箱等），提高配载效率和质量，减轻船方使用船员的工作负担。
集装箱船舶的大型化，单港集装箱的装卸量大大增加，以往只能被动地等待码头方提供的各种所需数据，而且再次手动输入计算机进行文件编辑和传输，其间费时费力并且容易出现错误，本文介绍一种利用EXCEL中VBA编程功能，自动从港方提供的EDI文件中提取集装箱有关信息数据。
1 EDI简介
EDI是英文Electronic Data Interchange的缩写。联合国欧经会贸易程序简化工作组（UN/ECE/WP.4）于1994年9月23日在日内瓦举行的第40届会议上通过了EDI的技术定义。国际标准化组织（ISO）联席会议同年接受了这一定义：
Electronic Data Interchange（EDI）：The electronic transferor computer to computer of commercial or administrative transactions using as agreed standard to structured the transaction 0r message data。中文译为：电子数据交换（EDI）：是使用一种商定的标准来处理所涉及的交易或信息数据的结构，商业或行政交易事項，从计算机到计算机的电子传递。
目前被广泛使用的EDI格式国际标准主要有两个：UN/EDIFACT和ANSIX12，现在各口岸、代理、货运相关方一般采用UN/EDIFACT标准格式，常用的版本为V1.5和V2.0两个版本（2016年7月1日起实行VGM规则，BAPLIE采用2.2版本）。本文以BAPLIE（Bayplan/stowage plan occupied and empty locations message）V2.0为介绍主体。BAPLIE国际标准文件可在http：//www.smdg.org/官方网站下载。
2 EDI结构分析
一个标准的EDI数据根据其内容的功能和性质又可分为三个区：标题区：说明报文的性质；明细区：详细说明报文内容；结尾区：核对并结束报文；EDI标准由数据元、数据段和报文三要素组成的。多个数据元组成数据段，多个数据段组成报文。数据元是报文中的最基本的单位。两个或两个以上的数据元构成一个复合数据元，而此时的数据元称为子数据元。
一个数据段包括标识符唯一标识，段标识符由3个大写字母组成（属必需的M），数据元通过其在段中的位置来识别，数据元由“+”分隔，以单引号（）为数据段结束符。一份完整UN/EDIFACT MESSAGE结构如下：
本文所关心的是有关集装箱的信息，包括集装箱装载位置，重量、装卸港、集装箱的特殊信息等，撇开报头、报尾其他信息，着重解析集装箱数据。
一份完整的EDI数据应由UNH段、BGM段、DTM段、RFF段、NAD段、记录组1、记录组2和UNT段组成、（记录组1又是由TDT段、LOC段、DTM段、RFF段和FTX段组成，记录组2由LOC段、GID段、GDS段、FTX段、MEA段、DIM段、TMP段、RNG段、LOC段、RFF段）、记录组3和记录组4组成。（记录组3由EQD段、EQA段和NAD段组成，记录组4由DGS段和FTX段组成。）
每个集装箱信息以标识符LOC+147+开始，一个集装箱的具体数据按LOC（M）-GDS（C）-FTX（C）- MEA（M）-DIM（C）- TMP（C）-RNG（C）-LOC（M）-RFF（C）顺序（），下面以一个实例说明。
（备注：V1.5 为EDI-1.5版本，V2.0为EDI-1.5版本，V2.2为今年7月1日实行集装箱称重后启用的EDI-2.2版本，各版本的标识符是一致的。）
其中第1行至第8行为EDI的报头段，包括EDI的版本，船舶信息、码头信息及到离港信息，第9行至等28行为2个集装箱的完整信息，……表示其中省略了N个集装箱信息，第18421行至第18422行为文件结束段，本文有用的数据是第9行至第18420行的每个集装箱数据信息。
每个集装箱数据信息从LOC+147 集装箱装载位置开始，包括积载位置（LOC+147）、货物重量（MEA+WT或MEA+VGM），装货港（LOC+6）、卸货港（LOC+12）、承运人（NAD+CA），集装箱信息（EQD+CN，箱号、箱型、空/重）及特殊箱子（危险品（DGS）、冷藏箱（TMP）、超尺寸箱（DIM））等。
3 EXCEL解析过程
从上面的EDI信息分析看，集装箱数据按标识符有顺序的排列，我们就可以利用EXCEL中VBA编程功能，从EDI文件中解析出我们想知道的集装箱数据，解析过程按下面流程图进行（图3-2）。
VBA是MICROSOFT OFFICE办公软件集成的功能，是MICROSOFT VISUAL BASIC的简版，包含了VB的极大数功能。具有极强编程功能和执行力，与MICROSOFT OFFICE无缝衔接。
（1）工作表设计，新建一个EXCEL工作薄，并新建3个工作表，第一个工作表命名为“EDI”， 第二个工作表命名为“清单”， 第三个表命名为 “汇总表”。“EDI表”用于存放EDI原文，“清单”用于存放已解析出来的集装箱信息。 “汇总表”用于存放集装箱数量统计。
EDI原文以单引号（）为数据段结束符，每一行为一个数据段，在“EDI表”的A列用来存放EDI数据，在B1放入“=COUNTA（A：A）”公式，用于计算EDI的总行数。
第二个工作表“清单”的第一行分别填入“Data、ID、SIZE、WT、POL、POD、E、DG、CLASS、UNNO、RF、TEMP、UNIT”用来存放集装箱信息。（图3-1）
表“汇总表”可以设计成如图3-3的格式。
（2）VBA代码编写：VBA编辑器可用快捷键Alt-F11打开，VBA界面分为工程资源管理器、属性窗口、代码窗口，代码窗口内是输入VBA代码的区域。
4 功能使用：冷藏箱、危险品制作
如果要制作冷藏箱清单，按装货港、卸货港分别分类统计制作，可插入新的工作表，命名為RF，表格可按体系文件格式设计成如图4-1的式样。
如果要制作危险品清单，可将上述代码略作修改，就可完成，读者自行编辑。
另外，EXCEL本身具体很强的分析统计功能，读者可以通过使用分类统计、筛选、排序等功能，获得各种报表。
5 总结
EDI技术己经在集装箱运输的很多环节得到应用，本文是以EXCEL程序为依托，将解析的电子数据文件（EDI）应用于实际，逐步实现各部分功能。首先对EDI解析的原理和系统结构及EDI国际标准进行充分的了解，对真实船舶的电子数据文件（EDI）文件按照UN/EDIFACT标准进行解析，最终应用于实际运用完成集装箱信息的提取。在实现过程中遇到的主要问题和解决方法如下：
（1）电子数据文件（EDI）文件解析问题。通过对电子数据文件（EDI）文件进行结构分析和语句分析并结合实例逐步分析电子数据文件（EDI）文件的解析过程，完成了电子数据文件（EDI）文件解析。最终将上述解析过程通过VBA编程实现之。
（2）制作各种报表，减轻工作强度，减少输入误差；
（3）现在每次靠泊前，代理、口岸都会将预配载图报文EDI文件发船，船方可根据配载图报文EDI文件，预先制作出到港装卸作业用的各种清单。
（4）本文仅起到抛砖引玉的作用，更多的功能读者可以自行开发、扩展。
参考文献：
[1]李馥佳.EDI在集装箱配载中的应用[D].大连海事大学，2010，06.
[2]MICROSOFT OFFICE 帮助文档.
[3]UN/ECE WP.4，UN/EDIFACT Syntax Implementation Guidelines.l993.
[4]董延丹.EXCEL函数在集装箱业务中的应用[J].物流技术，2013，32（3）.
摘要：本文介绍利用MICROSOFT OFFICE中EXCEL电子表格程序从EDI文件中解析提取有关集装箱有关信息，并进行分类汇总。
关键词：EXCEL电子表格 EDI数据 数据解析 VBA SQL语句 分类汇总
0 引言
随着集装箱运输行业发展，并持续稳定增长，集装箱国际贸易进出口总额不断增加，随之而来的是作为国际贸易货物运输的主要方式之一的集装箱运输量的不断增加。为了抓住机遇并且迎接挑战，各航运公司加速发展集装箱运输船队，并逐步向大型化发展，为了应尽可能地提高集装箱运输中每个环节的效率。本文以此为契机，研究由口岸、代理、公司提供给船方的集装箱电子数据文件（EDI），通过解析集装箱电子数据文件（EDI），直接提取集装箱相关信息，包括积载位置、装卸港、货物重量和货物的一些特殊信息（如危险品、冷藏箱、超尺寸箱等），提高配载效率和质量，减轻船方使用船员的工作负担。
集装箱船舶的大型化，单港集装箱的装卸量大大增加，以往只能被动地等待码头方提供的各种所需数据，而且再次手动输入计算机进行文件编辑和传输，其间费时费力并且容易出现错误，本文介绍一种利用EXCEL中VBA编程功能，自动从港方提供的EDI文件中提取集装箱有关信息数据。
1 EDI简介
EDI是英文Electronic Data Interchange的缩写。联合国欧经会贸易程序简化工作组（UN/ECE/WP.4）于1994年9月23日在日内瓦举行的第40届会议上通过了EDI的技术定义。国际标准化组织（ISO）联席会议同年接受了这一定义：
Electronic Data Interchange（EDI）：The electronic transferor computer to computer of commercial or administrative transactions using as agreed standard to structured the transaction 0r message data。中文译为：电子数据交换（EDI）：是使用一种商定的标准来处理所涉及的交易或信息数据的结构，商业或行政交易事項，从计算机到计算机的电子传递。
目前被广泛使用的EDI格式国际标准主要有两个：UN/EDIFACT和ANSIX12，现在各口岸、代理、货运相关方一般采用UN/EDIFACT标准格式，常用的版本为V1.5和V2.0两个版本（2016年7月1日起实行VGM规则，BAPLIE采用2.2版本）。本文以BAPLIE（Bayplan/stowage plan occupied and empty locations message）V2.0为介绍主体。BAPLIE国际标准文件可在http：//www.smdg.org/官方网站下载。
2 EDI结构分析
一个标准的EDI数据根据其内容的功能和性质又可分为三个区：标题区：说明报文的性质；明细区：详细说明报文内容；结尾区：核对并结束报文；EDI标准由数据元、数据段和报文三要素组成的。多个数据元组成数据段，多个数据段组成报文。数据元是报文中的最基本的单位。两个或两个以上的数据元构成一个复合数据元，而此时的数据元称为子数据元。
一个数据段包括标识符唯一标识，段标识符由3个大写字母组成（属必需的M），数据元通过其在段中的位置来识别，数据元由“+”分隔，以单引号（）为数据段结束符。一份完整UN/EDIFACT MESSAGE结构如下：
本文所关心的是有关集装箱的信息，包括集装箱装载位置，重量、装卸港、集装箱的特殊信息等，撇开报头、报尾其他信息，着重解析集装箱数据。
一份完整的EDI数据应由UNH段、BGM段、DTM段、RFF段、NAD段、记录组1、记录组2和UNT段组成、（记录组1又是由TDT段、LOC段、DTM段、RFF段和FTX段组成，记录组2由LOC段、GID段、GDS段、FTX段、MEA段、DIM段、TMP段、RNG段、LOC段、RFF段）、记录组3和记录组4组成。（记录组3由EQD段、EQA段和NAD段组成，记录组4由DGS段和FTX段组成。）
每个集装箱信息以标识符LOC+147+开始，一个集装箱的具体数据按LOC（M）-GDS（C）-FTX（C）- MEA（M）-DIM（C）- TMP（C）-RNG（C）-LOC（M）-RFF（C）顺序（），下面以一个实例说明。
（备注：V1.5 为EDI-1.5版本，V2.0为EDI-1.5版本，V2.2为今年7月1日实行集装箱称重后启用的EDI-2.2版本，各版本的标识符是一致的。）
其中第1行至第8行为EDI的报头段，包括EDI的版本，船舶信息、码头信息及到离港信息，第9行至等28行为2个集装箱的完整信息，……表示其中省略了N个集装箱信息，第18421行至第18422行为文件结束段，本文有用的数据是第9行至第18420行的每个集装箱数据信息。
每个集装箱数据信息从LOC+147 集装箱装载位置开始，包括积载位置（LOC+147）、货物重量（MEA+WT或MEA+VGM），装货港（LOC+6）、卸货港（LOC+12）、承运人（NAD+CA），集装箱信息（EQD+CN，箱号、箱型、空/重）及特殊箱子（危险品（DGS）、冷藏箱（TMP）、超尺寸箱（DIM））等。
3 EXCEL解析过程
从上面的EDI信息分析看，集装箱数据按标识符有顺序的排列，我们就可以利用EXCEL中VBA编程功能，从EDI文件中解析出我们想知道的集装箱数据，解析过程按下面流程图进行（图3-2）。
VBA是MICROSOFT OFFICE办公软件集成的功能，是MICROSOFT VISUAL BASIC的简版，包含了VB的极大数功能。具有极强编程功能和执行力，与MICROSOFT OFFICE无缝衔接。
（1）工作表设计，新建一个EXCEL工作薄，并新建3个工作表，第一个工作表命名为“EDI”， 第二个工作表命名为“清单”， 第三个表命名为 “汇总表”。“EDI表”用于存放EDI原文，“清单”用于存放已解析出来的集装箱信息。 “汇总表”用于存放集装箱数量统计。
EDI原文以单引号（）为数据段结束符，每一行为一个数据段，在“EDI表”的A列用来存放EDI数据，在B1放入“=COUNTA（A：A）”公式，用于计算EDI的总行数。
第二个工作表“清单”的第一行分别填入“Data、ID、SIZE、WT、POL、POD、E、DG、CLASS、UNNO、RF、TEMP、UNIT”用来存放集装箱信息。（图3-1）
表“汇总表”可以设计成如图3-3的格式。
（2）VBA代码编写：VBA编辑器可用快捷键Alt-F11打开，VBA界面分为工程资源管理器、属性窗口、代码窗口，代码窗口内是输入VBA代码的区域。
4 功能使用：冷藏箱、危险品制作
如果要制作冷藏箱清单，按装货港、卸货港分别分类统计制作，可插入新的工作表，命名為RF，表格可按体系文件格式设计成如图4-1的式样。
如果要制作危险品清单，可将上述代码略作修改，就可完成，读者自行编辑。
另外，EXCEL本身具体很强的分析统计功能，读者可以通过使用分类统计、筛选、排序等功能，获得各种报表。
5 总结
EDI技术己经在集装箱运输的很多环节得到应用，本文是以EXCEL程序为依托，将解析的电子数据文件（EDI）应用于实际，逐步实现各部分功能。首先对EDI解析的原理和系统结构及EDI国际标准进行充分的了解，对真实船舶的电子数据文件（EDI）文件按照UN/EDIFACT标准进行解析，最终应用于实际运用完成集装箱信息的提取。在实现过程中遇到的主要问题和解决方法如下：
（1）电子数据文件（EDI）文件解析问题。通过对电子数据文件（EDI）文件进行结构分析和语句分析并结合实例逐步分析电子数据文件（EDI）文件的解析过程，完成了电子数据文件（EDI）文件解析。最终将上述解析过程通过VBA编程实现之。
（2）制作各种报表，减轻工作强度，减少输入误差；
（3）现在每次靠泊前，代理、口岸都会将预配载图报文EDI文件发船，船方可根据配载图报文EDI文件，预先制作出到港装卸作业用的各种清单。
（4）本文仅起到抛砖引玉的作用，更多的功能读者可以自行开发、扩展。
参考文献：
[1]李馥佳.EDI在集装箱配载中的应用[D].大连海事大学，2010，06.
[2]MICROSOFT OFFICE 帮助文档.
[3]UN/ECE WP.4，UN/EDIFACT Syntax Implementation Guidelines.l993.
[4]董延丹.EXCEL函数在集装箱业务中的应用[J].物流技术，2013，32（3）.
