csv file and lists

[cfastM58RS]

Is there a way to read the first column of a csv file into one list and the second column into another list?  Right now I have a macro that reads the entire line (toolname,toolnumber) into a list and then I have to separate each string into 2 separate lists by doing a FOREACH loop.  This takes a long time when the csv file has 1000 lines or more.

Is there a command to read everything up to the "," and then jump to the next line? Or am I better off having one csv file for the toolnames and one for the toolnumbers?

[urizenYHS3W]

Read the entire line and then split it using the tokens() function.

Something like this but you need to fill in the blanks.

STRING LIST cvs = {}
FILE READ $cvs FROM cvs_file
FOREACH line IN cvs {
  STRING LIST toks = tokens(line, ',')
  INT x = add_last(names, toks[0])
  $x = add_last(numbers, toks[1])
}

[cfastM58RS]

Thanks for the reply.

I am currently using the tokens function to split the csv file into my separate lists. The problem is having to FOREACH over the csv file to split it.  I'm wondering if it is possible to only import the first column of the csv and then import the second column.

I'm probably splitting hairs here but I have some time and want to write an efficient macro.  If I can avoid doing a FOREACH loop on the csv I think it will speed things up.

[Anonymous]

That's a lot of tools.  Are you doing something with every line in the list?  If not, perhaps read the whole thing in, but then split the lines up using tokens as suggested but only as you need them.

[cfastM58RS]

I have one tool database for multiple machines.  I am creating a csv file for each machine to define the tool numbers. (the machine operators are able to program in a different software at the machine so I want to match their tool numbers to ease switching over to powermill.) The csv file contains only the tool name and tool number.  

If any one has done this before I would appreciate a nudge in the right direction.  I have a working macro right now but it takes too long to run because it has to FOREACH through the csv file for every tool in the project.

[Anonymous]

I don't think there is a way to do exactly what you are asking in a decent amount of time.  Perhaps some kind of script that runs outside of PowerMill could, but that might be a complicated solution to a simple problem.  What are you doing with the lists once you have them imported from the CSV?  Perhaps there is a different way to get where you're going.

[cfastM58RS · Accepted solution]

I made some changes that helped speed it up.  I'm sure there is still room for improvement but it works for now.  I attached the macro if anyone wants to take a look.(It's messy but gets the job done!)

I am going to "accept as solution" to close this out but if anyone has some more input I'd gladly listen.

[Anonymous]

Can you upload an example of the CSV?  I think I could speed that up at least a little.

[cfastM58RS]

Here's an example

[Anonymous]

Well, I tried what I thought should be faster, but it ended up not being so.  See code below in case it helps at all.  Code that improves the time slightly is pasted below that.

String CSVFileName = "H:\Powermill Config\Tool CSV files\OKK_VM7.csv"
// $CSVFileName = FILESELECT "CSV File"
INT $StartTime = time()
//imprt csv data to a list
STRING LIST $csvFile = {}
FILE OPEN $CSVFileName FOR READ AS input
FILE READ $csvFile FROM input
FILE CLOSE input

STRING LIST tokens = {}
String FilterExpression = ""
ENTITY LIST $ToolNames = {}
String TName = ""

STRING LIST $remove = {"_1", "_2", "_3", "_4", "_5", "_6", "_7", "_8", "_9"} 
INT $LineLength = 0
INT $TokPos = 0
STRING $TokString = ""

	FOREACH $line IN $csvFile {
		$tokens = TOKENS($line, ',')
		$FilterExpression = "position(name;'" + $tokens[0] + "') != -1"
		$ToolNames = filter(folder('tool');$FilterExpression)
		Foreach $tl in $ToolNames {
			$TName = $tl.name
			$LineLength = length($TName)
			$TokPos = $LineLength -2
			$TokString = substring($TName, $TokPos, $LineLength)
			WHILE member($remove, $TokString) {			
				$TName = substring($TName, 0, $TokPos)
				$LineLength = length($TName)
				$TokPos = $LineLength -2
				$TokString = substring($TName, $TokPos, $LineLength)
			}
			If $TName == $tokens[0] {
				$tl.number.userdefined = 1
				$tl.number = int($tokens[1])				
			}
		}
	}
	
INT $EndTime = time()

INT $TimeDif = $EndTime - $StartTime

STRING $msg = string($TimeDif) + " seconds."
MESSAGE INFO $msg

Slightly improved time code below.

String CSVFileName = "H:\Powermill Config\Tool CSV files\OKK_VM7.csv"
// $CSVFileName = FILESELECT "CSV File" "*.csv"
INT $StartTime = time()
//imprt csv data to a list
STRING LIST $csvFile = {}
FILE OPEN $CSVFileName FOR READ AS input
FILE READ $csvFile FROM input
FILE CLOSE input

STRING LIST $ToolNames = extract(folder('tool');'name')
	
//Get rid of trailing "_1" "_2" on names for comparing to csv
STRING LIST $remove = {"_1", "_2", "_3", "_4", "_5", "_6", "_7", "_8", "_9"} 
INT $LineLength = 0
INT $TokPos = 0
STRING $TokString = ""

//renumber tools
INT LIST $NewNumbers = {}
INT $counter = 0
INT $Tnumber = 0
STRING $toolname = ""
FOREACH $tName IN $ToolNames {

	$LineLength = length($tName)
	$TokPos = $LineLength -2
	$TokString = substring($tName, $TokPos, $LineLength)
	WHILE member($remove, $TokString) {			
		$tName = substring($tName, 0, $TokPos)
		$LineLength = length($tName)
		$TokPos = $LineLength -2
		$TokString = substring($tName, $TokPos, $LineLength)
		}

	FOREACH $line IN $csvFile {
		STRING LIST $tokens = TOKENS($line, ',')
		IF $tName == $tokens[0] {
			$Tnumber = int($tokens[1])
			$toolname = $ToolNames[$counter]
			ENTITY $finalTool = $entity('Tool', $toolname)
			$finalTool.number.userdefined = 1
			$finalTool.number = $Tnumber
			
			BREAK
			}
		}
	$counter = $counter +1	
	}

INT $EndTime = time()

INT $TimeDif = $EndTime - $StartTime

STRING $msg = string($TimeDif) + " seconds."
MESSAGE INFO $msg	

[cfastM58RS]

Thanks for taking the time to help me out, greatly appreciated!