Data from Excel to SQL Server, Via Office Data bridging is a neglected cousin of IT. The common ancestor is "data processing." Somehow, data has been left behind in the information age.
January 23, 2009 By Dan Allen
William is a client. I am helping load data he collects from thousands of sources. Some of the data is in spreadsheets, some is in text files, some is on CDs. William has a lot of data. This is all about loading the records into a database system, where it can be manipulated easily. The key tricks are
Picking a database system that will do what you know you want done with the data, without requiring you spend months learning how it works. Pick one that doesn't fit the bill, and you are not going to get the data you want. How do you know ahead of time whether a particular database system will fit the bill? Write to me or post to me in the comment form or firing range, and I will answer on a case by case basis, unless I get more than 10,000 responses, in which case I will have to summarize via an article.
Putting the data into the database system. Funny that this is a trick, eh? Once the data is in its all-purpose canDoAnything database system, it is fun, easy, gratifying, astounding, etc. working with the data, as long as you are one of the many people who like working with data (includes all Excel enthusiasts). Unfortunately, the price of this near frictionless database world is you have to make the data stream into rows and columns, where you get at it with the wondrous database tools. You have to parse your starting data into the fields and tables you want in your target database. You can have all the parsing worked out, and still get stuck, because the data bridging does not read one of the cells in your gigantic dataset the way you expect. It just bombs, not even telling you which cell most of the time. It sux. If only you can get the data into the database system... this is data bridging. Data bridging continues to bring pain into the world, even now as we enter the post-embryo phase of the information technology boom. So, the trick is data bridging
You might think data bridging is what IT is all about, but it turns out that data bridging is limited in areas where most people would assume there are just programs for that.
The amazing thing is technology seems to have moved on to other things, like making movies on a web screen, and blown off the work of making data bridging as good as people would expect.
---------- Forwarded message ----------
From: Dan Allen
Date: Fri, Jan 23, 2009 at 7:39 PM
Subject: Instead of EE this time...
To: William Jackson
... William,
As I started posting into EE (experts-exchange.com), I realized, that your trial edition of Access Office2007 is a great solution to loading the cheese data. If you can open the xlsx file in Access Office, or better, import it to Access Office2007. From there, you can put out a text file with a delimiter understandable by SQL Server 2000.
An advantage of SQL Server 2008 (2005 has become 2008 in the SQL Server world) is it will tie directly to xlsx files. It's fortunate that you have Access Office 2007, because it is a useful component for general purpose data bridging, because of its almost universal adaptability to MS data... I say almost, because some of the exceptions are astonishing, but I think you will find Access Office 2007 quite awesome and almost even SQL Server like.
I still say, if you can deal with Access Office , you will LOVE sql server 2000. It is everything Access Office tries to be, except it never glitches, and the tools for working with the data are the absolute best available. For comparison, you can look at a product called ERWIN, which made a fortune providing tools like the ones in SQL Server, except.. this is extremely huge, probably the most important thing I can tell you about database technology... SQL Server 2000 comes with tools like ERWINS, with the gigantic advantage that the tools tie directly to your database, instead of just giving you specs for making your database on another platform. With SQL Server, you can talk to the DB with pictures, and it translates it into the necessary SQL much better than any other DB. Then on top of that, the core DB engine is perfect and fast. It is what computers were made to do, without the usual bugs. I know you will come to see what I mean. The trick is recognizing the database and the tools are NOT one and the same. The tools talk to the database. The database understands SQL and nothing else. Very important to remember this.
The reason databridging still is such a big pain in the butt is that the bridges into the database have not been developed adequately. This was an unbelievable disappointment, when SQL Server 2005 came out. One of the proclaimed advantages of the 2005 edition was its supposedly new architecture for the data briding components. They said they were changing DTS (Data Transformation Services), the old system, to SSIS (SQL Server Integration Services), the new system. When it came out, it was as if they had changed the body on a car without altering the chassis, suspension, engine or transmission, but calling it a new architecture. It was the old system with a new name.
In time, when you get a chance to upgrade to SQL Server 2005/8, you will see all of this for yourself, and be amazed at how complicated something as simple as "data bridging" is, and how this is an area of technology that is underdeveloped.
Here is a discussion of the topic of "ETL - Extract Transform Load" technology. Data bridging is my term for ETL. I think part of the problem is people think "ETL" instead of "data bridging". "ETL" puts you on intellectual heals before you cross the start line. http://en.wikipedia.org/wiki/Extract,_transform,_load
My recommendation is you use Access Office 2007 as your ETL/data bridging technology, to make text files for export to SQL Server 2000. By the time your trials run out, you will be extremely good with this, I guranatee you. William Jackson, data animal, my hero.
Can't get it to load on Access Office but the 65K records loads ok on excel
2 messages
William Jackson
Fri, Jan 23, 2009 at 9:44 PM
To: Dan Allen
Can't get it to load on Access Office but the 65K records loads ok on excel. The installation fu Outlook 2003. Will try to do a uninstall reinstall agn. Using large notebook because I had a trial on Emachine BIG COMPUTER!
Dan Allen
Fri, Jan 23, 2009 at 9:53 PM
To: William Jackson
The installation fu Outlook 2003.
My sympathies.
Excel should load to Access Office. If it doesn't... oh my, I can picture it now. If we saw that file in Excel, it would have maybe multiple spreadsheets in the workbook, all of them dumped together maybe into that text file, one after the other.
Also, there could be lines at the top of one or more spreadsheets with titles and such, with the data columns starting in like row 20 or something.
Oh yea, then someone complained to MS, because their macros were being lost when they exported their spreadsheet to text, so MS tried helping them out by including macros as binary objects in text files. I just made that one up, but it is not much of a stretch.
William, this is not your fault. This is where technology sux.
This is exactly what I was writing about tonight in my data bridging article.
New Host Next Steps Rather than relying on email for a task list that is changing all the time, I am keeping the next steps and status on this page. By Dan Allen
Slow Is Fast Plugging Into Bedrock Even though immediate objectives are in sight, still necessary to delay speed for infrastructure development. In this case, infrastructure is spelled V-T-D-i-g-g-e-r. By Dan Allen
What is AJAX Programming? AJAX is a special program for updating parts of a webpage. The benefit is that tiny parts of a web page can display changes without re-displaying the parts of the page that have not changed. Re-display forces the internet to carry mountains of data it already has delivered. AJAX lets the internet say: "Update this one part of the webpage, and leave everything else still."
By Dan Allen
Ferguson and the Montpelier 650 Stephen Ferguson pays the freight, but is not allowed to drive. (Indy News Service) Stephen Ferguson grew up near the speedway. Even though he practically owns the team, team captain, Jimmy Glass, will not let him strap-on the car for a ride. By Tim Twinam, Montpelier Star
Data Bridging Bridges only go in difficult spots, whether the bridge is for G1 Endy racing or data streams. In this Montpelier Telecommunications Super Speedway photo, data bridging is set in place during a driver rest period (August 2008). In the Boston area, data bridge work like this takes place under lights at night, to avoid disrupting traffic. Montpelier Speedway road crews have the relative luxury of working in the sunlight, kept cool by Green Mountain breezes.
Moving data from one kind of software or database to another is difficult. Paste this web page, or even just a part of it, into Word, email, Excel, or almost any other program, and most likely what you get will be wild distortions of what you are looking at now. By Dan Allen
Basic Racing Skills FTP File Transfer Protocol (FTP) is our friend in web racing. By Dan Allen
Vermont Electric Running Super High Speed Broadband Over the next three years, Vermont's electric companies, through Velco and Vermont Transco LLC are installing super high bandwidth backbone for public use.
