CSVtoarray get column values

[jpf1281]

Hello,

I am trying to figure out how to return the column values in an array. I am using the CSVtoArray function to create the array from an existing file, but I am unable to return the column values. The overally purpose of this automation is to take the fields in the array and create a new file based on the data in a different format and skip any columns where there is no data. Below is the code I have so far. Any help with what I am missing would be greatly appreciated!

CSVFileToArray>C:\ftpfiles\BHR\Test.csv,arrCSV

If>arrCSV_count>0
Let>row=0
Let>col=0
Repeat>row
MessageModal>%row%
Let>colCount=%arrCSV_2_count%
MessageModal>%colCount%
Repeat>col

Let>field1=%arrCSV_%row%_%col%%
if>field1=""
MessageModal>Value is blank
else
MessageModal>%field1%
endif
//MessageModal>%field1%
Wait 2
WriteLn>C:\ftpfiles\BHR\NewFileTest.csv,nWLNRes,%field1%,%field1%

Let>col=col+1
Until>col=arrCSV_%col%_count
Let>row=row+1
Until>row=arrCSV_count
Endif

[Grovkillen]

Let>field1=%arrCSV_%row%_%col%% <--- wrong

Let>field1=arrCSV_%row%_%col% <--- correct

[jpf1281]

Got it! Thanks for the clarification there. So with that out of the wy and values being returned. I am stumped on another issue that seems like it should work. For the first part of IF statement I am trying to write all values of the first line in the array to a new file. The code instead returning value of arrCSV_0_0 instead of the actual value in the array. The second part of the IF is returning these values instead, even though at that point it should loop back and be on row 1, and not 0 now. Any ideas?


CSVFileToArray>C:\ftpfiles\BHR\Test.csv,arrCSV

Let>row=0
Let>col=0
Let>colCount=5

If>arrCSV_count>0
Let>row=0

Repeat>row
Let>col=0

if>row=0
Repeat>col
Let>LineToWrite=arrCSV_%row%_%col%
WriteLn>C:\ftpfiles\BHR\NewFileTest.csv,nWLNRes,%LineToWrite%
Let>col=%col%+1
Until>col=colCount

else

Repeat>col
Let>field1=arrCSV_%row%_%col%
WriteLn>C:\ftpfiles\BHR\NewFileTest.csv,nWLNRes,%field1%
Let>col=%col%+1
Until>col=colCount

endif

Let>row=%row%+1
Until>row=arrCSV_count
Endif

[Grovkillen]

You need to start with column 1 and row 1.

[hagchr]

Hi, using CSVFileToArray and looking through the watch list, it seems rows starts at 1 and cols start at 0 (don't know why), ie you need to reflect that in the loop starts and stops.

One example for dumping the (non-empty) items into a file:

Code: Select all

Let>source=C:\Users\Christer\Desktop\Test.csv
Let>target=C:\Users\Christer\Desktop\NewFileTest.csv
CSVFileToArray>%source%,arrCSV

Let>row=0
While>row<arrCSV_COUNT
  Add>row,1
  Let>col=0
  While>col<arrCSV_%row%_COUNT
    If>arrCSV_%row%_%col%<>
      WriteLn>%target%,nWLNRes,arrCSV_%row%_%col%
    EndIf
    Add>col,1
  EndWhile
EndWhile

[jpf1281]

Perfect, this was incredibly helpful!