vba CSV 日期格式

CSV date format

提问by Shoban

I have a VB application which extracts data and creates 3 CSV files (a.csv, b.csv, c.csv). Then I use another Excel spreadsheet (import.xls) to import all the data from the above CSV files into this sheet.

我有一个 VB 应用程序，它提取数据并创建 3 个 CSV 文件（a.csv、b.csv、c.csv）。然后我使用另一个 Excel 电子表格 (import.xls) 将上述 CSV 文件中的所有数据导入到此工作表中。

import.xls file has a macro which opens the CSV files one by one and copies the data. The problem I am facing is the dates in the CSV files are stored as mm/dd/yyyy and this is copied as is to the Excel sheet. But I want the date in dd/mm/yyy format.

import.xls 文件有一个宏，可以一一打开 CSV 文件并复制数据。我面临的问题是 CSV 文件中的日期存储为 mm/dd/yyyy，并按原样复制到 Excel 工作表中。但我想要 dd/mm/yyy 格式的日期。

When I open any of the CSV files manually the dates are displayed in the correct format (mm/dd/yyyy). Any idea how I can solve this issue?

当我手动打开任何 CSV 文件时，日期以正确的格式 (mm/dd/yyyy) 显示。知道如何解决这个问题吗？

回答by Greg Hewgill

When I run into this problem I usually write out the dates as yyyy-mm-dd which Excel will interpret unambiguously.

当我遇到这个问题时，我通常将日期写成 yyyy-mm-dd，Excel 会明确解释这些日期。

回答by Mark

You can use the FormatVBA function:

您可以使用FormatVBA 函数：

Format(DateText, "dd/mm/yyyy")

That will format it how ever you like.

这将按照您的喜好对其进行格式化。

For a more permanant solution, try changing your regional settings in windows itself, Excel uses this for its date formatting.

要获得更持久的解决方案，请尝试在 Windows 本身中更改区域设置，Excel 将其用于日期格式。

Start -> Settings -> Control Panel -> Regional Options.

开始 -> 设置 -> 控制面板 -> 区域选项。

Make sure that the language is set to whatever is appropriate and that the date settings are as you want them to be

确保语言设置为适当的任何内容，并且日期设置如您所愿

回答by SpyJournal

First of all the other answers are all good but theres some more information you might find helpful

首先，其他答案都很好，但您可能会发现更多信息有帮助

A CSV file only contains text. That is the data is not in date format but in a text format. So when you open a CSV file in Excel, Excel by default interprets that data for you. It doesn't have to. You could force it to leave it as text, Or as mentioned by Mark you can add code into your import macro that alters it for you. If you want an automated process then this is the best solution. Simply add the code in VBA to the macro that applies the required date format to the column with the date data. Alternatively you could do this manually after the file is open and the data has been pasted by selecting the column yourself and chancing the format. You can customise number formats (choose custom) and then write it up yourself. Eg dd/mm/yyyy.

CSV 文件仅包含文本。也就是说，数据不是日期格式，而是文本格式。因此，当您在 Excel 中打开 CSV 文件时，Excel 默认会为您解释该数据。它没有必要。您可以强制将其保留为文本，或者如 Mark 所提到的，您可以将代码添加到您的导入宏中，为您更改它。如果您想要一个自动化流程，那么这是最好的解决方案。只需将 VBA 中的代码添加到宏，即可将所需的日期格式应用于具有日期数据的列。或者，您可以在打开文件并通过自己选择列并更改格式来粘贴数据后手动执行此操作。您可以自定义数字格式（选择自定义），然后自己编写。例如 dd/mm/yyyy。

回答by SpyJournal

I had a similar problem and solved it like this, essentially it is the 'all of the above' option

我遇到了类似的问题并像这样解决了它，本质上它是“以上所有”选项

1. With format(date,'dd-mmm-yyyy')convert the date to the 2-3-4 format e.g.01-aug-2008, no way a computer mistakes 01-aug for 08-jan :-)
2. Since copying this in excel automatically reverts it back to the date/time format, convert the result to a string by using cstron the function in 1
3. Format the cells in which the date is copied as text (if you don't, thickheaded as excel is, it will convert it back to date / time again
4. Save the sheet as a csv file and open in notepad to confirm it has worked

1. 随着format(date,'dd-mmm-yyyy')转换日期为2-3-4格式EG01-AUG-2008，没办法计算机失误01 - 8月08-JAN :-)
2. 由于在 excel 中复制它会自动将其恢复为日期/时间格式，因此通过cstr在 1 中的函数上使用将结果转换为字符串
3. 格式化将日期复制为文本的单元格（如果不这样做，像 excel 一样厚脸皮，它将再次将其转换回日期/时间
4. 将工作表另存为 csv 文件并在记事本中打开以确认它已工作

Some people may say it's overkill, but better be on the safe side, than having a user call me a year from now and telling me it's doing something strange with the date's

有些人可能会说这是矫枉过正，但最好是安全起见，而不是让用户在一年后打电话给我并告诉我它对日期做了一些奇怪的事情