CSVtoarray get column values

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
jpf1281
Newbie
Posts: 11
Joined: Mon Jun 14, 2021 1:40 pm

CSVtoarray get column values

Post by jpf1281 » Wed Feb 08, 2023 3:51 pm

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

User avatar
Grovkillen
Automation Wizard
Posts: 1009
Joined: Fri Aug 10, 2012 2:38 pm
Location: Bräcke, Sweden
Contact:

Re: CSVtoarray get column values

Post by Grovkillen » Wed Feb 08, 2023 7:42 pm

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

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

Running: 15.0.24
version history

jpf1281
Newbie
Posts: 11
Joined: Mon Jun 14, 2021 1:40 pm

Re: CSVtoarray get column values

Post by jpf1281 » Wed Feb 08, 2023 8:31 pm

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

User avatar
Grovkillen
Automation Wizard
Posts: 1009
Joined: Fri Aug 10, 2012 2:38 pm
Location: Bräcke, Sweden
Contact:

Re: CSVtoarray get column values

Post by Grovkillen » Thu Feb 09, 2023 5:56 am

You need to start with column 1 and row 1.
Let>ME=%Script%

Running: 15.0.24
version history

hagchr
Automation Wizard
Posts: 327
Joined: Mon Jul 05, 2010 7:53 am
Location: Stockholm, Sweden

Re: CSVtoarray get column values

Post by hagchr » Thu Feb 09, 2023 10:13 am

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
Newbie
Posts: 11
Joined: Mon Jun 14, 2021 1:40 pm

Re: CSVtoarray get column values

Post by jpf1281 » Thu Feb 09, 2023 9:04 pm

Perfect, this was incredibly helpful!

Post Reply
cron
Sign up to our newsletter for free automation tips, tricks & discounts