Sorry, wrong information. It doesn't work without dot. If '[frmFile.Extension,Form]' = '.csv' is correct.

Hello all,

I'm reviving this thread with another question. We can validate a file for its type by the extension. But how can we validate the content before importing it into the database?

Suppose there is an upload with some 500 lines. Each line will be imported into the database. But what if some field contains wrond data, say text instead numbers, or the text is too long?

Of course, one could constrain the excel file input fields. But what if people don't use Excel and make upload files by hand? Or what if there is some other way to avoid that constraint? Can each field be checked using conditions? Is that possible?

No one wants to blindly upload bigger files unchecked. How would I implement such a check?

Best regards,
- Alex.

When you are doing your mapping, you can test your target column in the destination SQL.

For example if you had a column whose target was @Description, you could write the destination SQL as
[FORMAT,@Description,{LEFT:100}] if your description column in the DB was a varchar(100)
or
CASE WHEN [FORMAT,@Date,{ISDATE}] = True THEN @Date ELSE NULL END
to avoid the row being skipped if the date column is not a valid date

Also, if you don't do any validation and you just want to report errors/results, you can put a name in the File action like MyImport then test the following action variables for values:
[MyImport.isSuccessful,Action] - True/False
[MyImport.Status,Action] - Total rows
[MyImport.Errors,Action] - Total rows not imported

Wow, pauldes, you make everything look so easy ;-) I'm on my way with your hints! Thank you very much, once again!

Best regards,
- Alex.

It's wasn't the first time around, trust me, it took me forever.....Good Luck <img src='desktopmodules/ntforums/images/emoticons/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'>

Hi all,

Just a little additional question regarding this subject. Is it possible to use the SQL column mapping for variable mapping, so that values from the file can be used in the module? For instance for reporting what row numbers have incorrect values and thus can not be imported. I am probably avidly missing something obvious.

Or is it better to create a temporary SQL view, poor the file contents in there and see which data actually is imported, after which an OK or NOT OK by the user can be issued, resulting in the real import (or not) of the data by copying it from the temporary table to the actual table?

If so, it would be very useful to be able to state the row numbers or the ID columns from the uploaded file that have unimportable data. Can this be done somehow?

Best regards,
- Alex.

yeah, I know what you are talking about. I've asked about this before. BI4CE would have to let us know if this is in the product yet.

Thanks pauldes! Let's hope the developers notice this discussion.

Currently, ListX only supports a status flag - success or failure. If you need more detailed error reporting, then your idea of dumping everything into a separate table works just fine. I'd recommend that table have an IDENTITY(1,1) field called RowNumber and then set every field as a VARCHAR, so that any data type is supported. You'll then have to run a routine that returns RowNumber for any row that has invalid data (be sure and check for NULLs where NULL is not allowed too).

That is valuable information bgates, thanks! I'm glad I was thinking along the correct lines somehow. Let's see what I can come up with using your info. Thanks again!

Regards,
- Alex.