Here's a brief sub I wrote in VBA that would do the trick. You can correct this issue by updating your used range before the save. 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). (You can also get blank columns due to this issue.) 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. When you create a CSV from a Workbook, the CSV is generated based upon your UsedRange. Note that if I open this newly created csv, and then click Save As, and choose csv, my procedure likes it again. So, is there some additional step/setting I need to do to not get the extraneuos rows to show up in the csv? wb.SaveAs(aFiles(i).Replace(".xls", "B.csv"),, ,, , False) 'saves a copy of the spreadsheet as a csv I have tried xlCSV, xlCSVWindows, and xlCSVDOS as my file format, but they all do the same thing. Here is the line of code I am using for my SavesAs in my code. 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. (All of the rows I expect, 15 rows with two single quotes, then the rest are just blank). I can see these rows when I look at the file using Notepad. The SaveAs function in the Excel interop seems to export all of the rows (including blank ones). Microsoft Office, LibreOffice, Kingsoft Office, Google Docs.So, I am trying to convert some xls files to a csv, and everything works great, except for one part. CSV implementations may not handle such field data or use quotation marks to surround the field. The basic idea of separating fields with a comma is clear, but that idea gets complicated when the field data may also contain commas or even embedded line-breaks. Since then, Microsoft has made the Excel binary format specification available to download freely. has created documentation of the Excel format. The comma as a field separator is the source of the name for this file format. Each record consists of one or more fields, separated by commas. In computing, a comma-separated values (CSV) file stores tabular data (numbers and text) in plain text.
Excel 2007 uses Office Open XML as its primary file format, an XML-based format that followed after a previous XML-based format called "XML Spreadsheet". Microsoft Excel, up until 2007 version used a proprietary binary file format called Excel Binary File Format (.XLS) as its primary format.
0 kommentar(er)
