Loading csv file to table fails

[Caravelle]

Here is the relevant code:

  OpenDialog1.Filter = "csv Files|*.csv"
  If OpenDialog1.Show <> cCancel Then
    tblImport.LoadCSV(OpenDialog1.File, ",", False, True)
  End If

It normally works without problem, but this is what happened today with one particular csv file:

tblImport.LoadCSV(OpenDialog1.File, ",", True, True)
Error description: length cannot be less than zero.
Parameter name: length

The csv file looks just the same as the other csv files which do not produce the error. All were produced the same way, indeed they were all exported from the same Excel spreadsheet. It looks perfectly well-formed to me (in NotePad).

Maybe I'm being particularly thick-headed this evening, but I can't see how any "length" parameter comes into this, or what there can possibly be that's less than zero. What is it that has a length parameter? The csv file is perfectly well-formed.

Can anyone please kindly give me a clue what might be wrong?

Caravelle

 

[mjcoon]

Here is the relevant code:

  OpenDialog1.Filter = "csv Files|*.csv"
  If OpenDialog1.Show <> cCancel Then
    tblImport.LoadCSV(OpenDialog1.File, ",", False, True)
  End If

But I notice that the error message shows the heading parameter as True. So perhaps if there was only one line in the file and that was a heading, or no lines at all, this would be an error. But I agree about the message text; it does seem strange!

Mike.

 

[Caravelle]

Well spotted, Mike, but that was just me trying out different variations on the parameters for .Loadcsv to see if it makes any difference (it doesn't).

There is something inspiring - I think that's the word - about posting here for help. Usually I can barely finish a sentence before thinking of a possible solution, which I then try out before carrying on. More often than not I find my own answer before I've finished, and delete the post. I think the process of setting out the problem formally clarifies things in the mind. Not this time, unfortunately, and my tinkering resulted in a mismatch. Must try harder next time!

Caravelle

 

[Caravelle]

Problem solved, though I'm not sure how the particular error message is explained.

I have discovered that when you save an Excel worksheet as a csv file, Excel does NOT put a comma in place to represent any empty cells at the end of the line row. I found the relevant lines in my csv file, added the required commas manually and then .LoadCSV worked just fine on the file. It seems to expect a fixed number of delimited columns in each row, and doesn't work if there are not enough commas.

I dislike Excel, but it has useful and time-saving features which make it useful for data entry on my desktop, for example auto-completion and the ability to type data into cells and drag it down to copy it into as many adjacent cells as you want - this saves me a whole lot of typing as my data can be quite repetitive at times, and it can help avoid typing errors too. I shall have to add a routine to count the commas in each row and add as many as are necessary at the end to reach the expected number.

Caravelle