Excel：如何仅在CSV文件中向字符串添加双引号

I want to create a CSV file from Excel in which string values should be in double quotes and date values should be in MM/dd/yyyy format. All the numeric and Boolean values should be without quotes.

我想从Excel创建一个CSV文件,其中字符串值应该是双引号,日期值应该是MM / dd / yyyy格式。所有数值和布尔值都应该没有引号。

How would I go about this?

我怎么会这样呢?

3 个解决方案

#1

It's kind of scary that Excel doesn't let you specify formats. Here's a MrExcel link that might prove useful to you as well.

Excel不允许您指定格式,这有点可怕。这是一个可能对您有用的MrExcel链接。

http://www.mrexcel.com/forum/showthread.php?t=320531

Here's the code from that site:

这是该网站的代码:

Sub CSVFile()

Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")

If FName <> False Then
  ListSep = Application.International(xlListSeparator)
  If Selection.Cells.Count > 1 Then
    Set SrcRg = Selection
  Else
    Set SrcRg = ActiveSheet.UsedRange
  End If    
  Open FName For Output As #1    
  For Each CurrRow In SrcRg.Rows
    CurrTextStr = ""
    For Each CurrCell In CurrRow.Cells
      CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ListSep
    Next
    While Right(CurrTextStr, 1) = ListSep
      CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
    Wend    
    Print #1, CurrTextStr    
  Next    
  Close #1    
  End If
End Sub

#2

It's easier to use VBA to do this. The SaveAs method of the Workbook object only lets you choose pre-defined formats and the xlCSV one does not delimit strings with double-quotes.

使用VBA更容易实现此目的。 Workbook对象的SaveAs方法只允许您选择预定义的格式,而xlCSV不会使用双引号分隔字符串。

To do this in VBA:

要在VBA中执行此操作:

Dim fileOut As Integer

fileOut = FreeFile
Open "C:\foo.csv" For Output As #fileOut

Write #fileOut, 14, "Stack Overflow", Date, True

Close #fileOut

(NB Date is a VBA statement that returns the current system date as a Variant of sub-type Date)

(NB Date是一个VBA语句,它将当前系统日期作为子类型Date的变体返回)

If you then examine the file in Notepad:

如果您然后在记事本中检查该文件:

14,"Stack Overflow",#2009-05-12#,#TRUE#

The string has been delimited as required, the date converted to universal format and both the date and boolean are delimited with # signs.

字符串已根据需要分隔,日期转换为通用格式,日期和布尔值都用#符号分隔。

To read the data back in use the Input # statement which will interpret all of the values appropriately.

要使用Input#语句重新读取数据,它将适当地解释所有值。

If you want to write part of a line and then finish writing it later then:

如果你想写一部分行,然后在以后完成写:

Write #fileOut, 14, "Stack Overflow";
Write #fileOut, Date, True

produces the same result as the original program. The semi-colon at the end of the first statement prevents a new line being started

产生与原始程序相同的结果。第一个语句末尾的分号会阻止启动新行

Strings with embedded double-quotes will cause problems so you'll need to remove or replace those characters

嵌入双引号的字符串会导致问题,因此您需要删除或替换这些字符

#3

The default csv file format is to use commas between each value, quotes to contain each cell (In case your cell contains a comma) and line breaks for the next row e.g.

默认的csv文件格式是在每个值之间使用逗号,引号包含每个单元格(如果您的单元格包含逗号)和下一行的换行符,例如

"A1","B1"
"A2","B2"

This is only from me toying with open office, which may vary a little from excel. But try it and see.

这只是我玩弄开放式办公室,这可能与excel略有不同。但试试看,看看。

#1