database - BCP 带有逗号分隔符的文件

Question

我得到一个逗号分隔格式的文件，需要将数据 BCP 到 Sybase 服务器，但这里的问题是几列中的数据也有用逗号分隔的描述。当我在 .fmt 格式文件中给出“，”终止符时，它会整理数据库中导入的数据。有没有机会从java程序或任何东西中删除逗号？如果您观察该行，您可以看到突出显示的列数据，这是我有很多其他的列

例如：- 是的，格式化不顺利，提供文件的用户并没有让步

 filed 1, Rachel Green, "Pink, Panther", Bank Of,America,

这里粉红色的路径正在以完美的方式复制到 DB，但美国银行被复制到两列

9631450,PIMCO98,MSBNA,PIMCO-2498/MSBNA,28-268258-0-6AG34,4HMC3,,,,,Bnd - Rese,Bnd - Revse,Rec,9500.0,UD,5100.0,SD,017-08-strong text16,2017-08-17,2018-08-17,US12651DAG34,US12651DAG34,88.19331,634163.37,USD,2018-08-13,605767.68,605767.68,6.376502,,,Match with Diff,Major,2,mtm,,"trade_date,fixed_coupon,10_pct_diff,strike_price,start_date",2018-04-06,>=3m,"mtm:2018-04-06,10_pct_diff:2018-04-06","mtm:>=3m,10_pct_diff:>=3m",Approved,Pending,"Simarjit Sandhu (PIMCO) 2018-08-10 21:46: We confirm marks.MS to confirm marks and provide price,Simarjit Sandhu (PIMCO) 2018-06-29 20:26: MS need to confirm the price and pricing sources. Please upload your comment in Tri-optima,Simarjit Sandhu (PIMCO) 2018-06-22 22:16: MS need to confirm the price and pricing sources. Please upload your comment in Tri-optima,Simarjit Sandhu (PIMCO) 2018-06-12 22:08: As per Tammy on 6/11 confirmed marks and using internal pricing,Suraj Shukla (PIMCO) 2018-04-06 16:01: We see MS missing trade ,, MS to confirm & advise on upload status.,Suraj Shukla (PIMCO) 2018-03-23 18:13: We are In-Line with our price ,,marks & pricing source. MS to confirm & advise on their marks & pricing source.,Suraj Shukla (PIMCO) 2018-03-12 18:50: We are In-Line with our price ,,marks & pricing source. MS to confirm & advise on their marks & pricing source.,Chetan Chandak (PIMCO) 2018-02-20 15:55: MS drop down  the position. MS to confirm and re-upload.,Chetan Chandak (PIMCO) 2017-11-13 21:57: MS need to confirm the price and pricing sources. Please upload your comment in Tri-optima.,Chetan Chandak (PIMCO) 2017-08-21 16:07: MS missing trade. MS to confirm and upload.",,Incomplete,2018-04-10,,,None,Trade Booking,Broker missing trade,2018-04-06 15:02:03,,,,,,9500000,,,,88.19331,,,,,1.0,,,Bond,,,,PIMCO Income Opportunity Fund,"**Morgan Stanley Bank, National Association"**,MSB,,Suraj Shukla,2018-04-06 15:02:03,0,https://secure.trioptima.com/exposure/reconciliations/PIMCO/MS/GMRA/browse/?q=trade_id%3D2498-268258-0-US12651DAG34,2017-08-16,GMRA,,TRI486670,,,,9500000.0,,,,,,,,,,,26061022,Closed,2018-04-06,Suraj Shukla,,,,,,2018-08-17,,,,,,,,,PIMCO,Pacific Investment Management Company LLC,MS,Morgan Stanley,DXHAUNK4X09LWO5HLA94,G1MLHIS0N32I3QPILB75,,,,,,,,,,,Swap Dealer,,,,,,,PIMCO,"mtm:91,10_pct_diff:91",,,,,,,,,,,,,,2.59113,,,,,,,Resolved,,persistent,Bilateral,

标签： databaseunixsybasecommabcp

Answer 1

注意：可能有一种巧妙的方法可以做到这一点sed，但不要看我......

使用awk我建议将当前分隔符（逗号）切换为其他字符（例如，管道|）：

awk '
BEGIN { FS="\"" ; OFS="" }
{ for (i = 1; i <= NF; i+=2) { gsub(",","|",$i) }
    print $0
}' <input_file>

• FS="\"" : 将双引号 ( ") 定义为 awk 的输入分隔符
• OFS="" : 定义一个空格作为 awk 的输出分隔符
• for (...)/gsub ：遍历奇数字段（即双引号之外的所有内容，用管道替换所有出现的逗号
• 此时，双引号内的所有逗号将作为数据的一部分保留在原处
• 打印新行

以及针对您的示例输入运行上述结果的结果：

9631450|PIMCO98|MSBNA|PIMCO-2498/MSBNA|28-268258-0-6AG34|4HMC3|||||Bnd - Rese|Bnd - Revse|Rec|9500.0|UD|5100.0|SD|017-08-strong text16|2017-08-17|2018-08-17|US12651DAG34|US12651DAG34|88.19331|634163.37|USD|2018-08-13|605767.68|605767.68|6.376502|||Match with Diff|Major|2|mtm||trade_date,fixed_coupon,10_pct_diff,strike_price,start_date|2018-04-06|>=3m|mtm:2018-04-06,10_pct_diff:2018-04-06|mtm:>=3m,10_pct_diff:>=3m|Approved|Pending|Simarjit Sandhu (PIMCO) 2018-08-10 21:46: We confirm marks.MS to confirm marks and provide price,Simarjit Sandhu (PIMCO) 2018-06-29 20:26: MS need to confirm the price and pricing sources. Please upload your comment in Tri-optima,Simarjit Sandhu (PIMCO) 2018-06-22 22:16: MS need to confirm the price and pricing sources. Please upload your comment in Tri-optima,Simarjit Sandhu (PIMCO) 2018-06-12 22:08: As per Tammy on 6/11 confirmed marks and using internal pricing,Suraj Shukla (PIMCO) 2018-04-06 16:01: We see MS missing trade ,, MS to confirm & advise on upload status.,Suraj Shukla (PIMCO) 2018-03-23 18:13: We are In-Line with our price ,,marks & pricing source. MS to confirm & advise on their marks & pricing source.,Suraj Shukla (PIMCO) 2018-03-12 18:50:We are In-Line with our price ,,marks & pricing source. MS to confirm & advise on their marks & pricing source.,Chetan Chandak (PIMCO) 2018-02-20 15:55: MS drop down  the position. MS to confirm and re-upload.,Chetan Chandak (PIMCO) 2017-11-13 21:57: MS need to confirm the price and pricing sources. Please upload your comment in Tri-optima.,Chetan Chandak (PIMCO) 2017-08-21 16:07: MS missing trade. MS to confirm and upload.||Incomplete|2018-04-10|||None|Trade Booking|Broker missing trade|2018-04-06 15:02:03||||||9500000||||88.19331|||||1.0|||Bond||||PIMCO Income Opportunity Fund|**Morgan Stanley Bank, National Association**|MSB||Suraj Shukla|2018-04-06 15:02:03|0|https://secure.trioptima.com/exposure/reconciliations/PIMCO/MS/GMRA/browse/?q=trade_id%3D2498-268258-0-US12651DAG34|2017-08-16|GMRA||TRI486670||||9500000.0|||||||||||26061022|Closed|2018-04-06|Suraj Shukla||||||2018-08-17|||||||||PIMCO|Pacific Investment Management Company LLC|MS|Morgan Stanley|DXHAUNK4X09LWO5HLA94|G1MLHIS0N32I3QPILB75|||||||||||Swap Dealer|||||||PIMCO|mtm:91,10_pct_diff:91||||||||||||||2.59113|||||||Resolved||persistent|Bilateral|

注意：显然，您需要 a) 选择一个未显示在数据中的新分隔符（在此示例中为管道）和 b) 调整bcp命令以使用新分隔符