Missing commas in a CSV file

[peachmelba]

I have a file that I have created in Excel which has 21 columns. This is saved as a CSV file. I then import this file into Access using the following

Set rs = db.OpenRecordset(strTableName)
Do While Not EOF(1)
i = 0
rs.AddNew
Do Until i = intNoImportFields
With rs
Input #1, strFieldValue

Select Case strFieldValue
Case Is <> &quot;&quot;
.Fields(i).Value = strFieldValue
Case Else
'Check if field is mandatory
If varCheck(3, i) Then
![ErrorCode] = ![ErrorCode] - 1
End If

Select Case .Fields(i).Type
Case dbText
.Fields(i).Value = &quot;&quot;
Case dbDate
.Fields(i).Value = Null
Case dbNumeric
.Fields(i).Value = 0
Case dbBoolean
.Fields(i).Value = vbNo
Case Else
.Fields(i).Value = &quot;&quot;
End Select
End Select
End With
i = i + 1
Loop
rs.Update
Input #1, strFieldValue
Loop

This works great when the last row is populated and is really fast. But if i have a few cells with blank data at the end of each line when it is saved some commas are missing.

Does anyone know a away of using this function and checking which column or row I am in to make sure that I am not missing any commas?

Thanks

Peachmelba

 

[Craig0201]

Loop around the read in string using instr to count the commmas.

Craig

 

[peachmelba]

Craig

Thanks but that is the problem. I need to read in one cell at a time hence why I am using 'Input' rather than 'Line Input'.

The problem is with the number of commas which is not consistent. Excel seems to drop commas after about 10 rows. This means that the first ten rows have 21 commas and then the next 10 have only 20. This causes my numbering to go out of sink and I get all sort of problems with data conversion errors.

I have also tried look for Chr(13) with InStr to see if it is the last column, but I dont seem to be able to fid one.

Basically I need to detect when the Line #1, stField is on the last column so that I can skip out and go to a new record?

Any ideas?

Peachmelba

 

[Craig0201]

Why must you read 1 field at at time?

Read the row in, count the commas. Split the row into an array. Use the array to populate each field individually. Same effect and you get the count.

Craig