如何将带有不均匀间距的无格式平面文件作为分隔符加载到ssis中的两个标题？ [英] How to load an unformatted flat file with uneven spacing as delimiter and two headers in ssis?
问题描述
我有一个任务要加载奇怪格式的文本文件。文件也包含不需要的数据。文件背靠背包含两个标题，并且在备用行上指定每个标题的数据。标题行在（------）之后开始。需要读取标题及其对应的数据，并使用它将其转储到某个excel / table目标中。让我知道如何使用ssis中的任何转换来解决这个问题，或者可以使用脚本。
不知道如何使用脚本任务。
文件数据：
Hi,
I got a task to load strangely formatted text file. File contains unwanted data too. File contains two headers back to back and data for each header is specified on alternate line.Header rows start after (------). Need to read both the header along with its corresponding data and dump it into some excel/table destination using. Let me know how to solve this using any transformation in ssis or may be with script.
Don't know how to use script task for this.
File data:
PAGE 2 BATCH ABC PAYMENT DATE & DUE DATE EDIT PAGE 481
------------------------------------------------------------------------------------------------------------------------------------
SEO XRAT CLT LOAN OPENING PAYMENT MATURIUH LOAN NEXE ORIG-AMT OFF TO CATE CONTC MON NO.TO TOL NEL S CUP CO IND PAT
NOM CODE NOM NOMTER DATE DUO DATE DATE TIME PT # MONEY AQ LOAN NUMBER BLOCK PAYMENT U TYP GH OMG IND
1-3 4-6 7-13/90-102 14-19 20-25 26-31 32-34 35-37 38-46 47-48 49 50-51 52-61 62 63 64-72 73 4-5 76 77 8-80
------------------------------------------------------------------------------------------------------------------------------------
SEO XRAT CLT LOAN A/C A/C MIN MAX MAX PENDI LATE CCH L/F PARTLYS CUR L/F L/F L/F
NOM CODE NOM NOMTER CODE FACTOR MON MON ROAD DAYS MONE POT L/A L/F JAC INT VAD CD USED PI VAD DT
1-3 4-6 7-13/90-102 14 15 20-23 24-29 30-34 35-37 38-42 43 44 49 60 61-63 64-69
USED-ID:
------------------------------------------------------------------------------------------------------------------------------------
454542 070 567 2136547895 08-08-18 08-06-18 11-02-18 123 256 62,222 LK 5 55 5463218975 5 3 5,555.22 33 H55
025641 055 123 5144511352 B .55321 2.55 6531.22 H #AS
454542 070 567 2136547895 08-08-18 08-06-18 11-02-18 123 256 62,222 LK 5 55 5463218975 5 3 5,555.22 33 H55
025641 055 123 5144511352 B .55321 2.55 6531.22 H #AS
454542 070 567 2136547895 08-08-18 08-06-18 11-02-18 123 256 62,222 LK 5 55 5463218975 5 3 5,555.22 33 H55
025641 055 123 5144511352 B .55321 2.55 6531.22
输出将包含两个文件：
File1：
Output will contain two file:
File1:
SEO XRAT CLT LOAN OPENING PAYMENT MATURIUH LOAN NEXE ORIG-AMT OFF TO CATE CONTC MON NO.TO TOL NEL S CUP CO IND PAT
NOM CODE NOM NOMTER DATE DUO DATE DATE TIME PT # MONEY AQ LOAN NUMBER BLOCK PAYMENT U TYP GH OMG IND
454542 070 567 2136547895 08-08-18 08-06-18 11-02-18 123 256 62,222 LK 5 55 5463218975 5 3 5,555.22 33 H55
454542 070 567 2136547895 08-08-18 08-06-18 11-02-18 123 256 62,222 LK 5 55 5463218975 5 3 5,555.22 33 H55
454542 070 567 2136547895 08-08-18 08-06-18 11-02-18 123 256 62,222 LK 5 55 5463218975 5 3 5,555.22 33 H55
文件2：
File 2:
SEO XRAT CLT LOAN A/C A/C MIN MAX MAX PENDI LATE CCH L/F PARTLYS CUR L/F L/F L/F
NOM CODE NOM NOMTER CODE FACTOR MON MON ROAD DAYS MONE POT L/A L/F JAC INT VAD CD USED PI VAD DT
025641 055 123 5144511352 B .55321 2.55 6531.22 H #AS
025641 055 123 5144511352 B .55321 2.55 6531.22 H #AS
025641 055 123 5144511352 B .55321 2.55 6531.22 H #AS
我尝试了什么：
现在我正在读取一列中的文件并手动使用派生列尝试将其拆分子串函数。但这仅适用于一个标头，而且它的编码类型太硬。需要一些动态方法来直接读取标题行和数据行。
What I have tried:
Right now i am reading file in one column and using derived column manually trying to split it using substring function. But that works for only one header and it is too hard coded type. Need some dynamic approach to read header rows as well as data row directly.
推荐答案
创建一个平面文件导入程序（使用导入/导出向导）并按照提示操作。平面文件的一个选项是固定长度字段或类似的东西。谷歌是你的朋友。
Create a flat-file importer (using the import/export wizard) and follow the prompts. One of the options for a flat file is "fixed length fields" or something like that. Google is your friend.
这篇关于如何将带有不均匀间距的无格式平面文件作为分隔符加载到ssis中的两个标题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
