Update Table from CSV (by
Felice Di Stefano)
(for PHP MySQL, PHP ADODB, ASP Vbs, ASP Js)
This server behavior will allow to update a database table from a CSV file with .csv or .txt extension. The file doesn't require to have all the columns of the chosen table but the user may define which columns to update, will not matter if the file has more columns than the user defined columns these will be skipped, it matters that the files has the defined columns, if less than these are found the file is declared invalid, for instance if are chosen these columns from the table (FirstName, LastName, Email) when these columns are found the file is valid. The application assumes that in the first row are column headings.
Applying
You need to have a form on the page and the file field for the file import and of course a button to submit the form, than you can open the Update Table from CSV server behavior from the FX Import-Export CSV submenu in the server behaviors palette and apply it.
| Form: | Select the form on the page will perform the operation. |
| Connection: | Select the connection is already created which will communicate with the database where to update the data. If doesn't exist create it. |
| Update Table: | Choose from the dropdown the table to be updated. |
| Columns: | Select all the table columns in which you want to update data. The file must contain at least the selected columns with relative heading otherwise is invalid for example if Email is selected the name must be matched case insensitive. An error message will display if not all the selected columns are found. Doesn't require that the order of the columns in the file respects the order of the columns in the table. |
| Delimiter: | Define here the expected delimiter which may be a char or a combination of chars, for a tab delimiter use \t. If the delimiter is not found on the first row at least one time the file is invalid and an error message will display. |
| Line Length: | This will be valid and enabled only for PHP and must be enough to read a line including line breaks. So calculate the data can contain the database depending on definitions. |
| Where Column: | Select here the table column must be matched in order the update to happen. Usually a column containing a unique value like an ID but it is up to you how to manage this option. Remind that when the value is matched in multiple rows multiple rows will be updated. |
| File Field Name: | Select the file field from the dropdown which is used for the update. |
| Submit Button Name: | Select from the dropdown the button in the form which will submit the update. |
| Invalid File Msg: | Define an error message to display when a file is declared invalid. Reasons for invalid file may be: the file is not a CSV with extension .csv or .txt, the delimiter is not matched or the number of columns in the file are less than the number of selected columns. |
| Missing Cols Msg: | Define an error message to display when not all the selected columns are matched. |
| Redirect To: | Define here if you wish a page to redirect to if the update operation was successful. |
Note:
The definition of columns is recognized automatically from the database but
the data in the file must match the column definitions otherwise a SQL native
message will display, if for example a column in the database expects a numeric
value but the files contains a string value a message appears indicating the
row and that row is skipped but all the correct rows will be updated.
As mentioned the order of columns in the file different from the order in the
table columns is not relevant.
Advanced: When the update happens successfully the application creates the "csvdone" variable which can be used to trigger a subsequent action like sending a confirmation email for example. Therefore if you need to have something like that make your code check for the existence of this variable.
by FELIXONE