Re: Searching for a solution with Batch programming

RivaCom@xxxxxxxxx schrieb:
> I was tasked with a job to write a batch program to download a excel
> file from a FTP and convert it to CSV, getting the file was a 2 second
> job. But finding a free program to convert xls 2 csv or txt was a
> nightmare. I finally found a standalone app, which i can run from
> command line. My problem being after it converts a dialog pops up
> saying "Conversion Complete", and I can't figure out how I would go
> about exiting the program. The last few lines of commands go:
>
> ftp -s:download.bat
> open ftphyperlink
> username
> password
> get test.xls
> bye
> xls2csv.exe test.xls
> exit
>
> Anyone have any suggestions or better solutions? This will be running
> on windows server 2003, with no office applications on it.

There are very different methods to solve your problem; all require some
programming work, some installed software, and all involve some risk of
failure.

(1) Minimal programming/VBScript installed/maximum risk of failure

A quick, dirty, brutal, and risky hack is to terminate your xls2csv.exe
by force (hoping that the .csv output will usable). You can use this VBScript
(saved as killer.vbs and called like "cscript killer.vbs" from a dos box):

Const WshRunning = 0
Const WshFinished = 1

Dim sCmd : sCmd = "notepad.exe"
' sCmd = "your command line: xls2csv.exe test.xls (or more elaborate)"
Dim oWSH : Set oWSH = CreateObject( "WScript.Shell" )
Dim oNP : Set oNP = oWSH.Exec( sCmd )

WScript.Stdout.Write "Press Enter to kill the process. "
WScript.Stdin.ReadLine

If WshFinished = oNP.Status Then
WScript.Stdout.WriteLine "Process has finished already."
Else
WScript.Stdout.WriteLine "Trying to terminate the process."
oNP.Terminate
End If

If cscript.exe isn't found or not enabled and you can't get your Admin
to install/enable it, skip to (2). If it runs, it should start Notepad.exe,
wait for your "Enter", and kill Notepad - even if you enter some text
and are asked for saving your work. Use the disabled sCmd = "..." line to
specify your commandline for xls2csv and test if the output .csv file
is usable if you kill the process after the "Conversion Complete" dialog
has poped up, but without dismissing it. If this test fails, skip to (2).
If it succeeds, the flaw of the killer.vbs has to be removed: It saves
you klicking Ok in the dialog by asking you to hit Enter in the dos box.

After editing sFSpec, you can use "tryopen.vbs" ("cscript tryopen.vbs")

Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
Dim sFSpec : sFSpec = "your .csv output file"
Dim isOpen : isOpen = False
Dim oTS
Do While Not isOpen
WScript.Echo "testing", sFSpec
On Error Resume Next
Set oTS = oFS.OpenTextFile( sFSpec, ForAppending )
isOpen = (0 = Err.Number)
On Error GoTo 0
WScript.Sleep 1000
Loop
WScript.Echo sFSpec, "is ready."
oTS.Close

to see whether xls2csv.exe closes the file *before* the dialog pops up.
If not, you can't use the brutal approach, so skip to (2). If closing
the output file can be detected and used as the point in time to zap the
program, merge those scripts to kill the process after the output file
is accessible (replace the manual waiting with the Not isOpen loop).
Your .bat file would then look like

...
get test.xls
bye
*cscript killer2.vbs*
exit

(2) A bit more of programming/VBScript or AutoIt installed/high risk of
failure

If the output file's status can't be used to determine when to kill xls2csv.exe
and/or the brutal termination of the program results in a bad result, you
can use VBScript to monitor the open windows and to send keystrokes (.SendKeys
method of the WScript.Shell object) to the program. This technique is a not
so quick and rather dirty hack; I never used/will use it, but YMMV.
As an alternative to VBScript/SendKeys you should consider AutoIt, because it's
designed for such use.

http://www.autoitscript.com/autoit3/

Again, I can't help you with this approach, because I have no experience with
AutoIt.

(3) More programming/.NET & ADO (ODBC/OLEDB?) installed/small risk of failure

This small proof of concept C# program:

using System;

using System.Data;
using System.Data.Common;
using System.Data.Odbc;

using System.IO;

namespace xls2csv
{
class Program
{
/// <summary>
/// POC: convert .XLS to .CSV using ADO/SELECT INTO .. IN
/// </summary>
static int Main(string[] args)
{
int iRVal = 0;

// change these (full!) pathes
string sSrcFSpec = @"S:\home\eh\proj\lang\csharp\sdb\xls2csv\data\test00.xls";
string sDstFSpec = @"S:\home\eh\proj\lang\csharp\sdb\xls2csv\data\test02.csv";
string sSQL = "SELECT * INTO test02.csv FROM [tblTest$] IN '" + sSrcFSpec + "' 'Excel 8.0;'";

Console.WriteLine(
"Src: {0}\nDst: {1}\nSQL: {2}\n"
, sSrcFSpec
, sDstFSpec
, sSQL
);

string sDstPath = Path.GetDirectoryName( sDstFSpec );
string sDstTable = Path.GetFileName( sDstFSpec );
string sTCS = string.Format(
"Driver={{Microsoft Text Driver (*.txt; *.csv)}};DBQ={0}"
, sDstPath
);

if( File.Exists( sDstFSpec ) )
{
File.Delete( sDstFSpec );
}

OdbcConnection oTCN = new OdbcConnection();
oTCN.ConnectionString = sTCS;
oTCN.Open();

OdbcCommand oCmd = new OdbcCommand();
oCmd.Connection = oTCN;
oCmd.CommandText = sSQL;
oCmd.ExecuteNonQuery();

oTCN.Close();

return iRVal;
}
}
}

build with "csc xsl2csv.cs" did convert/export tblTest from test00.xls to test02.csv:

S:\home\eh\proj\lang\csharp\sdb\xls2csv\simple
dir ..\data
...
Verzeichnis von S:\home\eh\proj\lang\csharp\sdb\xls2csv\data

27.02.2008 18:55 <DIR> .
27.02.2008 18:40 <DIR> ..
27.02.2008 14:57 16.384 test00.xls
27.02.2008 18:55 <DIR> hide
1 Datei(en) 16.384 Bytes
...
csc xls2csv.cs
Microsoft (R) Visual C# .NET Compiler version 7.10.3052.4
for Microsoft (R) .NET Framework version 1.1.4322
Copyright (C) Microsoft Corporation 2001-2002. All rights reserved.
...
xls2csv.exe
Src: S:\home\eh\proj\lang\csharp\sdb\xls2csv\data\test00.xls
Dst: S:\home\eh\proj\lang\csharp\sdb\xls2csv\data\test02.csv
SQL: SELECT * INTO test02.csv FROM [tblTest$] IN 'S:\home\eh\proj\lang\csharp\sdb\xls2csv\data\test00.xls' 'Excel 8.0;'
...
dir ..\data
...
Verzeichnis von S:\home\eh\proj\lang\csharp\sdb\xls2csv\data
...
27.02.2008 14:57 16.384 test00.xls
27.02.2008 18:57 2.205 test02.csv
27.02.2008 18:57 136 schema.ini
...

The first lines of test02.csv (German locale!)

"iId";"sName";"dtBirth"
0,00;"Hans Meier 1";27.02.2008 00:00:00
10,00;"Hans Meier 2";27.02.2008 00:00:00
20,00;"Hans Meier 3";27.02.2008 00:00:00

show that there are some hurdles to overcome: to change the floating point
output of the first column, I had to edit the (automagically created) schema.ini
file:

[test02.csv]
ColNameHeader=True
CharacterSet=1252
Format=Delimited(;)
Col1=iId Float ==> Col1=iId Integer
Col2=sName Char Width 255
Col3=dtBirth Date

and presto:

"iId";"sName";"dtBirth"
0;"Hans Meier 1";27.02.2008 00:00:00
10;"Hans Meier 2";27.02.2008 00:00:00

In addition, the POC code has to enhanced (parameters, error handling, ...). But
before that, you should make sure, that the necessary .NET components are/can be
installed on your server. I hope that the sample code will help you to estimate
whether this third strategy (which I'd favourite) suits your needs/resources.
.