我有一個由管道分隔的數據文件（參見下面的範例三行）。

我想要做的是“剔除”這些欄位並將它們放入變數中。它們具有預定義的長度。我想按照以下方式將文件轉換為 SQL 腳本：

輸入：

|          416|CAWNBORE LIMITED                                                                                              |CAWNBORE                 |     8|            0|     0|00.00     |            0|            0|********NO ADDRESS DETAILS*******                 |********NO ADDRESS DETAILS*******                 |********NO ADDRESS DETAILS*******                 |********NO ADDRESS DETAILS*******                 |            0|            0|            0|            0|            0|
|          431|MAIN HOLDINGS LIMITED                                                                                         |MAINHOLDINGSCHA          |     8|            0|     0|00.00     |            0|            0|********NO ADDRESS DETAILS*******                 |********NO ADDRESS DETAILS*******                 |********NO ADDRESS DETAILS*******                 |********NO ADDRESS DETAILS*******                 |            0|     19650509|            0|            0|            0|
|          432|DUBLIN NORTH CITY MILLING COMPANY LIMITED                                                                     |DUBLINNORTHCITY          |     8|            0|     1|00.00     |     18750125|     19830124|113 PHIBSBORO ROAD                                |DUBLIN                                            |                                                  |                                                  |       216410|     19901106|            0|     20030124|            0|

基本上，剝離管道 - 用逗號分隔數據並插入 SQL 字元串 INSERT INTO …. VALUES(data_in_here 逗號分隔);

期望的輸出：

INSERT INTO tbcrocompany (id_company, nm_company, id_keyword, cd_status, dt_company_status, cd_type, cd_principle_obj, dt_register, dt_last_ar, ad_line_1, ad_line_2, ad_line_3, ad_line_4, cd_town_number, dt_dissolved, dt_bond_expiry, dt_next_ar, dt_last_accounts) VALUES (416,'CAWNBORE LI|MITED','CAWNBORE',8,0,0, '00.00', 0, 0, '********NO ADDRESS DETAILS*******', '********NO ADDRESS DETAILS*******', '********NO ADDRESS DETAILS*******', '********NO ADDRESS DETAILS*******', 0, 0, 0, 0, 0);
INSERT INTO tbcrocompany (id_company, nm_company, id_keyword, cd_status, dt_company_status, cd_type, cd_principle_obj, dt_register, dt_last_ar, ad_line_1, ad_line_2, ad_line_3, ad_line_4, cd_town_number, dt_dissolved, dt_bond_expiry, dt_next_ar, dt_last_accounts) VALUES (431,'MAIN HOLDIN|GS LIMITED','MAINHOLDINGSCHA',8,0,0, '00.00', 0, 0, '********NO ADDRESS DETAILS*******', '********NO ADDRESS DETAILS*******', '********NO ADDRESS DETAILS*******', '********NO ADDRESS DETAILS*******', 0, 19650509, 0, 0, 0);
INSERT INTO tbcrocompany (id_company, nm_company, id_keyword, cd_status, dt_company_status, cd_type, cd_principle_obj, dt_register, dt_last_ar, ad_line_1, ad_line_2, ad_line_3, ad_line_4, cd_town_number, dt_dissolved, dt_bond_expiry, dt_next_ar, dt_last_accounts) VALUES (432,'DUBLIN NORTH CITY MILLING COMPANY LIMITED','DUBLINNORTHCITY',8,0,1, '00.00', 18750125, 19830124, '113 PHIBSBORO ROAD', 'DUBLIN', '', '', 216410, 19901106, 0, 20030124, 0);

假設您的數據在文件data中，這應該適用於合理的輸入（例如，您的數據中沒有|或換行符）：

sed -e 's/^ *| *//' -e 's/ *$//' -e 's/ *| */|/g' data |
   while IFS='|' read -r f1 f2 f3 f4 f5 f6
   do
       # INSERT INTO mt (F1, F2, F3, F4, F5, F6) VALUES ( 16524,01,'10/17/2012','3930621977','XXNPUES        ', 'S1');
       echo "INSERT INTO mt (F1, F2, F3, F4, F5, F6) VALUES ($f1,$f2,'$f3','$f4','$f5','$f6');"
   done

第一行從數據輸入中修剪前導和尾隨空格，並丟棄初始的|，因此| 416|CABlah |Somewhere else |變為416|CABlah|Somewhere else|。如果不適合您的案例，則可以完全修改或刪除表達式。

引用自：https://unix.stackexchange.com/questions/272001