Uploading a CSV to recreate a SQL table

[Ted Giles]

A potential client I am working with has asked for a process which I currently do using Toad for SQL Server.
The process is simple, as it Drops the existing Table and Recreates the new Table using the CSV they have supplied.

What they need is to be able to log into the application as Administrator and launch an upload process which takes the CSV from their PC and recreates the new Table.
Is there a way of getting the CSV onto a server through InDe and then recreating the new Table?

It may not be possible to access their PC dynamically, so what about Drop Box as the source of the new CSV?

Any ideas?

[ljwilson]

Look at my acme example, Developer - Manual Upload.

That demonstrated dragging a csv file to the blob field and then having it saved in a table and processed.

As to changing schema, would probably have to translate the csv file to the corresponding sql to drop/create the table.

...jack

[john w]

Ted- I have a process where I take a csv file from a 3rd party, upload it and then convert it to a JSON string. I manipulate the data, changing values through various calculations , and then pass it back to the user as a new csv string.

Given that experience you should be able to so what you want. It should not be hard but may get a bit tedious depending on the amount of csv data fields you have. You probably could create a stored proc in SQL to handle a large part of what you want to do.

[Ted Giles]

Thanks John.
I will think on that.
It will be easier for them to upload a CSV directly to the SQLServer and actually recreate the Table as the format is consistent.
Perhaps a SQL script will do that and can be run from the PC. That way, the Schema says the same.

[ljwilson]

Once the table is created, you can simply empty it before the csv upload. You can do that inside your inde app with a simple "delete from table".

If you need to initially create the table inside your inde app, then just store the sql which creates it as a string and run it using SQLExecute.

...jack

[Ted Giles]

Not sure I quite understand, Jack.
Are you suggesting that the Blob field can be imported into a Table using a button on the app?
The data set is 10 gig and rising.
Would that be a problem?

I will revisit Acme and see if I can work it out.

[ljwilson]

Yes, but for that large of a data-set I would rethink the process.

Is the client's computer and server in the same (fast) local area network? If so then uploading the file to the server might be ok through the web interface, but loading 10GB of data from the file on the server to the database is going to be slow unless you do a bulk insert at the server level.

If server and client are not local to each other, then I would lean toward doing an ftp or sftp transfer of the file from the client computer to the server, then have the server do a bulk insert.

https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15

Is all of this data new every time, or is it really just some new records at the end and/or only a few changed/deleted records? If the majority is static, then look at doing a diff of the original load file with the new one, where you create 3 update files--one for new, one for deletions, and one for updating existing ones. Obviously this is a lot more complicated coding-wise. Not worth it probably if the data load is only once a month, but if every day would make sense to pursue.

Another thought if the file is fairly static is to use rsync or equivalent (DeltaCopy is a free windows wrapper around rsync: http://www.aboutmyip.com/AboutMyXApp/DeltaCopy.jsp) to get the file changes uploaded, then drop the data and do another bulk insert. That would be the fastest and least coding. I've used DeltaCopy for years--you can use the Windows task scheduler to send the file daily or hourly or whenever from one computer to another.

...jack

[Ted Giles]

Thanks Jack.
I am unclear as to how often the updat/new data is being loaded at the moment.
Will get back on that.
When I connect to the Db using Toad, the upload takes 3 or 4 minutes.