将xls文件转换为csv，但是添加了额外的行吗？ [英] Convert xls File to csv, but extra rows added?

问题描述

因此，我正在尝试将一些xls文件转换为csv，并且除一部分以外，其他一切都很好。 Excel互操作中的SaveAs函数似乎会导出所有行（包括空白行）。使用记事本查看文件时，可以看到这些行。 （我期望的所有行，用两个单引号引起的15行，然后其余都是空白）。然后，我有一个存储过程，它将这个csv导入到所需的表中（这适用于已手动转换为csv的电子表格（例如，打开，文件->另存为等）。

So, I am trying to convert some xls files to a csv, and everything works great, except for one part. The SaveAs function in the Excel interop seems to export all of the rows (including blank ones). I can see these rows when I look at the file using Notepad. (All of the rows I expect, 15 rows with two single quotes, then the rest are just blank). I then have a stored procedure that takes this csv and imports to the desired table (this works on spreadsheets that have been manually converted to csv (e.g. open, File--> Saves As, etc.)

这是我在代码中用于SavesAs的代码行，我尝试将xlCSV，xlCSVWindows和xlCSVDOS用作文件格式，但是它们都做同样的事情。

Here is the line of code I am using for my SavesAs in my code. I have tried xlCSV, xlCSVWindows, and xlCSVDOS as my file format, but they all do the same thing.

wb.SaveAs(aFiles(i).Replace(".xls", "B.csv"), Excel.XlFileFormat.xlCSVMSDOS, , , , False) 'saves a copy of the spreadsheet as a csv

因此，是否还有其他步骤/设置我需要做的是不要让Extraneuos行显示在csv中？

So, is there some additional step/setting I need to do to not get the extraneuos rows to show up in the csv?

请注意，如果我打开此新创建的csv，然后单击另存为，然后选择csv，我的过程会再次喜欢它。

Note that if I open this newly created csv, and then click Save As, and choose csv, my procedure likes it again.

推荐答案

从工作簿创建CSV时，会根据您的UsedRange。由于只需使用格式化应用程序即可扩展UsedRange说谎到一个单元格（没有任何内容），这就是为什么您得到空白行的原因。 （由于此问题，您也可以获取空白列。）

When you create a CSV from a Workbook, the CSV is generated based upon your UsedRange. Since the UsedRange can be expanded simply by having formatting applied to a cell (without any contents) this is why you are getting blank rows. (You can also get blank columns due to this issue.)

当您打开生成的CSV时，所有这些不包含内容的单元格由于具有没有内容或格式（因为只有值保存在CSV中）。

When you open the generated CSV all of those no-content cells no longer contribute to the UsedRange due to having no content or formatting (since only values are saved in CSVs).

您可以通过在保存之前更新使用范围来解决此问题。这是我在VBA中写的一个简短的文章，可以解决这个问题。这段代码会让您丢失所有格式，但是我认为这并不重要，因为无论如何您都保存为CSV。

You can correct this issue by updating your used range before the save. Here's a brief sub I wrote in VBA that would do the trick. This code would make you lose all formatting, but I figured that wasn't important since you're saving to a CSV anyway. I'll leave the conversion to VB.Net up to you.

Sub CorrectUsedRange()
    Dim values
    Dim usedRangeAddress As String
    Dim r As Range
    'Get UsedRange Address prior to deleting Range
    usedRangeAddress = ActiveSheet.UsedRange.Address
    'Store values of cells to array.
    values = ActiveSheet.UsedRange
    'Delete all cells in the sheet
    ActiveSheet.Cells.Delete
    'Restore values to their initial locations
    Range(usedRangeAddress) = values
End Sub

这篇关于将xls文件转换为csv，但是添加了额外的行吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！