Reading Comma-Delimited File

HCL Domino Domino Forum
1

OK guys, I apologise for the potential simplicity of this one, but I cannot get my head around it - Sorry!

I need to read in a csv file, and from it, create notes documents. Everything is fine, apart from not all fields in the csv file are populated, so you can end up with lines as follows:

1,2,3,8,10

If I try to read this file in, I get a type-mismatch error.

Can anyone tell me how I can do this, without modifying the csv file beforehand.

Thanks,

Darren

Here is the code I am currently using:

Dim arrayOfRecs() As RecType

Dim fileNum As Integer

fileNum = Freefile()

Open “file.txt” For Input As fileNum

Dim txt As String

Dim counter As Integer

Dim countRec As Integer

Do While Not Eof( fileNum )

Line Input #fileNum, txt

counter = counter + 1

Loop

Seek fileNum, 1

Redim arrayOfRecs( 1 To counter )

countRec = 1

While Not Eof( fileNum )

Input #fileNum, _

arrayOfRecs( countRec ).RecordType, _

arrayOfRecs( countRec ).ClientNumber, _

arrayOfRecs( countRec ).PolicyNumber, _

arrayOfRecs( countRec ).InceptionDate, _

arrayOfRecs( countRec ).ExpiryDate, _

arrayOfRecs( countRec ).LastRenewal, _

arrayOfRecs( countRec ).CurrentRenewal, _

arrayOfRecs( countRec ).EffectiveDate, _

arrayOfRecs( countRec ).Title, _

arrayOfRecs( countRec ).Initials, _

arrayOfRecs( countRec ).Surname, _

arrayOfRecs( countRec ).RishHouse, _

arrayOfRecs( countRec ).RiskStreet, _

arrayOfRecs( countRec ).RiskLocality, _

arrayOfRecs( countRec ).RiskCity, _

arrayOfRecs( countRec ).RiskCounty, _

arrayOfRecs( countRec ).RiskPostCode, _

arrayOfRecs( countRec ).DateOfBirth, _

arrayOfRecs( countRec ).InsuredAge, _

arrayOfRecs( countRec ).Occupation, _

arrayOfRecs( countRec ).BuildArea, _

arrayOfRecs( countRec ).ContentsArea, _

arrayOfRecs( countRec ).YearPropertyBuilt , _

arrayOfRecs( countRec ).TypeOfProperty, _

arrayOfRecs( countRec ).NoOfBedrooms, _

arrayOfRecs( countRec ).RoofConstruction, _

arrayOfRecs( countRec ).WallConstruction, _

arrayOfRecs( countRec ).TypeOfCover, _

arrayOfRecs( countRec ).Security, _

arrayOfRecs( countRec ).AccDamageContents, _

arrayOfRecs( countRec ).AccDamageBuildings, _

arrayOfRecs( countRec ).PersonalBelongings, _

arrayOfRecs( countRec ).NoClaimsDiscount, _

arrayOfRecs( countRec ).ContentsSumInsured, _

arrayOfRecs( countRec ).BuildingsSumInsured, _

arrayOfRecs( countRec ).CorrespondenceHouse, _

arrayOfRecs( countRec ).CorrespondenceStreet, _

arrayOfRecs( countRec ).CorrespondenceLocality, _

arrayOfRecs( countRec ).CorrespondenceCity, _

arrayOfRecs( countRec ).CorrespondenceCounty, _

arrayOfRecs( countRec ).CorrespondencePostCode, _

arrayOfRecs( countRec ).Endorsement1, _

arrayOfRecs( countRec ).Endorsement2, _

arrayOfRecs( countRec ).Endorsement3

countRec = countRec + 1

Wend

Close fileNum

2

Subject: Reading Comma-Delimited File

I haven’t looked too deeply at your code but I have just done a simple thing and it looks like this.This code will assign each comma delimited field to successive variables (even if they are blank) and will trim off leading and trailing blanks. After each field assignment it modifies LineText to move to next postion.

Do While Not Eof(fileN)

Line Input #fileN, LineText

LineField1 = Trim(Left(LineText,Instr(LineText,“,”)-1))

LineText = Right(LineText, (Len(LineText)-Instr(LineText,“,”)))

LineField2 = Trim(Left(LineText,Instr(LineText,“,”)-1))

LineText = Right(LineText, (Len(LineText)-Instr(LineText,“,”)))

…etc.

Loop

3

Subject: RE: Reading Comma-Delimited File

Works a charm - thank you very much!

Darren

4

Subject: Reading Comma-Delimited File

Try splitting the line in the file into a variant object for testing purposes and placing into a variable., etc.

eg:

Dim parseRecord As Variant

Open filename For Input As fileNum%

Do While Not Eof(fileNum%)

	Line Input #fileNum%, txt$  ' Read each line of the file.

	If Not counter% = 0 Then

                                            parseRecord = Split( recordData, "," )

                                            'Insert Code here for reviewing data, use parseRecord( 0 ) to retrieve data here is an example.

                                            line1item = parseRecord(0)

		line2item = parseRecord(1)

		line3item = parseRecord(2)

	End If

	counter% = counter% + 1

Loop	

Close fileNum%