Text Variable problem

[Saville]

Under version 4, I am unable to assign a text string to a variable (as text) if it contains leading zeros's. I have an Excel spreadsheet with a column of employee numbers, all 6 characters in length. Many have leading zero's
i.e. "000123".

Although the clipboard is holding the complete number, the leading zeros are being dropped when the the clipboard value is assigned to the variable.

This is a critical issue for me, as the PeopleSoft application I am interfacing with must include the leading zeros as in the search string.

I didn't have this issue with version 3.5. It arose with the enhanced variable functions in version 4.0

Any support on this issue would be greatly appreciated.

[forumsupport]

Hello,

Can you please paste the text format of the section of your task (involving variable assignment) that worked in 3.5 and also the corresponding section of task in 4.0 and we can suggest you alternative solution.

[Saville]

Excel column formatted as text and contains list of 6-character employee numbers, many with leading zeros.
These leading zeros MUST remain as part of the string passed to the application being used.

Examples using employee number "000123"

In version 3.5

Comment: Copy EmplNo from Excel column A, and assign it to variable "Emplno"
Clear Clipboard
Delay: (100 ms)
Keystrokes: [F2][SHIFT DOWN][HOME][SHIFT UP][CTRL DOWN]c[CTRL UP][TAB] in "Microsoft Excel*"
Delay: (200 ms)
Assign value of Clipboard to variable "$EmplNo$

<<< Result in variable $EmplNo$ is "000123" >>>

In version 4.0

Comment: Copy EmplNo from Excel column A, and assign it to variable "Emplno"
Clear Clipboard
Delay: (100 ms)
Keystrokes: [F2][SHIFT DOWN][HOME][SHIFT UP][CTRL DOWN]c[CTRL UP][TAB] in "Microsoft Excel*"
Variable Operation : $Clipboard$ To $EmplNo$
Delay: (200 ms)

<<< Result in variable $EmplNo$ is 123 (leading zeros are dropped, and value is passed as numeric) >>>

[Saville]

Any additional information available on this issue? Is it being researched?

-Thanks

[forumsupport]

Hello,

thank you for the information.

We did reproduce the similar behavior. The reason it does that is because Variable Operation deals with only numbers and hence it is trimming the initial zeros to reflect the actual numbers as opposed to string assignment.

You can still use the same approach as the 3.5 and we have forwarded this case to the development to handle in the future release of Automation Anywhere.

[Nick]

Saville,

For a more efficient task do the following.
Save your Excel file to a .csv file from the Save As file menu in Excel.
Open notepad and place the text below in the file. Save the File to the same directory as your .csv file.
If you have more than 4 columns you will need to add another line for each column
e.g. “Col5=Number Text” (For column 5)

The file name and extension is “Schema.ini”


[NameOfYourfile.csv]
Format=CSVDelimited
ColNameHeader=True
MaxScanRows=0
Col1=Number Text
Col2=Number Text
Col3=Number Text
Col4=Number Text

The above file tells ‘AA’ to treat your data as text so no formatting is lost such as leading ‘000123’

Next create a task to loop through your .csv file. This is so much more efficient and accurate than sending keystrokes to Excel and using the clipboard.

Here’s a sample task.
1 Connect to 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\PeopleSoftUploads\Account_TBL\;Extended Properties="text;HDR=YES;FMT=Delimited";Persist Security Info=False'

2 Execute SQL Statement: SELECT * FROM NameOfYourfile.csv

3 Open "http://peoplesoft_URL"

4 Click on 'Add a New Value' link in the webpage 'Account Code Table'

5 Start Loop " Each row in a SQL query dataset"

6 Type '$Dataset Column(1)$' in ACCT_CD_TBL_ACCT_CD in the webpage 'Account Code Table'

7 Click on 'javascript:submitAction_main(document.main,%20'#I CSearch');' image in the webpage 'Account Code Table'

8 Type '$Dataset Column(4)$' in ACCT_CD_TBL_EFFDT$0 in the webpage 'Account Code Table'

9 Type '$Dataset Column(2)$' in ACCT_CD_TBL_DESCR$0 in the webpage 'Account Code Table'

10 Type '$Dataset Column(3)$' in ACCT_CD_TBL_DESCRSHORT$0 in the webpage 'Account Code Table'

11 Click on 'javascript:submitAction_main(document.main,%20'#I CSave');' image in the webpage 'Account Code Table'

12 Click on 'javascript:submitAction_main(document.main,%20'#I CAdd');' image in the webpage 'Account Code Table'

13 End Loop