 |
 |
|
|
|
|
 |
|
|
Samples and other shared info.
| You are not authorized to post a reply.
|
|
| Author |
Messages |
|
Michelle
Posts:43
 |
| 10/24/2007 10:04 AM |
 Alert
|
| Sorry, wrong information. It doesn't work without dot. If '[frmFile.Extension,Form]' = '.csv' is correct. |
|
Michelle Chan |
|
|
AlexK
Posts:123
|
| 01/09/2008 8:38 AM |
Alert
|
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. |
|
|
|
|
pauldes
Posts:1392
|
| 01/09/2008 9:06 AM |
Alert
|
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 |
|
ListX....makes you look brilliant, even though you're not. |
|
|
AlexK
Posts:123
|
| 01/09/2008 9:13 AM |
Alert
|
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. |
|
|
|
|
pauldes
Posts:1392
|
| 01/09/2008 9:22 AM |
Alert
|
| It's wasn't the first time around, trust me, it took me forever.....Good Luck <img src='http://dnn.bi4ce.com/desktopmodules/ntforums/images/emoticons/smile.gif' height='20' width='20' border='0' title='Smile' align='absmiddle'> |
|
ListX....makes you look brilliant, even though you're not. |
|
|
AlexK
Posts:123
|
| 01/15/2008 4:22 AM |
Alert
|
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. |
|
|
|
|
pauldes
Posts:1392
|
| 01/16/2008 8:56 AM |
Alert
|
| 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. |
|
ListX....makes you look brilliant, even though you're not. |
|
|
AlexK
Posts:123
|
| 01/16/2008 9:08 AM |
Alert
|
| Thanks pauldes! Let's hope the developers notice this discussion. |
|
|
|
|
bgates
Posts:197
|
| 01/16/2008 10:06 AM |
Alert
|
| 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). |
|
Bob Gates
Business Intelligence Force, Inc. (bi4ce) |
|
|
AlexK
Posts:123
|
| 01/17/2008 3:20 AM |
Alert
|
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. |
|
|
|
|
|
| You are not authorized to post a reply. |
|
|
|
ActiveForums 3.6
|
|
| Latest Post
|
 |
|
At R2integrated (formerly Bi4ce), we take support seriously. That's why we support our customers and DNN community with daily monitoring from our experienced engineering team. We ask that the first step taken is to read the relevant documentation and support forums prior to submitting any questions that may already be available or have been answered. We ask that you review the documentation that we provide for our products before posting a question.
The Forums are for our customers to chat, exchange ideas and strategies, and submit feedback. Please be sure to perform keyword searches for previous related forum responses.
To be helpful when submitting a new item, please include the following:
- DNN Version
- Module Version
- Admin Log Viewer Information
- Environment detail: Operating system, .NET framework version, database and version, IIS version, Browser version (if appropriate)
We always try to respond quickly and monitor the forums daily during business hours (EST). Occasionally, requests for a specific project requirement may not apply for the free support offered. For project specific support please submit via our Information Request form.
Thank you for using our Forums.
Click here to register for the Forums
|
|
|
|
| © 2008 by R2integrated (formerly Bi4ce) | DNN® is a registered trademark of DotNetNuke Corporation
|
 |
|
|
|
 |
|