如何解析复杂的CSV文件

Question

我收到了一个包含字符串和元组元素组合的CSV文件，但找不到正确解析它的方法。我错过了什么明显的东西吗？

csvfile

"presentation_id","presentation_name","sectionId","sectionNumber","courseId","courseIdentifier","courseName","activity_id","activity_prompt","activity_content","solution","event_timestamp","answer_id","answer","isCorrect","userid","firstname","lastname","email","role"
"26cc7957-5a6b-4bde-a996-dd823f54ece7","3-Axial Skeleton F18","937c47b0-cc66-4938-81de-1b1b58388499","001","3b5b5e49-1798-4eab-86d7-186cf59149b4","MOVESCI 230","Human Musculoskeletal Anatomy","62d059e8-9ab4-41d4-9eb8-00ba67d9fac9","A blow to which side of the knee might tear the medial collateral ligament?","{"choices":["medial","lateral"],"type":"MultipleChoice"}","{"solution":[1],"selectAll":false,"type":"MultipleChoice"}","2018-09-30 23:54:16.000","7b5048e5-7460-49f8-a64a-763b7f62d771","{"solution":[1],"type":"MultipleChoice"}","1","57ba970d-d02b-4a10-a64d-56f02336ee08","Student","One","[e-mail]","Student"
"26cc7957-5a6b-4bde-a996-dd823f54ece7","3-Axial Skeleton F18","937c47b0-cc66-4938-81de-1b1b58388499","001","3b5b5e49-1798-4eab-86d7-186cf59149b4","MOVESCI 230","Human Musculoskeletal Anatomy","f82cb32b-45ce-4d3a-aa74-b3fa1a1038a2","What is the name of this movement?","{"choices":["right rotation","left rotation","right lateral rotation","left lateral rotation"],"type":"MultipleChoice"}","{"solution":[1],"selectAll":false,"type":"MultipleChoice"}","2018-09-30 23:20:33.000","d6cce4d9-37ae-409e-afc5-54ad79f86226","{"solution":[3],"type":"MultipleChoice"}","0","921d1b9b-f550-4289-89f1-2a805b27eeb3","Student","Two","[e-mail]","Student"

第一行是标题，第二行开始数据

with open(filepathcsv) as csvfile:
    readCSV = csv.reader(csvfile)
    for row in readCSV:
        numcolumns = len(row)
        print(numcolumns,": ",row)

产量：

20 :  ['presentation_id', 'presentation_name', 'sectionId', 'sectionNumber', 'courseId', 'courseIdentifier', 'courseName', 'activity_id', 'activity_prompt', 'activity_content', 'solution', 'event_timestamp', 'answer_id', 'answer', 'isCorrect', 'userid', 'firstname', 'lastname', 'email', 'role']
25 :  ['26cc7957-5a6b-4bde-a996-dd823f54ece7', '3-Axial Skeleton F18', '937c47b0-cc66-4938-81de-1b1b58388499', '001', '3b5b5e49-1798-4eab-86d7-186cf59149b4', 'MOVESCI 230', 'Human Musculoskeletal Anatomy', '62d059e8-9ab4-41d4-9eb8-00ba67d9fac9', 'A blow to which side of the knee might tear the medial collateral ligament?', '{choices":["medial"', 'lateral]', 'type:"MultipleChoice"}"', '{solution":[1]', 'selectAll:false', 'type:"MultipleChoice"}"', '2018-09-30 23:54:16.000', '7b5048e5-7460-49f8-a64a-763b7f62d771', '{solution":[1]', 'type:"MultipleChoice"}"', '1', '57ba970d-d02b-4a10-a64d-56f02336ee08', 'William', 'Muter', '[e-mail]', 'Student']
27 :  ['26cc7957-5a6b-4bde-a996-dd823f54ece7', '3-Axial Skeleton F18', '937c47b0-cc66-4938-81de-1b1b58388499', '001', '3b5b5e49-1798-4eab-86d7-186cf59149b4', 'MOVESCI 230', 'Human Musculoskeletal Anatomy', 'f82cb32b-45ce-4d3a-aa74-b3fa1a1038a2', 'What is the name of this movement?', '{choices":["right rotation"', 'left rotation', 'right lateral rotation', 'left lateral rotation]', 'type:"MultipleChoice"}"', '{solution":[1]', 'selectAll:false', 'type:"MultipleChoice"}"', '2018-09-30 23:20:33.000', 'd6cce4d9-37ae-409e-afc5-54ad79f86226', '{solution":[3]', 'type:"MultipleChoice"}"', '0', '921d1b9b-f550-4289-89f1-2a805b27eeb3', 'Noah', 'Willett', '[e-mail]', 'Student']

csv.reader解析每行不同，因为结构复杂，嵌入了花括号元素。

...但是我希望每行有20个元素。

Answer 1

谢谢大家的建议！

另外，我很抱歉，因为我没有包括我试图解析的原始CSV文件（例如：）

“b5ae18d3-b6dd-4d0a-84fe-7c43df472571”|“气候快速变化”| W18.pdf”|“18563b1e-a467-44b3-aed7-3607a1acd712”|“001”|“c86c8c8d-dca6-41cd-a010-a83e40d93e75”|“气候102”|“极端天气”|“278c4561-c834-43-a770-3F563F563”与欧洲城市在同一纬度的阿伯安”ク|“{”选项“[“斯德哥尔摩，瑞典”，“柏林，德国”，“伦敦，英国”，“巴黎，法国”，“马德里，西班牙]，”类型“：“多重回声”}“{”解决方案“[4]，”选择全部“：”假“，”类型“：“多重回声”}”|“2019-01-31 22:11:08.000”|“81392cd3-28e9-4e2e-8a33-018104b1f4d1”|“{”解决方案“[3,4]，”类型“：“多重回声”|“0”|“2DB100C-12407-12441;-B5411”|[e-mail]“|”学生“ee73fdaf-a926-4899-b0f7-9b942f1b44ad”|“6-肘、腕、手W19”|“48539109-529e-4359-83b9-2ae81be0532c”|“001”|“3B5E49-1798-4eab-86d7-186cf59149b4”|“MOVESCI 230”|“人体肌肉骨骼解剖学”|“fcd7c673-d944-48c3-8a09-F45834”|“这个运动叫什么名字？”？（3）选择“，”类型：“（类型：“（解决方案：[3]解决解决方案[3]，“类型：“（解决解决解决解决解决解决解决解决解决解决解决解决解决解决解决解决解决解决解决解决[3]，，”选择所有：假，“类型：：假，”类型：“假，”类型：“（假，”类型：“（假，”类型：“（假，”类型：“（假，”类型：“（多回声）假，”类型：“（多回声）假，”类型：“（多回声）假，”类型，”类型：“（多回声）类型：“（多回声）假，”类型，”类型：“（多回声）假，”类型，”类型：“（多回声）类型：“（多回声）类型：“（多回声）类型：：“（多回声）类型：：：，类型：“（类型：“（多回声）类型）类型：“（多回声）类型）类型：：：：：：：））类型，”类型：“（类型：“（多回声）类型：：：：：：：：：：））类型，”类型，”类型：：：“（多回声）类型：：：：“两个"|"[e-mail]“|”学生”

你是对的，问题是CSV文件的形式。

1. 我更改了readCSV=csv。读卡器（csvfile）到readCSV=csv。读卡器（csvfile，分隔符=“|”，引号=“|”）
2. 然后，我获取结果列表，并从每个元素中删除多余的引号

html" target="_blank">程序的其余部分现在正常工作。

Answer 2

记录中的，而不是代码。您的代码工作正常。为了解决这个问题，您需要修复csv文件，因为json内容的字段没有正确序列化。

只需将一个引号符号“更改为两个引号符号”，即可将其转义。

下面是固定csv行的示例。

"26cc7957-5a6b-4bde-a996-dd823f54ece7","3-Axial Skeleton F18","937c47b0-cc66-4938-81de-1b1b58388499","001","3b5b5e49-1798-4eab-86d7-186cf59149b4","MOVESCI 230","Human Musculoskeletal Anatomy","f82cb32b-45ce-4d3a-aa74-b3fa1a1038a2","What is the name of this movement?","{""choices"":[""right rotation"",""left rotation"",""right lateral rotation"",""left lateral rotation""],""type"":""MultipleChoice""}","{""solution"":[1],""selectAll"":false,""type"":""MultipleChoice""}","2018-09-30 23:20:33.000","d6cce4d9-37ae-409e-afc5-54ad79f86226","{""solution"":[3],""type"":""MultipleChoice""}","0","921d1b9b-f550-4289-89f1-2a805b27eeb3","Student","Two","[e-mail]","Student"

以及修复后代码的结果：

20 :  ['26cc7957-5a6b-4bde-a996-dd823f54ece7', '3-Axial Skeleton F18', '937c47b0-cc66-4938-81de-1b1b58388499', '001', '3b5b5e49-1798-4eab-86d7-186cf59149b4', 'MOVESCI 230', 'Human Musculoskeletal Anatomy', 'f82cb32b-45ce-4d3a-aa74-b3fa1a1038a2', 'What is the name of this movement?', '{"choices":["right rotation","left rotation","right lateral rotation","left lateral rotation"],"type":"MultipleChoice"}', '{"solution":[1],"selectAll":false,"type":"MultipleChoice"}', '2018-09-30 23:20:33.000', 'd6cce4d9-37ae-409e-afc5-54ad79f86226', '{"solution":[3],"type":"MultipleChoice"}', '0', '921d1b9b-f550-4289-89f1-2a805b27eeb3', 'Student', 'Two', '[e-mail]', 'Student']