Manipulate an XLS Excel file date from a batch file

the date of the FILE itself (i.e. date created, accessed, modified) or the date within a cell?

A little bit more explanation would be welcome indeed.

Thanks to both of you for replying so promptly.

I am talkinng here of the content of an Excel File.

Here is a sample batch file of what I would need to do.

********************************************
@ECHO OFF

setlocal ENABLEDELAYEDEXPANSION

FOR /L %%A in (1,1,100) do (
  SET Row=%%A
  IF CELL A!Row! == "DataToBeRemoved" DELETE ROW !Row!
)

Exit
********************************************

Thanks

An excel file can either be a delimiter sepaated file (usually tab or comma) or a true excel file. In the latter I fear you have to be carefull not destroying its content because the process might destoy special characters to the excel file.

When the first is true, why not use FOR /F in stead? It opens the specified file(s) and walks through every line of the file. Besides it gives you the option of defining delimiters, an end of line character, the number of tokens to be returned and the amount of lines to skip from processing. Deleteing lines can't be done though. You have to output every line you want to a new file (sjkipping the ones you don't want), delete the old file and rename the new file to the old file.

If this doesn't help, I have a Windows version of awk available (including documentation) . This allows you to search a file for patterns and define actions based on it. I'll attach it to this mail. Have a look at it. I wouldn't be surprised if it does the trick for you. The awk.exe works upto win 7 64 bits.

Hmmm, somehow the attachment is not attached. I'll try it again, hope it works now...
 
Nope, it wont work. Doesn't seem to get uploaded. Strange. Have to figure out what's going on.

Now it works. Used the wrong file format (7z instead of zip).
awk.zip

Thanks CvD,

The tool you provided to me seem's very cool for manipulating text files.  I will certainly use this in another project.

My challange here is to do modify the content of an XLS file, not CSV.

Here is a modified version of my "wanabe" batch file:

********************************************
@ECHO OFF

setlocal ENABLEDELAYEDEXPANSION

FOR /L %%A in (1,1,100) do (
  SET Row=%%A
  IF TheFile.xls(CELL A!Row!) == "DataToBeRemoved" DELETE TheFile.xls(ROW !Row!)
)

Exit
********************************************

Have any ideas?

Thanks!

Not right now I have. But I'll give it a thought. Does it have to be a batch file by the way?  What if I were to find a solution using other means?

Thanks CvD,

I tried it but it did not work. However, I did not find anywhere "DataToBeRemoved" so the scritp can find "DataToBeRemoved" so it can delete the row.

Have any idea?

Oh, ok, I see. I've forgot to mention a parameter named search. It should contain the value you are serching for. Btw, this script only searches the first cell of a row as that is what I understood you wanted.

Try again with the supplied test sheet and parameter search=DataToBeRemoved. It should work. I've tried it on office 2007 files and on offce 97-2003 files.

Sorry CvD for taking so long to reply to you. Got an operation and was brainly challanged by the pain killers.

Could you please tell me where I put "search=DataToBeRemoved"?

Is it going to be in the VBS Script bor the batch file?

Would it be ok if I ask you to append "search=DataToBeRemoved" in the script so I can run it as is?

Thanks again,
Rene

Fantastic, absolute success...

Thanks CvD for your hard work!

Cheers,
Rene

CvD,

FYI, I splitted points between 2 of your answers

Thanks again and cheers,
Rene

You're welcome