0

I create page to import records so users can import records using a CSV file.

I am already using Sanitization to remove unexpected characters for specific field of the record, Do I have to validate each record in csv file before insert it into Database then display the errors to user or just using sanitization only cause user will follow the format if format fail just remove it using sanitization method ?

  • I always think of it this way. Even if there is only a one in a million chance that the data somehow gets corrupted, a computer can do an operation a million times quite quickly. So one in a million will happen very often. – Dunk Aug 31 '16 at 16:18
1

If you performed a partial load of the data due to data errors, there may be problems resubmitting the data unless they were keys to stop the same data being loaded again.

I'd be inclined to parse the data first and offer the user an option as to whether they want to upload a clean subset of the data or correct the various errors themselves and try again. Whether you want to provide some sort of wizard here is largely up to you and depends on the sort of data you're processing. You'd need considerable resources to try and auto-correct things like name & address etc.

Modifying the data as it is uploaded can be contentious unless there is an understood file definition. You don't state the sort of data you're loading but taking a simple example such as gender - this can yield a surprising number of values: M (male), F (female), U (unknown), N (not supplied), D (derived), blank etc.

  • for selection value if user fill it with incorrect format, I will set it to default value – Joko Wandiro Aug 30 '16 at 9:48
  • I just want to know how to handle it correctly. Do I have to validate each record before insert it into database if there's record with incorrect value then display error to user to modify it according to the format. Do the validation have to be implemented same if user fill data for one record in website form – Joko Wandiro Aug 30 '16 at 9:52
  • The validation would be the same as if they entered the data on the website however, drop-downs and the like would limit what they can enter. However, if the number of records to be input is large, then a CSV may be the better option. – Robbie Dee Aug 30 '16 at 10:18
  • Do I have to implement validation or just using sanitization only. I send notification to user while there's duplicate entry so user can fix the problem. Do I have to implement validation so user can fix the problem if data is not valid or according to the format ? It's BackEnd Application which just authenticated user can access in Application. User will be employee which follow existing rule or format, if there's typos, sanitization will handle it with removing unexpected characters. – Joko Wandiro Aug 30 '16 at 10:25
  • It might be easier if you added the sort of data you're trying to parse to the question. Whatever you call it, you want clean data on the database. This may consist of: dropping data that can't be cleansed, deriving data from other columns, tidying presentation of some data e.g. salutation: Mr, Mrs, Ms, Miss etc, making sure other data is within reasonable bounds e.g. age etc etc etc. – Robbie Dee Aug 30 '16 at 10:48
1

Do I have to validate each record in csv file before insert it into Database ...

Yes. Absolutely. No question.

... then display the errors to user or just using sanitization only cause user will follow the format if format fail just remove it using sanitization method ?

Unclear.

When you take data from the users via a screen, you validate that data before saving it. Just because that [same?] data is coming from a file, you still have to validate it, according to the same rules.

The problem is how to tell the User about anything that "goes wrong" in the process.

You could treat the entire file as one transaction and reject the whole lot if anything goes wrong with any record. Then you have to direct the user to the errant field[s] in the errant record[s] so that they can fix it/them. That might be in the form of a report or, perhaps, a screen; if you use the latter, then you might also have the option to assist the user in correcting the problem. YMMV.

Alternatively, you could treat each record as "atomic"; if the file contains 100 rows and only row #5 is a problem, then you can load all the others and just "send the user back" row #5, possibly in a separate, "rejected" file.

If your input file is "compatible" with your Users' favourite spreadsheet program (i.e. said program doesn't do "helpful" but "stupid" things to your data like, say, stripping all the [significant] leading zeroes from your "numeric" fields) then you might have the option to "return" errors as additional columns in the CSV file. That makes them easier for the Users to find and, therefore, to correct. Again, do you given them the whole file back with accepted rows suitably "marked", or just the rejected rows?

Perhaps it might be useful to have a discussion with the Users to see which they might prefer (and, indeed, are capable of dealing with).

  • Yeah the problem is how to tell user which record in file is wrong or not valid. Give user notification Ex: name is required for record x. Do this good programming practical to handle import record from file ? – Joko Wandiro Aug 31 '16 at 3:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.