How to eliminate trailing empty row/columns

Question

I am using Microsoft Interop to convert excel files into csv files. I use sheet.SaveAs function.

My initial excel sheet has data from A1 to AZ columns for 100 rows. I need in the CSV just the data from A1 to AP and only for 50 rows.

Using the Range function, I delete the row51-100, I clear the contents for the same rows, still when I save as CSV, I find rows 51-100 as below: (just commas). I do not want to see these commas in CSV.

,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,

The same for column AQ-AZ as well. I do not want these data in CSV. I delete, clear contents using Range function, yet these AQ-AZ columns appears in CSV files as “,,,,,,,,,,,,,,,,,,,,,” .

Is there a way to save XLS as CSV with only Range that I want to see in the CSV file. Is there a way to control the range that goes into CSV file?

In short, I want to see in CSV file just the data for column A1 to AP for 50 rows. No empty trailing “,”s. Is there a way?

Trici

Answer 1

The issue you are describing seems like a "Last Cell" issue. The last cell is the original end of your data, even after you delete rows/columns.

Here is what Microsoft has to say about it: How to reset the last cell in Excel

I seem to remember a programmatic way of doing this, but for the life of me, I cannot recall how.

Having looked at that info, maybe you could rethink how you can do this.

Perhaps you could just read the data you need and write it out yourself.

i.e. For each row in range, get the row as a value which will be an array of object, convert to array of string, string.join with the delimiter as a comma and append to a .csv file.

Disembogue

That worked for me. Just selected the empty columns and then under the Clear menu I selected All. – Lonne

@Lonne that did not work me, what did work was to copy the populated columns in a new worksheet and overwriting the old CSV file. – Inkerman

Answer 2

Clearing the contents as suggested in another answer did not work for me, what did work was copying the populated columns in a new worksheet and overwriting the old CSV.

Thrombin

Answer 3

Simply select the trailing empty columns in Excel, right click and select: clear contents. Then save.

Footie

Answer 4

I also had this issue in a complex excel files (multiple pages).

I could not remove the empty line by clicking "delete" on the entire line. The way I checked the result was exporting the page as "CSV+utf8", and opening the *.csv file as a text file under notepad++. I could see a trailing line with only commas (',').

However I entered a dummy word ("foobar" in my case), and then I click "delete" on the complete line, then at that point this worked.

Kellikellia