excel - CSV 中数据提取末尾的尾随逗号

Question

关于我的问题的一点背景:

我有由 MS Access 中的查询创建的 CSV 文件。 CSV 文件的主体包含列；姓名、地址、邮政编码、日期等。文件的最后一行包含数据摘要；文件名、日期、时间戳和记录总数。由于所有字段都包含字符串，并且我们不希望系统截断或尝试评估日期和邮政编码，因此我们用引号将这些字段输出。

最后两行看起来像这样:

"Name","Code","Address","Address2","Office","Location","City","State","PostCode","Date"
"FileName","DDMMYYYY","HH:MM:SS",TotalRecords,"","","","","",""

我需要删除最后一行中多余的逗号，所以它看起来像这样:

"FileName","DDMMYYYY","HH:MM:SS",TotalRecords

我已经浏览并尝试了这里的解决方案:
CSV file has extra commas

这会创建我原始文件的副本，但会保留所有逗号。如果我删除所有引号，则此解决方案有效。

Answer 1

如果您在 Excel 中，您可以在 VBA 中执行这一行:

left(Activecell.text,len(trim(replace(replace(activecell.Text,","," "),""""," "))))

我将从中间解释:

replace(replace(activecell.Text,","," "),""""," ")

用空格替换所有逗号和双引号

trim(replace(replace(activecell.Text,","," "),""""," "))

修剪它，即删除起始和尾随空格

len(trim(replace(replace(activecell.Text,","," "),""""," ")))

获取修剪后文本的长度

left(Activecell.text,len(trim(replace(replace(activecell.Text,","," "),""""," "))))

最后给我原始字符串的左侧部分，其中字符数设置为我们计算出的长度减去开头和结尾的逗号和双引号。