Append text question:

[Rustywinger]

This might seem obvious to the more Excel saavy crowd...

 

The question: I delete rows of data in an excel CVS, and then use a macro to append text to it, however the appended text will skip the deleted rows in Excel, leaving blank rows for however many rows you have deleted.

 

<-This is me with a problem.

 

 

The background, which is skippable if you know the answer to the above and have blessed me with it.... ( <- this would be me with the answer)

 

 

I have a Macro that uses an Excel CSV source file run through a 'Process Text file' loop, it will grab data and use it to process a record in an application and then append the line to a destination CSV file.

 

This process can be interrupted by errors or other undetectable events and the files are several thousand lines long. I restart the macro by running the destination file through a Process Text File loop in order to determine how many rows have been completed before restarting the main process with the source CSV file. All the above is all contained in one Macro which is simply fed a file name and then detects or creates the destination files it needs.

[Cory]

You never asked your question.

[Rustywinger]

You never asked your question.

 

I guess I don't know how to ask it... the macro is not appending to the first blank row in Excel if that row had previously been deleted.

 

How do I approach this? Is this some Excel file quirk?

[Cory]

Appending the row or adding a row? I'm afraid I don't understand. And are you doing this in Excel or with ASCII File Process.

 

Let me offer some advice. If you are working with a CSV do not use Excel. Instead use the ASCII File Process command. You can do everything you want in that loop and simply build your own output file. I like tabs as delimiters instead of commas as tabs almost never appear in text fields so I don't have to bother with quotes. As you go thru your source file do your thing then write it all out line by line. For some it's hard to conceptualize but I swear that once you have tried it you will slap yourself in the forehead. I wrote this web page a long time ago about it.

[Rustywinger]

Cory, sorry, the issue I am having is complex to me and hard to articulate and because of that, my post will get too long, and probably too much of an chore for samaritans to tackle.

 

I just read your post and your linked web page, it seems very similar to what I am doing, except I am using comma delimited output. I read from a source excel file using "Process Text File", which grabs a record and then 'does its thing' and finally writes a modified line to a destination .csv source file using Append variable to text file command. So when I refer to Excel files above, they are not actually open when they are being processed.

 

The Source CSV file can be in excess of 7,500 records and after each record is read into an ME variable, its values are isolated and used to perform automatic searches in another application to retrieve data from a remote database, as well as download associated files as identified from the contents of the variable.

 

There are maybe 250 lines of instruction between the main Text file begin process and Text file End Process. This is a very long affair which runs for hours and things can go wrong so I have written validation into the beginning of this macro before the main process loop. This allows me to restart the whole process by analyzing the destination file to find out what row to restart loading the source file in the Process Text File loop.

 

The PROBLEM itself is when in the down time when the process has stopped, I manually analyze the CSV file and find that, for example, the last 10 rows were errors and just delete them while open in Excel. I then close the file and run the macro again, and the validation correctly identifies which row to start (first blank row) reading in the source file... BUT, at the end of the loop, when the modified record is appended back to the destination text file, it doesn't write to one of the 10 blank lines where I just deleted errors, and instead begins on the 11th line. This really messes up the validation when I have to restart it again later.

 

I am thinking this is some Excel quirk... it is saving some kind of data in the deleted rows that shouldn't be there. I'm trying to see if anyone can confirm this issue from previous experience? Maybe I should use some other viewing program to do my manual edits, but a 7,500 line file can be hard to handle.

 

Thanks! (I'd post the validation code here, but I'm at home)

[kevin]

Here is a shot in the dark. When you delete the empty rows, do you also delete the end of line character? In other words, if you go to the end of the file after deleting where is your cursor? Is it at the end of the last or on the next line down:

 

A line

Another line

Last line|

 

or

 

A line

Another line

Last line

|

 

 

(where | represents the location of cursor)

 

If you see the latter, try pressing the Backspace key to create the former to see if it helps. I'm not sure if it will work but that is what I would try.

[Cory]

Let me try to re-iterate this to see if you think I have this right.

 

Your macro goes haywire and writes garbage to the last 10 lines of the output file. EG your last good data record was 90 and you have a total of 100. You open the output file in Excel and delete the last 10 lines and save. Now when you run the macro again and it creates new records starting at 101 instead of 91. The reason being that there are a whole bunch of extra CR-LF (0D0A in hex) at the end of the file.

 

Your problem is with Excel. Often when dealing with delimited data files it will do this for both rows or even sometimes columns. Your problem is that even though they're blank Excel thinks there's something of substance there. In my experience the problem is that the user chooses to 'delete' instead of 'delete row'. The prior only deletes the cell contents where the later removes the actual row. I highlight he entire row using the header at the left. Then I right click the header and choose 'delete row'. See if that works for you.

 

To 'see' this problem open the file with Notepad or better yet a proper hex editor like UltraEdit. In Notepad you will notice extra line breaks if you can arrow down past your data. In a hex editor you can turn the hex view on and actually see them. Check out the article on hex editors on my website.

 

You know it would be pretty easy to detect and fix those extra rows with a macro too. Maybe even in your main macro.

[Rustywinger]

Thanks, Kevin and Cory...

 

Haywire... strong words! ;-)

 

The actual thing that was showing up in there was the commas, duh. As Cory suggested I was selecting the row headers and then hitting the 'delete' button.

 

This is funny because in the validation I wrote to detect the last row, I actually had it set to ignore any commas that might be present in the record being processed. Again, Duh.

 

Well, I will get back to work on this now!

[Cory]

The actual thing that was showing up in there was the commas

I believe if you look with a hex editor you will see that there are carriage returns and line feeds too. This is what signifies a new record in a CSV and this is what is actually significant here.