[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Updating existing Pervasive Database entries from an Excel sheet

Posted on 2007-10-05
Medium Priority
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 29

Accepted Solution

Bill Bach earned 2000 total points
ID: 20021636
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.


Author Comment

ID: 20024025
Thank you! This answered my question and then some.

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

872 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