在Excel中格式化csv文件 [英] Formatting csv files in Excel

问题描述

Win XP，Excel 2007

Win XP, Excel 2007

我知道有其他各种各样的csv格式的文章，但不能找到我需要的。

I know there are various other posts on csv formatting but couldn't quite find what i needed.

我们的一些数据是由另一家公司在网站外举行的，他们每天早上向我们发送一个csv文件和前几天的数据。

Some of our data is held off site by another company and they send us a csv file every morning with the previous days data.

问题是此数据来自可能有下拉列表的Web输入表单。

The problem is this data has come from web input forms that may have drop-down lists.

例如，可能有员工人数选项，例如1-10,11-25,26-50等。

For example there may be a drop down list of Number of Employees with options like 1-10, 11-25, 26-50 etc

当我们在Excel中打开csv文件时， 已变成我们不想要的 Oct-01 日期格式。

When we open the csv file in Excel certain options like 1-10 has been turned into Oct-01 date format which we do not want.

是否有简单的方法来更改或重新格式化单元格，并做一个查找...替换？ （这似乎没有工作非常好，因为它保持回到日期）

Is there an easy way to change these back OR reformat the cells and do a find...replace? (This didn't seem to work terribly well as it kept reverting back to the date)

确实有一个更好的方法打开csv文件，以保持格式化完好？

Indeed is there a better way of opening the csv file to keep the formatting intact? and save us doing lots of find...replaces.

最终我们需要在Excel中打开csv。

Ultimately we will need to open the csv in Excel though.

感谢任何提示

推荐答案

这不是很讨厌吗？以下是我处理此问题的方式：

Isn't that SO annoying? Here's how I deal with this issue:

在Excel中打开CSV文件时，应该会出现一个包含解析选项的对话框。首先你选择分隔或固定，然后你得到一个预览数据解析的屏幕。

When you open the CSV file in Excel, you should get a dialog with parsing options. First you select delimited or fixed then you get a screen that previews the data parsing.

这很容易错过，但在对话框的右上角有一个选项，可以为每一列设置特定的数据格式。选择要保护的列，并将格式设置为文本。 （这将使Excel不会丢失新英格兰邮政编码的前导零）

It's easy to miss, but in the upper right corner of the dialog box there's an option to set a specific data format for each column. Select the column you want to protect and set the format to text. (This keeps Excel from dropping the leading zeros in ZIP codes for New England too!)

一旦你进入Excel，你可以做一个vlookup或替换来重置值到您自己的代码。

Once you get it into Excel, you can do a vlookup or replace to reset the values to your own codes.

希望这有助于。祝你好运。

Hope this helps. Good luck.

这篇关于在Excel中格式化csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！