How to Append CSV file without reading empty formatted cells

[excelvbalearner]

I have a code that is able to Append a range of data in a worksheet to an existing CSV file. However, I noticed that when the existing CSV file contains empty, but formatted cells beyond the end of the data range, the Append function takes into account those empty cells as well.

For example, the existing CSV file has 10 rows of data, while rows 11 to 20 are empty, but has been formatted (e.g. as "dd-mmm-yy"). So when I append the CSV file, the new data is added at row 21 instead of row 11.

Apart from manually deleting rows 11 to 20 in the CSV file, is there a quick fix to this? I have many existing CSV files, so it is not feasible to do the manually way. As to why some of the rows are empty, but formatted, it is due to some earlier amendments that took place.

Appreciate the help. Thanks.

 

[Roger Govier]

I have a code that is able to Append a range of data in a worksheet to an existing CSV file. However, I noticed that when the existing CSV file contains empty, but formatted cells beyond the end of the data range, the Append function takes into account those empty cells as well.

For example, the existing CSV file has 10 rows of data, while rows 11 to 20 are empty, but has been formatted (e.g. as "dd-mmm-yy"). So when I append the CSV file, the new data is added at row 21 instead of row 11.

Apart from manually deleting rows 11 to 20 in the CSV file, is there a quick fix to this? I have many existing CSV files, so it is not feasible to do the manually way. As to why some of the rows are empty, but formatted, it is due to some earlier amendments that took place.

Appreciate the help. Thanks.

I think I would amend my VBA code to read in the existing csv data first, find the last used row, append my new data and then save the result back as the original csv file.

 

[excelvbalearner]

Hi Roger,

Sorry if I am just reiterating what you said, but just want to clarify the process. So basically:

1. Read the CSV file using Input As #1
2. Find the last row using Do Until EOF(1)
3. Take the new data from the worksheet and append it to the CSV data from Step 1
4. Write (or overwrite) the new extended data to the original CSV file using Output As #1 ?

I haven't thought what the exact code will look like, but just want to clarify with you if my process is what you are suggesting. Thanks.

 

[Roger Govier]

Hi

If you read just open the csv file in Excel, it will start in A1 and will extend to the number of rows and columns as the data it contains.
With VBA, you can then determine the last row with data in column A on that sheet, value X

x = Cells(Rows.Count, 1).End(xlUp).Row

Copy your new data and paste it to cell AX+1
You now have all of the data without any blank rows.
Save that as a csv file as original filename.csv, overwriting the original.

 

[excelvbalearner]

I see... by opening in Excel, will that be meaningfully slower than using the Append As functionality? The reason I am concerned is because I have a lot of CSV files to update/append.

 

[Roger Govier]

I see... by opening in Excel, will that be meaningfully slower than using the Append As functionality? The reason I am concerned is because I have a lot of CSV files to update/append.

I wouldn't have thought so.
Just give it a try and see.

 

[excelvbalearner]

Hi Roger,

I tried your method. The run time is unfortunately around 6 times slower even after turning off the screen updating and display alerts during the run.