Updating existing Pervasive Database entries from an Excel sheet

Posted on 2007-10-05
Last Modified: 2012-08-14
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?
Question by:tmroenicke
    LVL 28

    Accepted Solution

    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, 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.


    Author Comment

    Thank you! This answered my question and then some.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This article describes some very basic things about SQL Server filegroups.
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now