Why won't word racer load?

Load word Table into MSSQL

• I have some word document file , and in this document I have several tables that I have to load into MSSQL , Is there any way to load or I have to Use any third party Software , Answer will be highly appreciated

• Answer:

  Hi, twims-ga: As I'm sure you have seen, the process of moving information from a Word document into a SQL Server database table must deal with the unrestricted types of data that might be present in a Word "table". My resume, for example, uses a Word table to provide formatting control, but it would be tough to "frame" that information as a nice relational database table. Therefore in addition to the mechanics of moving the data, I'd like to make some suggestions about "cleaning" the data as it moves. Typically a Word table will have few columns and many rows. So long as the row and column orientation of the table is to be preserved with respect to the SQL Server table, this works somewhat to our advantage in that there should be correspondingly few "datatypes" represented by those columns that may require conversion. To begin let me sketch out the recipe: 1) Start with a Word document containing a table and select/copy its contents. 2) Paste the information into an Excel spreadsheet and save the workbook. 3) Open Access and import the Excel spreadsheet into a new Access table. 4) After doing any final data conversions, export the Access table to SQL Server. Now the details! As our example I've chosen a data set mentioned in one of my recent answers, the relative frequencies of amino acids in vertebrate proteins: [Q221855: A problem on probabilities] http://answers.google.com/answers/main?cmd=threadview&id=221855 [Amino Acid Frequency] http://www.tiem.utk.edu/~harrell/webmodules/aminoacid.htm [Amino Acid Symbols] http://www.medicine.mcgill.ca/nephros/aasymbol.html I reformatted this relatively short table (there are only twenty amino acids) in a Word table of three columns: names of amino acid (Heading: Amino Acids) three letter abbreviations (Heading: Symbol) percentages of occurance (Heading: Observed Frequency in Vertebrates) This presents us with a fairly simple variety of datatypes to work with, a variable length character string (name), a fixed length character string (symbol), and a number expressed as a percentage. I then selected the contents of the Word table, copied these with Ctrl-C, opened Excel and pasted (into the default new spreadsheet) using Ctrl-V. Incidentally, here I'm using Office XP (Word 2002, Excel 2002, Access 2002) but the recipe followed is essentially the same as what I've used on previous versions (going back to Windows for Workgroups). It is not necessary to pre-select a group of cells in Excel of the exact size needed (3 by 21). Rather it is enough to have the focus in Excel on the upper left cell, and the pasting of the Word table's data then fills out the required number of rows and columns. I manually tweaked the width of the columns (and the depth of the first row), but this is was for aesthetics and is not mandatory. Now Excel has recognized the percentages in the final column as signifying a number, and the values are treated accordingly by Excel (although it formats them to two decimal places including a trailing zero). I saved the Excel spreadsheet as Word2SQL.xls and closed it. The Excel spreadsheet can now be used as a data source by Access. I opened Access and created a new database Word2SQL.mdb. Using the menu entry File > Get External Data > Import, I used the resulting dialog box, set for Files of type: Microsoft Excel (*.xls), to choose the previously save Word2SQL.xls. Clicking the Import button leads us to an Import Spreadsheet Wizard that displays either the spreadsheets or named ranges within the targetted workbook. In the immediate case our table is in the first spreadsheet, which happens to be the default selection in the dialog. Therefore in the first step of the Wizard we simply click the Next button. The second step of the Wizard has an optional checkbox titled "First Row Contains Column Headings" which (in our case) will work to our advantage. Using this option allows the Heading information to be treated as column names later. Again the checkbox is set by default in our case, so the second step of the Wizard also requires us merely to click the Next button. The third step asks whether we want the data to be saved in a new table, or in an existing one. We accept the default, which is to put it into a new table, and click Next. The fourth step asks us to confirm the names of column headings and the data types being used in each case. It also gives the opportunity to define indexes on columns. Note that the final column (Observed Frequencies in Vertebrates) is being converted as "double" numeric values. For the sake of simplicity I just accepted all the defaults (although on the next screen we will implicitly create an index on "Symbol") and clicked Next. On the fifth step we finally do something, namely pick Choose my own primary key: Symbol. Clicking Next takes us to the final step, where I replace the default table name (Sheet1 after the corresponding Excel worksheet) with AminoAcid. Click Finish. The conversion from Excel to Access produced a table AminoAcid with three columns and twenty rows, with two column headings that contain (as outlined above) embedded spaces. Some older versions of SQL Server did not allow column names to have embedded spaces, but this can be accomodated by SQL Server 7 and above. If necessary (or desirable) one can rename the columns in Access or perform data conversions before we take the next step of exporting to SQL Server. Where Excel may be a good place to do ad hoc adjustments (data cleanup), Access is probably the best place to do systematic transformations of data. Access allows us to do a lot in the way of conversion simply by changing the data type of a column. If strong machinery is needed, we can write a bit of code in Access VB for user-defined functions, and these can be employed with "Create table" and other sorts of Queries in Access. In short, Excel is a good place for initial touch-ups to data, but Access is probably better for handling any systematic problems. An exception to this scheme might be for numeric values that require some advanced mathematical treatment (e.g. converting percentages to standard deviations). To Export an Access table to SQL Server, use the File > Export... menu item (while the desired Access table is selected) and pick Save as Type: ODBC database (the last entry on my dropdown list). As it happens, I'd previously created a "File Data Source" DSN called TESTSQL.dsn that targets a database called "behave" on my local SQL Server 2000 instance. I just picked that one and clicked OK. If you don't have a suitable ODBC data source already to go, then use the New button on the dialog and a wizard will walk you through the steps of creating and verifying such a data source, then bring you back to this point so you can use it. Finally I used Query Analyzer to check over the results inside SQL Server. One minor and unavoidable glitch is that SQL Server stores the numeric values in full (binary) precision, so that if you look at them in Query Analyzer's result window, you are apt to see things like: Alanine Ala 7.3999999999999996E-2 where we originally had 7.4 % in the Word document table. These 999's are a result of the underlying binary arithmetic and do not indicate an error in the conversion process. If it were desirable to avoid this, a different datatype than "double" (namely decimal) could be used in Access (with Scale equal to three, specifying the number of positions to the right of the decimal place). After reviewing my suggestions and seeing how they may apply in your case, please use the Request for Answer Clarification feature to ask about any concerns that you may have. In the meantime I will zip up the various MS Office example files used above and find a place on the Internet where you can download them at your convenience. I'll post the link for this in a clarification below. regards, mathtalk-ga