Uploading data and creating updateable tables

[Ted Giles]

The following is what I am trying to achieve.

Process. Creating and Uploading data from CSV

User creates a CSV
The first field will be the PK. Assume that the ID Field is Unique.

Table Fields in Data in example;
ID,Test Data

Process Within InDe

Import data.
From a button,
Import data to a new SQL table on a server from a selectable location on a PC
Rename the table to the name expected by the InDe IDE
Set ID as PK

Issues

The ID field may not be unique but this is a user issue.
127,000 records to be uploaded each time.
Each upload will delete the existing table and recreate a new one - see import above.
The records can be changed after upload.

[ljwilson]

Ted,

Please see if the attached example does what you want. It is based on the "Manual Uploads" form from my Acme Invoice Example (posted in another thread in this forum: https://forum.instantdeveloper.com/viewtopic.php?f=7&t=83233&p=145954)

You will have to change the db connection settings to match your environment.

Notes:

1. Three sample .csv files are included.
2. First go to the Upload Form. You can then drag and drop or use the upload button to select a file to upload.
3. The file is processed one line at a time in the ProcessFile function.
4. The existing data in the BasicData table is deleted first.
5. The uploaded file is saved in the uploads table (in a blob field).
6. Errors (such as duplicate id's) are ignored, and the last error (if there is one) is shown when done.
7. The finished data can be seen in the Basic Data Form.
8. I created a 130,000 record test file (which is too big to upload here). It took just under 2 minutes to process with the SQL server local to the application.
9. If you test importing in a large file, I recommend you compile your app with debug off to save time. Otherwise it could take more like 12 minutes to import in a 130K record file, even with the debug limiting statements I have set in the ProcessFile function.
10. If the user wants to add their own record to the Basic Data form, upon save it will create a new guid for the id automatically using the MyDocumentHelper Class.
11. To display more than 500 rows I use the MAXROWS property for panels, but apply it to all panels using the Global Load function.

CSV Import Example Express 13.5.zip

CVS Import Example InDe 13.5 Express

(464.51 KiB) Downloaded 220 times

[Ted Giles]

Thanks Jack, I have it downloaded and will see if I can follow your approach and learn something!

Edit.
Have now changed the SQL source and it is working perfectly.
I have tried to follow the logic so I will use DeBug to watch the process flow.
Given the complexity of the operation, I am not surprised to have been stumped initially, but it does the job so I will do my best to understand it all.