# Reading Date as a variable from File in Automation

**URL:** <https://forums.toadworld.com/t/reading-date-as-a-variable-from-file-in-automation/49643>\
**Category:** Toad Data Point & Toad Intelligence Central\
**Created:** [April 22, 2020, 10:00am UTC](https://forums.toadworld.com/t/reading-date-as-a-variable-from-file-in-automation/49643 "2020-04-22T10:00:04Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![rvr.vijay1](https://avatars.discourse-cdn.com/v4/letter/r/45deac/32.png) [@rvr.vijay1](https://forums.toadworld.com/u/rvr.vijay1)\
**Post date:** [April 22, 2020, 10:00am UTC](https://forums.toadworld.com/t/reading-date-as-a-variable-from-file-in-automation/49643/1 "2020-04-22T10:00:04Z")

</div>

Hey Guys,

I am trying to automated one of my report using select to file activity.  
I query i have only date as dynamic but want to read from excel/access file. So each time it is giving different type of errors when trying to export the data.  
I tried using both :Variable and #Variable# to define the variable but seems it is not working.

 ![image](https://global.discourse-cdn.com/quest/original/2X/8/8262855150386c390652226c709413762a5c098f.png)  
 ![image](https://global.discourse-cdn.com/quest/original/2X/b/b21a98da80a261171d89cfaf379dad86fea07c77.png)  
4/22/2020 3:29:02 PM: Starting Automation Script  
4/22/2020 3:29:02 PM: Setting up environment  
4/22/2020 3:29:02 PM: Script run by Toad Data Point 5.1.2.166 as rvijay on JCPSI7HIJGH2  
4/22/2020 3:29:02 PM: AppDataDir = C:\Users\rvijay\AppData\Roaming\Quest Software\Toad Data Point 5.1  
4/22/2020 3:29:02 PM: Temp Dir = C:\Users\rvijay\AppData\Local\Temp  
4/22/2020 3:29:02 PM: Build started  
4/22/2020 3:29:02 PM: Compiling script  
4/22/2020 3:29:02 PM: Start CompileWorkflow  
4/22/2020 3:29:02 PM: Before Compile  
4/22/2020 3:29:04 PM: Return from CompileWorkflow  
4/22/2020 3:29:04 PM: Workflow Compiled without Errors  
4/22/2020 3:29:04 PM: Workflow Compiled has warnings  
4/22/2020 3:29:04 PM: Warning: No Exception Handler. To send an Email when there is an error, change the value of Email on Error to true and enter email values.  
4/22/2020 3:29:04 PM: Build completed  
4/22/2020 3:29:04 PM: Create Workflow instance  
4/22/2020 3:29:04 PM: Started workflow instance  
4/22/2020 3:29:04 PM: Begin execution script activities  
4/22/2020 3:29:04 PM: Variable "ROOT\_PATH" set to  
4/22/2020 3:29:04 PM: Variable "RUN\_MODE" set to Test  
4/22/2020 3:29:04 PM: Scanning the script for prompt bind variables  
4/22/2020 3:29:04 PM: Connecting to InputFile\_Markdown  
4/22/2020 3:29:04 PM: Variable "StartDate" final formatted value: 2/3/2020 12:00:00 AM  
4/22/2020 3:29:04 PM: Connecting to InputFile\_Markdown  
4/22/2020 3:29:04 PM: Variable "EndDate" final formatted value: 3/6/2020 12:00:00 AM  
4/22/2020 3:29:04 PM: Begin Export of File\_1  
4/22/2020 3:29:04 PM: Connection description = [LPB1-SCAN.JCPENNEY.COM](http://LPB1-SCAN.JCPENNEY.COM);MOMPODS (RVIJAY), RVIJAY  
4/22/2020 3:29:04 PM: Connection node found  
4/22/2020 3:29:04 PM: Connection to: [LPB1-SCAN.JCPENNEY.COM](http://LPB1-SCAN.JCPENNEY.COM);MOMPODS (RVIJAY), RVIJAY  
4/22/2020 3:29:04 PM: Connection successful  
4/22/2020 3:29:04 PM: Connection logon: RVIJAY  
4/22/2020 3:29:04 PM: Last schema: RVIJAY  
4/22/2020 3:29:04 PM: Default schema: RVIJAY  
4/22/2020 3:29:04 PM: Auto Commit: False  
4/22/2020 3:29:04 PM: SQL to execute: select th.store\_num  
, sum(item\_ord\_qty) as man\_mkdn\_count  
, (sum(case when (TID.disc\_reason\_txt = 'IA1') and (TID.adj\_type\_code in ('4', '6', '14')) then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end)

- sum(case when (TID.disc\_reason\_txt = 'IA1') and (TID.adj\_type\_code = '12') then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end)
- sum(case when TID.disc\_reason\_txt like 'IA2%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end)
- sum(case when TID.disc\_reason\_txt like 'IA3%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end)
- sum(case when TID.disc\_reason\_txt like 'IA4%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end)
- sum(case when TID.disc\_reason\_txt like 'IA5%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end)
- sum(case when TID.disc\_reason\_txt like 'IA6%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end)
- sum(case when TID.disc\_reason\_txt like 'IA7%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end)
- sum(case when TID.disc\_reason\_txt like 'IA8%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end)  
+sum(case when (TID.disc\_reason\_txt like 'Disc' and TID.ADJ\_TYPE\_CODE = 1) THEN (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end)
- SUM(case when (TID.DISC\_REASON\_TXT = 'Coupon Disc' and TID.ADJ\_TYPE\_CODE in (22,23)) then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end)) as TOTAL  
, sum(case when (TID.disc\_reason\_txt = 'IA1') and (TID.adj\_type\_code in ('4', '6', '14')) then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end) as ticket\_wrong\_regular  
, sum(case when (TID.disc\_reason\_txt = 'IA1') and (TID.adj\_type\_code = '12') then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end) as ticket\_wrong\_clearance  
, sum(case when TID.disc\_reason\_txt like 'IA2%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end) as PRICE\_MATCH  
, sum(case when TID.disc\_reason\_txt like 'IA3%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end) as not\_as\_signed  
, sum(case when TID.disc\_reason\_txt like 'IA4%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end) as IMPERFECTION  
, sum(case when TID.disc\_reason\_txt like 'IA5%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end) as coupon\_or\_cert  
, sum(case when TID.disc\_reason\_txt like 'IA6%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end) as APPEASEMENT  
, sum(case when TID.disc\_reason\_txt like 'IA7%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end) as MARKDOWN\_ZERO  
, sum(case when TID.disc\_reason\_txt like 'IA8%' then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end) as even\_exchange  
, sum(case when (TID.disc\_reason\_txt like 'Disc' and TID.ADJ\_TYPE\_CODE = 1) THEN (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end) AS OTHER\_ITM\_MAN\_ADJ  
, SUM(case when (TID.DISC\_REASON\_TXT = 'Coupon Disc' and TID.ADJ\_TYPE\_CODE in (22,23)) then (tid.starting\_rtl\_amt - tid.adj\_rtl\_amt) else 0 end) AS TRAN\_WIDE\_MAN\_ADJ  
from JCPRMS.JCP\_TRAN\_HEAD TH  
INNER JOIN JCPRMS.JCP\_TRAN\_ITEM TI  
ON TH.JCP\_TRAN\_HEAD\_SEQ = TI.JCP\_TRAN\_HEAD\_SEQ  
AND TH.STORE\_NUM = TI.STORE\_NUM  
AND TH.PARTITIONING\_DATE = TI.PARTITIONING\_DATE  
INNER JOIN JCPRMS.JCP\_TRAN\_ITEM\_DISC TID  
ON TI.STORE\_NUM = TID.STORE\_NUM  
AND TI.PARTITIONING\_DATE = TID.PARTITIONING\_DATE  
AND TI.JCP\_TRAN\_ITEM\_SEQ = TID.JCP\_TRAN\_ITEM\_SEQ  
where TH.PARTITIONING\_DATE BETWEEN to\_date(:StartDate,'MM/DD/YYY') AND to\_date(:EndDate,'MM/DD/YYY')  
AND (TID.DISC\_REASON\_TXT LIKE 'IA%' or (TID.DISC\_REASON\_TXT = 'Disc' and TID.ADJ\_TYPE\_CODE = 1 AND TID.ADJ\_MANUAL\_CODE = 'Y') OR (TID.DISC\_REASON\_TXT = 'Coupon Disc' and TID.ADJ\_TYPE\_CODE in (22,23) AND TID.ADJ\_MANUAL\_CODE = 'Y'))  
and th.void\_info\_ind \<\> 'Y'  
and ti.ln\_voided\_ind \<\> 'Y'  
and th.store\_num \<\> 2857  
and th.jcp\_tran\_head\_seq not in (select jcp\_tran\_head\_seq from jcp\_tran\_head where void\_info\_ind = 'Y' and PARTITIONING\_DATE BETWEEN to\_date(:StartDate,'MM/DD/YYY') AND to\_date(:EndDate,'MM/DD/YYY'))  
group by th.store\_num  
4/22/2020 3:29:04 PM: ValidateSuffix: Using base suffix:  
4/22/2020 3:29:04 PM: ValidateSuffix: Suffix is predefined literal - returning empty suffix instead  
4/22/2020 3:29:04 PM: Export Template: \<Quest.Toad.ImportExport.ExportEngine\>\</Quest.Toad.ImportExport.ExportEngine\>  
4/22/2020 3:29:19 PM: 3:29:04 PM Thread (4) Adding Export Notification Subscriber {0}  
4/22/2020 3:29:19 PM: 3:29:04 PM Thread (4) Export Started [4/22/2020 3:29:04 PM]  
4/22/2020 3:29:19 PM: 3:29:04 PM Thread (4) \*\* START \*\*  
4/22/2020 3:29:19 PM: 3:29:04 PM Thread (4) Export using connection: [LPB1-SCAN.JCPENNEY.COM](http://LPB1-SCAN.JCPENNEY.COM);MOMPODS (RVIJAY), RVIJAY  
4/22/2020 3:29:19 PM: 3:29:11 PM Thread (4) Export Wizard: Building object list  
4/22/2020 3:29:19 PM: 3:29:11 PM Thread (4) Build List done.  
4/22/2020 3:29:19 PM: 3:29:11 PM Thread (4) Exporting Data (1 of 1)  
4/22/2020 3:29:19 PM: 3:29:11 PM Thread (4) BEGIN Query :  
4/22/2020 3:29:19 PM: 3:29:11 PM Thread (4) Object SQL Query started.  
4/22/2020 3:29:19 PM: 3:29:18 PM Thread (20) InternalReadBackground - exception ORA-01858: a non-numeric character was found where a numeric was expected  
4/22/2020 3:29:19 PM: 3:29:18 PM Thread (20) InternalReadBackground - stack at Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()  
4/22/2020 3:29:19 PM: 3:29:18 PM Thread (6) DoneReadTreadWriter  
4/22/2020 3:29:19 PM: 3:29:18 PM Thread (6) DoneReadThreadWriter finally - adapter.RowsRead 0  
4/22/2020 3:29:19 PM: 3:29:19 PM Thread (4) END Query :  
4/22/2020 3:29:19 PM: 3:29:19 PM Thread (4) Object SQL Query finished with error: ORA-01858: a non-numeric character was found where a numeric was expected  
4/22/2020 3:29:19 PM: 3:29:19 PM Thread (4) at Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()  
4/22/2020 3:29:19 PM: 3:29:19 PM Thread (4) Export Finished [4/22/2020 3:29:19 PM]  
4/22/2020 3:29:19 PM: 3:29:19 PM Thread (4) Export time = 00:00:14.3782394  
4/22/2020 3:29:19 PM: Thread (4) Build Time 00:00:06.6611209  
4/22/2020 3:29:19 PM: Thread (4) Complete Time 00:00:14.3782394  
4/22/2020 3:29:19 PM: 3:29:19 PM Thread (4) \*\* END \*\*  
4/22/2020 3:29:19 PM: File\_1 - ORA-01858: a non-numeric character was found where a numeric was expected

4/22/2020 3:29:19 PM: ORA-01858: a non-numeric character was found where a numeric was expected  
4/22/2020 3:29:19 PM: System.Exception - File\_1 - ORA-01858: a non-numeric character was found where a numeric was expected

Inner exception:  
System.ApplicationException - ORA-01858: a non-numeric character was found where a numeric was expected  
at Quest.Toad.Workflow.Activities.Database.SelectToExcelActivity.Execute(ActivityExecutionContext executionContext)

4/22/2020 3:29:19 PM: Failed

Sharing the log file too in case of use!
