reading a csv file - no comma at end

[Anonymous]

I have the following code (mostly stolen from a post here, somewhere), and it works, mostly.

The source data has line breaks, and no comma at the end of the line, so the last item on row 1 and the first item on row 2 (and so on) are NOT split. 

Any ideas?

	oFolder = "C:\Metadata Files"
	oFile = oFolder & "\" & "00_Source.csv"

	oRead = System.IO.File.OpenText(oFile)

	'read the file
	oRead = System.IO.File.OpenText(oFile)
	EntireFile = oRead.ReadToEnd()
	oRead.Close()

	'create aray from text info
	'splitting at that comma
	oSplit = Split(EntireFile, ",")
	
	oSplitcol = 1
	oSplitRow = 1
	i = 0
		
	For Each wrd In oSplit
		MessageBox.Show("Index " & vbCrLf _
				& "Row: " & oSplitRow & ", Col: " & oSplitcol & " = " & oSplit(i))
		oSplitcol += 1
		i += 1
		If oSplitcol > oSplitColTotal Then
			oSplitRow += 1
			oSplitcol = 1
		End If
		
	Next

[JelteDeJong · Accepted solution]

The following code is a difference in setup but i think it will do what you need. The main difference is that the lines are splitted not the entire file.

Dim oFile = "C:\Metadata Files\00_Source.csv"

Dim lineNumber As Integer = 0
Dim columnNumber As Integer = 0
For Each line As String In System.IO.File.ReadLines(oFile)
	Dim cells As String() = line.Split(",")
	columnNumber = 0
	For Each cell As String In cells
		Console.WriteLine("line: {0}, column: {1}, value:{2}", lineNumber, columnNumber, cell)
		columnNumber = columnNumber + 1
	Next
	lineNumber = lineNumber + 1
Next

[Anonymous]

thank you! that helps tons.

I was hoping there'd be something like that - I was starting on split by vbcrlf, then split each one by "," and manually loop through each and assign to a 2 dimensional array... and then remembered that ilogic doesn't like dynamic arrays.

I don't have enough hair left for this spit.

[JamieVJohnson2]

iLogic editor is VB.Net based, so you can use list(of T) which can nest itself, or if you only need a single key value pair, then sortedlist(of T-Key, T-Value).

'simple one tier:

Dim lstParent as new list(of string)

'complex multi tier:

dim lstParent as new list(of list(of string))

dim lstChild as new list(of string)

lstParent.add(lstChild) 'or

lstParent.add(new list(of string))

'sorted list method:

dim lstParent as new sortedlist(of integer, string)

lstParent.add(intValue, strValue)

'and object nesting can go as crazy as you can keep your head straight.

[Anonymous]

hmmm... worth a look - the issue now is that strings are "xxx,xxx,,,xxx" and so on, but when read by line, they don't maintain the bounding " around strings which contain commas.

This used to be so easy.

[Anonymous · Accepted solution]

Final solution that worked in my case, an Excel generated CSV with line breaks and text in "quotes".

In case anyone comes looking:

Imports Microsoft.VisualBasic.FileIO.TextFieldParser
Imports System.IO

Class ThisRule
	Dim oMetaTable(999, 5) '2 dimensional array of metadata - max 998 drawings
        'by placing this in "ThisRule" class, the array is available to all subs and functions in this rule
Sub main
	Dim oParentDoc As Inventor.Document
	Dim oFolderName As String
	Dim oFileName As String
	'Read the metadata
	oFolderName = "C:\Metadata Files"
	oFileName = oFolderName & "\" & "00_Source.csv"
	
	Dim oRowNumber As Integer = 0
	Dim oColumnNumber As Integer = 0

'*** Original source:
'https://docs.microsoft.com/en-us/dotnet/api/microsoft.visualbasic.fileio.textfieldparser?redirectedfrom=MSDN&view=netframework-4.8
'***
    MyReader = New Microsoft.VisualBasic.FileIO.TextFieldParser(oFileName)
    MyReader.TextFieldType =  Microsoft.VisualBasic.FileIO.FieldType.Delimited
    MyReader.Delimiters = New String() {","}
    Dim currentRow As String()
    'Loop through all of the fields in the file. 
    'If any lines are corrupt, report an error and continue parsing. 
    While Not MyReader.EndOfData
        Try
            currentRow = MyReader.ReadFields()
			'add each item to the metatable
			oColumnNumber = 0
			For Each oField In currentRow
				oMetaTable(oRowNumber, oColumnNumber) = oField
				oColumnNumber += 1
			Next

			oRowNumber += 1
        Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
            MsgBox("Line " & ex.Message & 
            " is invalid.  Skipping")
        End Try
    End While
End Sub
End Class