RegEx，用于解析包含"JSON"格式的JSON的CSV [英] RegEx for parsing CSV that contains JSON in a "column"
问题描述
我需要解析一个逗号分隔的文本文件，其中一行可以包含json作为列值.该文件还包含一个标题行.
I need to parse a comma separated text file where a line can contain a json as column value. The file also contains a header row.
我试图构建一个正则表达式，以便可以解析文件并为每一行创建一个哈希图，并为该文件创建哈希图列表.
I am trying to build a regular expression, so that I can parse the file and create a hashmap for each line and list of hashmaps for the file.
我意识到正则表达式可能会为此而变得过于复杂，但是我不知道该如何设计代码来获取每一行的哈希图.
I realise that regex probably is going to be over complicated for this, but I don't know how else can I design the code to get the hashmap for each row.
我尝试了不同的方法-从SO和其他站点上的示例中获取信息，但是无法获得良好的工作表达.它们作用于不同部分，但不适用于整个数据
I tried different ways - from examples on SO and other sites, but am unable to get a good working expression. They work on different parts but not on the whole data
String[] values = readLine.split(",(?=([^\"]*\"[^\"]*\")*(?![^\"]*\"))", -1);
String[] values = readLine.split(",(?=(?:(?:[^'\",]*(?:'|\")){2})*[^'\",]*)", -1);
String[] values = readLine.split(",(\"([^\"]|\"\")*\")", -1);
String[] values = readLine.split(",(\\w+\\s)?(\"[^\"]+\"|\\w+)(\\(\\w\\d(,\\w\\d)*\\))?", -1);
String[] values = readLine.split(",(?=(?:(?:[^'",]*(?:'|")){2})*[^'",]*$)", -1);
下面是数据样本
id,apptoolID,apptoolUUID,accountNumber,accountName,name,description,mac,status,pGroups,oemCode,oemTagList,locationID,userCode,businessUnit,customerDescription,notification,ptdd,hdptdd,ptddSchedule,compLive,optInfo,config1,scriptDebugging,clearLocalStorage,created_at,updated_at
5703,2535,9e849f81-56c4-4415-b36e-b0fd370b3986,21126,"3 Way ChProduct",ZYX-21126TC-P1,"3 Way ChProduct - CHSH2112601DSHCL - Showroom",24-1c-04-0a-84-9c,ACTIVE,"[{"id":46,"name":"AX_AGRP_SIRIUSXM_BAC_AXP_GROUP","description":"Shops that can play the AX created\/provided Sirius XM creative.","numberOfPlayers":4494},{"id":33,"name":"AX_ZYX_CUSTOMER","numberOfPlayers":4532},{"id":11,"name":"PST Update","numberOfPlayers":604},{"id":2,"name":"Customer Players","numberOfPlayers":5711},{"id":41,"name":"AX_AGRP_EXTRA_PRICING_PLAN_BAC_AXP_GROUP","description":"AX auto-group Extra Pricing Plan BAC Group","numberOfPlayers":2215},{"id":39,"name":"AX_AGRP_MY_REWARDS_NATIONAL_GROUP","description":"AX auto-group My Rewards (National) BAC List","numberOfPlayers":2130}]",CH,"ChProduct,ALL,SHOP-SPECIFIC",CHSH2112601DSHCL,"{"oemCode":"222923"}",Showroom,Showroom,false,false,false,,false,"{"OPT_IN_FT":false,"OPT_IN_PSA":false,"OPT_IN_SP":false,"OPT_IN_TS":false,"OPT_IN_VS":false,"OPT_IN_WA":false,"OPT_OUT_ZYX":false,"OPT_OUT_SHOPCONTENT":false,"OPT_OUT_FA":false,"OPT_OUT_GT":false,"OPT_IN_TT":false}",2X2,false,false,"2019-02-18 20:57:53","2019-02-20 19:16:08"
5704,4248,494af61a-54ca-4ff2-a82d-7e795fd671ac,21126,"3 Way ChProduct",ZYX-21126TC-P2,"3 Way ChProduct - CISW2112602DTHCL - Service Waiting Area",e0-d5-5e-63-51-b9,ACTIVE,"[{"id":46,"name":"AX_AGRP_SIRIUSXM_BAC_AXP_GROUP","description":"Shops that can play the AX created\/provided Sirius XM creative.","numberOfPlayers":4494},{"id":33,"name":"AX_ZYX_CUSTOMER","numberOfPlayers":4532},{"id":2,"name":"Customer Players","numberOfPlayers":5711},{"id":27,"name":"FLEX Shop TV Players","description":"FLEX Shop TV Players group","numberOfPlayers":169},{"id":41,"name":"AX_AGRP_EXTRA_PRICING_PLAN_BAC_AXP_GROUP","description":"AX auto-group Extra Pricing Plan BAC Group","numberOfPlayers":2215},{"id":39,"name":"AX_AGRP_MY_REWARDS_NATIONAL_GROUP","description":"AX auto-group My Rewards (National) BAC List","numberOfPlayers":2130}]",CI,"ChProduct,CADILLAC,ALL,SHOP-SPECIFIC",CISW2112602DTHCL,"{"oemCode":"222923"}",Service_Lounge,"Service Waiting Area",true,true,false,,false,"{"OPT_IN_FT":false,"OPT_IN_PSA":"PSA337,PSA335,PSA332,PSA338,PSA326,PSA323,PS212,PS213,PSA293,PSA289","OPT_IN_SP":"SPCH1807","OPT_IN_TS":false,"OPT_IN_VS":false,"OPT_IN_WA":false,"OPT_OUT_ZYX":false,"OPT_OUT_SHOPCONTENT":"044077,034160,029205,022398,019888,019881,019880,019860,017596,010857","OPT_OUT_FA":"FACA1900,FACA1903,FACA1904,FACA1902,FACA1901,FACA1704,FACA1705,FACA1702,FACA1703,FACA1700,FACA1701,FACA1625,FACA1624,FACA1623,FACA1602,FACA1601,FACA1603,FACA0025,FACA0024,FACA0023,FACA1621,FACA00020,FACA00019,FACA1622,FACH1701","OPT_OUT_GT":false,"OPT_IN_TT":"TT043,TT037,TT033,TT032,TT031,TT027,TT028,TT026,TT029,TT030"}",2X2,false,false,"2019-02-18 20:57:53","2019-02-20 19:16:08"
5705,2537,94c4e9dc-e94e-4942-862a-1e4c98276f09,21126,"3 Way ChProduct",ZYX-21126TC-P3,"3 Way ChProduct - CHMA2112603MBHCL - Service Advisor Area & Service Waiting Area",24-1c-04-0a-84-b7,ACTIVE,"[{"id":46,"name":"AX_AGRP_SIRIUSXM_BAC_AXP_GROUP","description":"Shops that can play the AX created\/provided Sirius XM creative.","numberOfPlayers":4494},{"id":33,"name":"AX_ZYX_CUSTOMER","numberOfPlayers":4532},{"id":11,"name":"PST Update","numberOfPlayers":604},{"id":2,"name":"Customer Players","numberOfPlayers":5711},{"id":41,"name":"AX_AGRP_EXTRA_PRICING_PLAN_BAC_AXP_GROUP","description":"AX auto-group Extra Pricing Plan BAC Group","numberOfPlayers":2215},{"id":39,"name":"AX_AGRP_MY_REWARDS_NATIONAL_GROUP","description":"AX auto-group My Rewards (National) BAC List","numberOfPlayers":2130}]",CH,"ChProduct,ALL,SHOP-SPECIFIC",CHMA2112603MBHCL,"{"oemCode":"222923"}",Service,"Service Advisor Area & Service Waiting Area",false,false,false,,false,"{"OPT_IN_FT":false,"OPT_IN_PSA":false,"OPT_IN_SP":false,"OPT_IN_TS":false,"OPT_IN_VS":false,"OPT_IN_WA":false,"OPT_OUT_ZYX":false,"OPT_OUT_SHOPCONTENT":false,"OPT_OUT_FA":false,"OPT_OUT_GT":false,"OPT_IN_TT":false}",2X2,false,false,"2019-02-18 20:57:53","2019-02-20 19:16:08"
基本上，在将此类数据(在[]内)作为单个值获取时遇到了麻烦.该文件还具有其他类型的数据，例如嵌套引号，引号内的逗号等.
Basically, having trouble in getting this type of data ( inside [] ) as a single value. The file also has other type of data like nested quotes, commas inside quotes etc.
"[{"id":46,"name":"AX_AGRP_SIRIUSXM_BAC_AXP_GROUP","description":"Shops that can play the AX created\/provided Sirius XM creative.","numberOfPlayers":4494},{"id":33,"name":"AX_ZYX_CUSTOMER","numberOfPlayers":4532},{"id":11,"name":"PST Update","numberOfPlayers":604},{"id":2,"name":"Customer Players","numberOfPlayers":5711},{"id":41,"name":"AX_AGRP_EXTRA_PRICING_PLAN_BAC_AXP_GROUP","description":"AX auto-group Extra Pricing Plan BAC Group","numberOfPlayers":2215},{"id":39,"name":"AX_AGRP_MY_REWARDS_NATIONAL_GROUP","description":"AX auto-group My Rewards (National) BAC List","numberOfPlayers":2130}]"
感谢任何有关进行操作的帮助/建议.
Any help / suggestion on how to proceed is appreciated.
谢谢
推荐答案
如果输入数据是干净的，则可以轻松解决.但是，您的JSON字符串未正确转义，例如"{"OPT_IN_FT":false,"OPT_IN_PSA":"PSA337...使用双引号指示CSV上下文以及JSON上下文中的字符串.
If your input data would be clean, this could be simply solved easily. However, your JSON strings are not properly escaped, e.g. "{"OPT_IN_FT":false,"OPT_IN_PSA":"PSA337... is using a double quote to indicate a string in the CSV context as well as in the JSON context.
我的建议是在项目的JSON上下文中使用单引号，并在JSON字符串项目中使用转义引号.
My suggestion is to use single quotes in the JSON context for your items and escape quotes in JSON string items.
然后，您可以轻松地采用 this 之类的解决方案，或者使用正则表达式或自定义一个书面的解析器代码.
Then you could easily adapt solutions like this or that one using regex or custom written parser code.
如果要将CSV上下文中的字符串定界符更改为单引号，可以尝试这些 行:
If you want to change the string delimiter in the CSV context to a single quote you could try something along these lines:
import java.util.regex.Matcher;
import java.util.regex.Pattern;
class Test
{
public static void main (String[] args) throws java.lang.Exception
{
String input = "...";
Pattern re1= Pattern.compile("(?<=^|,)(?:\"(\\[.*?\\])\"|\"(\\{.*?\\})\"|\"([^\"\\{\\}\\[\\]]*)\")(?=,|$)", Pattern.DOTALL);
Matcher matcher = re1.matcher(input);
String singleQuoted = matcher.replaceAll("'$1$2$3'");
Pattern re = Pattern.compile(",(?=(?:[^']*'[^']*')*[^']*$)");
String[] parts = re.split(singleQuoted);
for(int partsIdx = 0; partsIdx < parts.length; partsIdx++ ){
System.out.println( "[" + partsIdx + "] = " + parts[partsIdx]);
}
}
}
显然有更优雅的方法可以做到这一点.
There are obviously more elegant ways to do this.
这篇关于RegEx，用于解析包含"JSON"格式的JSON的CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
