用EXCEL解析集装箱装载EDI数据文件.docVIP

用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：///官方网站下载。 　　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的版本，船舶信息、码