CODEWITHSUNDEEP
parsingcsvcoldfusionformatting
TaylorPLM
TaylorPLM

Reputation: 101

Parsing a CSV for Database Insertion when Formatted Incorrectly

I recently wrote a mailing platform for one of our employees to use. The system runs great, scales great, and is fun to use. However, it is currently inoperable due to a bug that I can't figure out how to fix (fairly inexperienced developer).

The process goes something like this...

  1. Upload a CSV file to a specific FTP directory.
  2. Go to the import_mailing_list page.
  3. Choose a CSV file within the FTP directory.
  4. Name and describe what the list contains.
  5. Associate file headings with database columns.

Then, the back-end loops over each line of the file, associating the values with a heading, and importing these values into a database.

This all works wonderfully, except in a specific case, when a raw CSV is not correctly formatted. For example...

fname, lname, email
Bob, Schlumberger, [email protected]
Bobbette, Schlumberger
Another, Record, [email protected]

As you can see, there is a missing comma on line two. This would cause an error when attempting to pull "valArray[3]" (or valArray[2], in the case of every language but mine).

I am looking for the most efficient solution to keep this error from happening. Perhaps I should check the array length, and compare it to the index we're going to attempt to pull, before pulling it. But to do this for each and every value seems inefficient. Anybody have another idea?

Our stack is ColdFusion 8/9 and MySQL 5.1. This is why I refer to the array index as [3].

Upvotes: 1

Views: 134

Answers (2)

Jake Feasel
Jake Feasel

Reputation: 16945

I suppose if you are looking for another way of doing this (instead of checking the array length each time, although that really doesn't sound that bad to me), you could wrap each line insert attempt in a try/catch block. If it fails, then stuff the failed row in a buffer (including the line number and error message) that you could then display to the user after the batch has completed, so they could see each of the failed lines and why they failed. This has the advantages of 1) not having to explicitly check the array length each time and 2) catching other errors that you might not have anticipated beforehand (maybe a value is too long for your field, for example).

Upvotes: 1

Henry
Henry

Reputation: 32915

There's ArrayIsDefined(array, elementIndex), or ArrayLen(array)

seems inefficient?

You gotta code what you need to code, forget about inefficiency. Get it right before you get it fast (when needed).

Upvotes: 5

Related Questions