We help IT Professionals succeed at work.

Manipulate an XLS Excel file date from a batch file

I've been looking for months on ways to manipulate Excel files data using a batch file.

I've been asking all around and it was a no go.

Now, I'm asking this to my Expert Exchange fellow geniuses.

Will you accept this challenge?   ;-)

Thanks for helping me.
Comment
Watch Question

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

Commented:
A little bit more explanation would be welcome indeed.

Author

Commented:
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
CvD

Commented:
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.

CvD

Commented:
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.
CvD

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

Author

Commented:
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!
CvD

Commented:
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?
Commented:
I might have come up with a solution, albeit not directly from batch.
I've created a vbs script that does the trick. But you can call it using cscript from a batch file.

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

setlocal ENABLEDELAYEDEXPANSION
cscript //nologo DelExcelRows.vba xls=c:\my\long\path\to\my\excelfile.xls sheet=1 skip=10
Exit
********************************************

The vbs file wil open the xls file passed to it via the xls parameter. The sheet parmeter is to select the correct sheet from a multiple sheet workbook. The skip parameter is used to advance the number of row according to the passed value.

There is only one drawback to this solution. If for whatever reason the procedure terminates unexpecetly, the excel proces will keep running in the background. Ju have to kill that proces using the taskmanager.

Have fun trying the solution.
DelExcelRows.zip

Author

Commented:
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?
CvD

Commented:
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.

Author

Commented:
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
CvD
Commented:
Hi Rene,

Hope your operation was a succes and you're mind is back on full speed now.
You should modify the batch file like shown below.

@ECHO OFF
cscript //nologo DelExcelRows.vba xls=c:\my\long\path\to\my\excelfile.xls sheet=1 skip=10 search=DataToBeRemoved
Exit

Success

Author

Commented:
Fantastic, absolute success...

Thanks CvD for your hard work!

Cheers,
Rene

Author

Commented:
CvD,

FYI, I splitted points between 2 of your answers

Thanks again and cheers,
Rene
CvD

Commented:
You're welcome