Invalid 'Column number' specified.

[loera]

Greetings,

I store data into a csv file from some tables on the web, but sometimes the table column size is different. When I get to a table that has less columns I get the error:

"Invalid 'Column Number' specified. Record(s) retrieved consists of 8 columns."

Here are two examples:

Frequency table has 10 columns: ULS License - Industrial/Business Pool, Conventional License - WNWJ805 - CALL FARMS - Frequencies Summary

Frequency table has 8 columns:ULS License - Paging and Radiotelephone License - KNKB548 - DON KEELER DBA: DISK COMMUNICATIONS, CO. - Frequencies Summary

What I tried to do was check to see, ahead of time, if the 9th column existed using IF VARIABLE but that just gave me the same error. I am sending a screen shot of the part of the task giving me this trouble.

Any suggestions?

Thank you,

-Loera
Using AA 5.5.2

[TylerMitton]

I've dealt with something similar... where a csv file had fewer columns in the first row than in some rows later on. To get around it, I added in a bunch of commas in the first row. You can try this as well... for each of your csv files do this before opening them:

Open "notepad C:\FilePath\FileName.csv"
Keystrokes: [END],,,,,,,,, in "*.csv - Notepad"
Keystrokes: [CTRL DOWN]s[CTRL UP][ALT DOWN][F4][ALT UP] in "*.csv - Notepad"

Add in as many commas in order to get the first row of the csv file to have at least the maximum number of columns that you will see in any of your files... you can add in extra and it wont cause a problem. I used to put in like 50 commas to make sure I didn't see any errors.

Hope that helps!

Tyler

[loera]

Thank you TylerMitton.

I tried your suggetion and it does work. However, the data gets stored in the wrong columns.

I would assume that if the error can catch how many columns there are, we could check that before we try to access the column that doesn't exist.

Any ideas?

-loera

[forumsupport]

Hello Loera,

If you are working with Automation Anywhere Enterprise version or higher then using Error Handling feature you can get total columns in a CSV file. Using Error Handling command we can get the error description (which contains total columns in a CSV) into system variable, $Error Description$ and with the help of String Manipulation command we can extract total columns into any user variable.

For your reference, we have created a sample task. Please copy it under location, 'C:\...\My Documents\Automation Anywhere\Automation Anywhere\My Tasks' folder. You may need to change the CSV path at line #1 as per your requirement.

[loera]

Greetings,

Regrettably we have only version 5.5 Primier. The work around I created was to check the column headings for information then using an IF statement to perform the appropriate action.
Thank you for the information that Automation Anywhere Enterprise version does have the ability to extract that sort of error data. I am sure that will come in handy in the future.

Thanks,

-Jose
AA 5.5 Premier

[TylerMitton]

Hi, sorry, I haven't been working with AA much recently, so haven't been on the forums. I just wanted to point out that adding the commas to the just first line of the file has never caused data to be stored in the wrong columns for me.

Also, I have stopped using notepad to add the commas as it is prone to errors if other tasks are running at the same time and take control of the keyboard. So I am now using a vbscript I created... I called it AddCommasToCsvFile.vbs and it's contents are as follows:

Set objArgs = WScript.Arguments
filePath = objArgs(0)

Const ForReading=1
Const ForWriting=2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set myFile = objFSO.OpenTextFile(filePath, ForReading, True)
Set myTemp= objFSO.OpenTextFile(filePath & ".tmp", ForWriting, True)
lineCount = 1

Do While Not myFile.AtEndofStream

myLine = myFile.ReadLine
If (lineCount = 1) Then
myLine = myLine & ",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"
End If
myTemp.WriteLine myLine
lineCount = lineCount + 1

Loop
myFile.Close
myTemp.Close
objFSO.DeleteFile(filePath)
objFSO.MoveFile filePath&".tmp", filePath


So I call that script after saving the csv files to my machine which adds in the commas. Note that is creates a copy, then deletes the original and renames the copy to have the same filename as the original... use at your own risk... but it's worked great for me for over a month.

Note sure if this is valid info for you or not but wanted to get it out there.

Cheers,

Tyler