XLS to CSV Process Hanging up

[wattsupnow]

Morning!! I am converting an xls document to csv document via winbatch. This is running with windows "Task Scheduler" every 15 min on Windows 7 64 bit.

The problem is after the script starts it hangs on this part and will not make the csv file. I have end process (via task manager) then manual run the script and everything works just fine. 

I moved this script from a windows XP pc and never had a problem but now it hangs and will not make the csv file. Can someone help me. I can upload the whole script if needed.

Code Select Expand


AddExtender("WWINT44I.DLL") ;-- Networking Extender
AddExtender("WWWSK44I.DLL") ;-- Winsocket Extender
AddExtender("WWPST44I.DLL") ;-- Postie Extender


:CONVERT_XLS_TO_CSV
;===================================
; QC WILL USE THIS OPTION
;===================================
dirChange(APP_PATH)
CURRENT_XLS_LIST = fileItemize("*.xls")
XLS_COUNT = itemCount(CURRENT_XLS_LIST,@TAB)

IF XLS_COUNT < 1 || CURRENT_XLS_LIST == ""
LOG(LOG_PATH,APP_NAME,"XLS CONVERT: ","NO FILE(S) TO PROCESS")
RETURN
ENDIF
;===================================
; QC WILL USE THIS OPTION
;===================================
IF XLS_COUNT > 0
LOG(LOG_PATH,APP_NAME,"XLS CONVERT: ", "STARTED")

FOR XX = 1 to XLS_COUNT
XLS_NAME     = itemExtract(XX,CURRENT_XLS_LIST,@TAB)
CSV_TEMP     = strCat(APP_PATH,"\TEMP_","%XX%_",CSV_NAME)
SOURCE_XLS = strCat(APP_PATH,"\",XLS_NAME)

ExcelWS = ObjectOpen("Excel.Application")
EWS = ExcelWS.Application
EWS.visible = @FALSE
EWS.UserControl = @FALSE
EWS.DisplayAlerts     = @FALSE
EWB = EWS.Workbooks
EWB.Open("%SOURCE_XLS%")
WBA = ExcelWS.ActiveWorkbook
WBA.SaveAs(::FileName="%CSV_TEMP%",FileFormat=6,CreateBackup=0)
TimeDelay(1)
ExcelWS.quit
ObjectClose(ExcelWS)
dropWild(ExcelWS)

LOG(LOG_PATH,APP_NAME,"XLS CONVERT: ",STRCAT("DELETED - ",strUpper(SOURCE_XLS)))
LOG(LOG_PATH,APP_NAME,"XLS CSV NAME: ",strUpper(CSV_TEMP))
fileDelete(strCat(APP_PATH,"\",XLS_NAME))
TimeDelay(1)
NEXT
ENDIF
RETURN
;===================================
;===================================


[Deana]

A hanging service can indicate that a possible error occurred and the error was trying to be displayed.
By default, services use a noninteractive window station and cannot interact with the user. : http://msdn.microsoft.com/en-us/library/windows/desktop/ms683502(v=vs.85).aspx

You will need to add IntControl 38 to the beginning of your script to tell it to run quietly: No error messages are displayed. If an error log filename is specified, then error messages will be written to that file. Consult the errorlog for errors or add DebugTrace for further debugging.

[wattsupnow]

Thanks for reply - Added the InControl 38 but still no help. Here is a copy of the tracelog.

Code Select Expand


************************************************************

*** Debug Initialized ***

==============================
Wed 2/26/2014 9:56:11 AM
WinBatch 32 2014A
WIL DLL 6.14ana
C:\WINBATCH PROGRAMS\EM940_OKFOODS_QC.exe
Windows platform: NT, version: 6.1, build: 7601 (Service Pack 1)
ErrorMode: @CANCEL
Valid Code Signature: No
UAC Manifest Settings: level="asInvoker" uiAccess="false"
UAC Elevation Level: Standard User or Disabled
==============================

TIME            = TimeYMDHMS()
(0) VALUE STRING => "2014:02:26:09:56:11"

DATESTAMP       = StrCat(Itemextract(1,time,":"),'/',Itemextract(2,time,":"),'/',Itemextract(3,time,":"))
(0) VALUE STRING => "2014/02/26"

TIMESTAMP       = StrCat(Itemextract(4,time,":"),':',Itemextract(5,time,":"),':',Itemextract(6,time,":"))
(0) VALUE STRING => "09:56:11"

DATE_TIME       = strCat(DATESTAMP,' - ',TIMESTAMP)
(0) VALUE STRING => "2014/02/26 - 09:56:11"

STATUS          = IniReadPvt("SETTINGS","STATUS",0,INI_NAME)
(0) VALUE STRING => "0"

APP_NAME        = IniReadPvt("SETTINGS","APP_NAME","",INI_NAME)
(0) VALUE STRING => "EM940_OKFOODS_QC"

CSV_NAME        = IniReadPvt("SETTINGS","CSV_NAME","",INI_NAME)
(0) VALUE STRING => "OK_FOODS.CSV"

LOG_NAME        = IniReadPvt("SETTINGS","LOG_NAME","",INI_NAME)
(16) VALUE STRING => "LOG_EM940_OKFOODS_QC.TXT"

LIVE          = IniReadPvt("SETTINGS","LIVE",0,INI_NAME)
(16) VALUE STRING => "1"

APP_PATH    = IniReadPvt("SETTINGS","APP_PATH",STRCAT("C:\",APP_NAME),INI_NAME)
(16) VALUE STRING => "C:\EM940_OKFOODS_QC"

LOG_PATH    = IniReadPvt("SETTINGS","LOG_PATH",STRCAT("C:\",APP_NAME,"\LOG"),INI_NAME)
(16) VALUE STRING => "C:\EM940_OKFOODS_QC\LOG"

CSV_PATH    = IniReadPvt("SETTINGS","CSV_PATH",STRCAT("C:\",APP_NAME,"\CSV"),INI_NAME)
(16) VALUE STRING => "C:\EM940_OKFOODS_QC\CSV"

INI_TEMP = STRCAT(APP_PATH,"\TEMP_",APP_NAME,".INI")
(16) VALUE STRING => "C:\EM940_OKFOODS_QC\TEMP_EM940_OKFOODS_QC.INI"

IniWritePvt("TEMPORY_SETTINGS","TOTAL",0,INI_TEMP)
(31) VALUE INT => 1

FTP_TYPE    = IniReadPvt("SETTINGS","FTP_TYPE","",INI_NAME)
(31) VALUE STRING => "ASC"

USE_TELNET    = IniReadPvt("SETTINGS","USE_TELNET","",INI_NAME)
(31) VALUE STRING => "0"

ALPHA_LOC = IniReadPvt("SETTINGS","ALPHA_LOC","",INI_NAME)
(31) VALUE STRING => "194.168.2.10"

ALPHA_PROMPT = IniReadPvt("SETTINGS","ALPHA_PROMPT","",INI_NAME)
(31) VALUE STRING => "F2#"

ALPHA_USER = IniReadPvt("SETTINGS","ALPHA_USER","",INI_NAME)
(31) VALUE STRING => "EM_USER"

ALPHA_PASS = IniReadPvt("SETTINGS","ALPHA_PASS","",INI_NAME)
(47) VALUE STRING => ""

ALPHA_COM = IniReadPvt("SETTINGS","ALPHA_COM","",INI_NAME)
(47) VALUE STRING => ""

TEMP_NAME       = IniReadPvt("SETTINGS","TEMP_NAME","",INI_NAME)
(47) VALUE STRING => "TEMP_EM940_OKFOODS_QC"

IF dirExist(APP_PATH) == @FALSE THEN dirMake(APP_PATH)
(47) ==>FALSE=> (skipped)

IF dirExist(LOG_PATH) == @FALSE THEN dirMake(LOG_PATH)
(47) ==>FALSE=> (skipped)

IF dirExist(CSV_PATH) == @FALSE THEN dirMake(CSV_PATH)
(47) ==>FALSE=> (skipped)

LOG(LOG_PATH,APP_NAME,"------- STARTING LOG -------","")
(47) CALLING UDF (log)

RETURNING FROM UDF (log)

LOG(LOG_PATH,APP_NAME,"------- STARTING LOG -------","")
(62) RETURN VALUE INT => 0

GOSUB ANY_FILES
(62) GOSUB

dirChange(APP_PATH)
(62) VALUE INT => 1

CURRENT_LIST = fileItemize("*.xls")
(62) VALUE UNICODE => ZM Hold Status Report Wk beg 12-30-13.xls

FILE_COUNT = itemCount(CURRENT_LIST,@TAB)
(62) VALUE INT => 1

IF FILE_COUNT < 1
(62) END OPERATOR

RETURN
(62) RETURN VALUE INT => 0

GOSUB EXCEL_CHECK
(62) GOSUB

EXCEL = "EXCEL.EXE"
(62) VALUE STRING => "EXCEL.EXE"

IF(appExist(EXCEL) == @TRUE)
(4196) END OPERATOR

LOG(LOG_PATH,APP_NAME,"EXCEL: ","WAS NOT RUNNING")
(4196) CALLING UDF (log)

RETURNING FROM UDF (log)

LOG(LOG_PATH,APP_NAME,"EXCEL: ","WAS NOT RUNNING")
(4196) RETURN VALUE INT => 0

RETURN
(4196) RETURN VALUE INT => 0

GOSUB CONNECTION_TEST
(4196) GOSUB

dirChange(APP_PATH)
(4196) VALUE INT => 1

tophandle=iBegin(0,"","")
(4212) VALUE INT => 13369348

conhandle=iHostConnect(tophandle,ALPHA_LOC,@FTPPASSIVE,ALPHA_USER,ALPHA_PASS)
(4290) VALUE INT => 13369352

ERR = iGetLastError()
(4290) VALUE INT => 0

IF conhandle == 0
(4290) END OPERATOR

iClose(conhandle)
(4290) VALUE INT => 1

iClose(tophandle)
(4290) VALUE INT => 1

LOG(LOG_PATH,APP_NAME,"FTP CONNECTION TEST: ","CONNECTION MADE")
(4306) CALLING UDF (log)

RETURNING FROM UDF (log)

LOG(LOG_PATH,APP_NAME,"FTP CONNECTION TEST: ","CONNECTION MADE")
(4306) RETURN VALUE INT => 0

RETURN
(4306) RETURN VALUE INT => 0

GOSUB CONVERT_XLS_TO_CSV
(4306) GOSUB

dirChange(APP_PATH)
(4306) VALUE INT => 1

CURRENT_XLS_LIST = fileItemize("*.xls")
(4306) VALUE UNICODE => ZM Hold Status Report Wk beg 12-30-13.xls

XLS_COUNT = itemCount(CURRENT_XLS_LIST,@TAB)
(4321) VALUE INT => 1

IF XLS_COUNT < 1 || CURRENT_XLS_LIST == ""
(4321) END OPERATOR

IF XLS_COUNT > 0
(4321) IF DO==>TRUE

LOG(LOG_PATH,APP_NAME,"XLS CONVERT: ", "STARTED")
(4321) CALLING UDF (log)

RETURNING FROM UDF (log)

LOG(LOG_PATH,APP_NAME,"XLS CONVERT: ", "STARTED")
(4321) RETURN VALUE INT => 0

FOR XX = 1 to XLS_COUNT
(4321) FOR TRUE==>1

XLS_NAME     = itemExtract(XX,CURRENT_XLS_LIST,@TAB)
(4337) VALUE UNICODE => ZM Hold Status Report Wk beg 12-30-13.xls

CSV_TEMP     = strCat(APP_PATH,"\TEMP_","1_",CSV_NAME)
(4337) VALUE STRING => "C:\EM940_OKFOODS_QC\TEMP_1_OK_FOODS.CSV"

SOURCE_XLS = strCat(APP_PATH,"\",XLS_NAME)
(4337) VALUE UNICODE => C:\EM940_OKFOODS_QC\ZM Hold Status Report Wk beg 12-30-13.xls

ExcelWS = ObjectOpen("Excel.Application")
(4508) VALUE INT/COMOBJ => 5207588

EWS = ExcelWS.Application
(4524) VALUE INT/COMOBJ => 5207588

EWS.visible = @FALSE
(4524) VALUE VARIANT_BOOL => 0

EWS.UserControl = @FALSE
(4524) VALUE VARIANT_BOOL => 0

EWS.DisplayAlerts = @FALSE
(4524) VALUE VARIANT_BOOL => 0

EWB = EWS.Workbooks
(4524) VALUE INT/COMOBJ => 5208452

EWB.Open("C:\EM940_OKFOODS_QC\ZM Hold Status Report Wk beg 12-30-13.xls")
(4540) VALUE INT => 0

TERMINAL WIL ERROR=>1261 (COM/CLR: Exception)

;;;END OF JOB;;;

---------- Begin structure stack dump ----------
  1 gosub             109 gosub CONVERT_XLS_TO_CSV         EM940_OKFOODS_QC.wbt
  2 if                186 IF XLS_COUNT > 0                 EM940_OKFOODS_QC.wbt
  3 for               189 FOR XX = 1 to XLS_COUNT          EM940_OKFOODS_QC.wbt
-------- End structure stack dump --------

---------- Begin WWWBATCH.INI dump ----------
[COM Sub-system]
Function=InvokeMember
ErrorCode=9 (0x80020009)
ErrorDesc=Exception occurred.


[Deana]

Ok I have a few notes.

Upon inspecting the trace file you will see that the script is failing with the error 1261 (COM/CLR: Exception) on the Excel Open method, the extended erro information written to the wwwbatch.ini section indicated the error is ErrorCode=9 (0x80020009):

EWB.Open("C:\EM940_OKFOODS_QC\ZM Hold Status Report Wk beg 12-30-13.xls")
(4540) VALUE INT => 0

TERMINAL WIL ERROR=>1261 (COM/CLR: Exception)

;;;END OF JOB;;;

---------- Begin structure stack dump ----------
  1 gosub             109 gosub CONVERT_XLS_TO_CSV         EM940_OKFOODS_QC.wbt
  2 if                186 IF XLS_COUNT > 0                 EM940_OKFOODS_QC.wbt
  3 for               189 FOR XX = 1 to XLS_COUNT          EM940_OKFOODS_QC.wbt
-------- End structure stack dump --------

---------- Begin WWWBATCH.INI dump ----------
[COM Sub-system]
Function=InvokeMember
ErrorCode=9 (0x80020009)
ErrorDesc=Exception occurred.

It appears that Excel is unable to open "C:\EM940_OKFOODS_QC\ZM Hold Status Report Wk beg 12-30-13.xls".

I recommend adding code to your script to capture, and log the files that fail, for later debugging. See IntControl 73 (http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+Tutorials+Trap~Errors.txt)

Also, since this is running as service you should probably consider formatting the code as a native service, so that the script can properly handle service requests. We have a nice code sample in the help file that should help get you started: WinBatch.chm  topic Service Script.

[mattcarl]

My $0.02
I was getting the same message.
... try "Run as Administrator" and see if that resolves it.....