Updating existing Pervasive Database entries from an Excel sheet

We have a pervasive database with all of our inventory order, reorder, and quantity ect.

I have an excel sheet with updated quantities of these 9,500 parts. Is there any programs that anyone is aware of that will take and generate "SQL Scripts" in this case UPDATE scripts based on this information, or is there a way to script it myself?
Who is Participating?
Bill BachPresidentCommented:
With ANY SQL database, you need to build a number of UPDATE queries to do this.  However, there are a few options:
1) You can export the data from Excel to a comma-delimited file, then automatically create an UPDATE query for each row by using some simple text processing tools to convert the comma-delimited output file into a series of UPDATE statements.  At http://www.goldstarsoftware.com/download.asp#DOSUtils, you can find a free DOS tool called MAKEBAT which will take an input file, prepend a text string to each line, append a text string to each line, and output the results.  This can get you started.  However, this would be an ideal solution only for an INSERT statement, where you can put the data inside a VALUES clause.  With UPDATE statements, you must handle each field separately, so it'll be a lot harder to do.  Instead, you'll probably need to write a simple text parser that parses the data and builds your UPDATE statement for you one line at a time.

2) On the other hand, if you're not a developer, then using an ETL tool would make a lot more sense.  There are applications known as Extract/Transform/Load tools, such as Pervasive's Data Integrator, that can read the comma-delimited file (or, even easier, read the Excel file directly) and update the necessary fields in the PSQL database for you.  This would be a FAR simpler and MUCH faster solution.  Although it's a bit pricey for a one-time deal, if you have to do this time and time again, and if you have other data integration needs for your business, then it will more than pay for itself.

There really is no comparison to these two approaches.  I can do what you want in about 15 minutes using Data Integrator, and have something that is easy to maintain, debug, and modify.  It can even be extended to handle exceptions (reporting them to an error file, or even doing an INSERT instead if that makes sense) and a lot of other special features.  The only catch -- you gotta buy the software up front.

tmroenickeAuthor Commented:
Thank you! This answered my question and then some.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.