{"id":24148,"date":"2020-10-24T15:46:46","date_gmt":"2020-10-24T08:46:46","guid":{"rendered":"https:\/\/tom.ji42.com\/?p=24148"},"modified":"2020-10-24T15:46:46","modified_gmt":"2020-10-24T08:46:46","slug":"extracting-structure-indexes-from-a-foxpro-database-to-excel","status":"publish","type":"post","link":"https:\/\/tom.tomwork.net\/?p=24148","title":{"rendered":"Extracting Structure\/Indexes from a FoxPro Database to Excel"},"content":{"rendered":"<p>Alternatively if you want to pursue the Excel route manually then &#8230;<\/p>\n<p>If you have a table MYTABLE.DBF with the following structure:<\/p>\n<pre class=\"lang-sql s-code-block hljs\"><code>Structure for table:          C:\\TEMP\\MYTABLE.DBF\r\nNumber of data records:       0       \r\nDate of last <span class=\"hljs-keyword\">update<\/span>:          <span class=\"hljs-number\">01<\/span>\/<span class=\"hljs-number\">05<\/span>\/<span class=\"hljs-number\">2014<\/span>\r\nCode Page:                    <span class=\"hljs-number\">1252<\/span>    \r\n <span class=\"hljs-keyword\">Field<\/span>  <span class=\"hljs-keyword\">Field<\/span> <span class=\"hljs-keyword\">Name<\/span>            <span class=\"hljs-keyword\">Type<\/span>                        Width      <span class=\"hljs-built_in\">Dec<\/span>    <span class=\"hljs-keyword\">Index<\/span>   <span class=\"hljs-keyword\">Collate<\/span>     <span class=\"hljs-keyword\">Nulls<\/span>       <span class=\"hljs-keyword\">Next<\/span>       Step\r\n     <span class=\"hljs-number\">1<\/span>  FIRSTNAME             <span class=\"hljs-built_in\">Character<\/span>                      <span class=\"hljs-number\">20<\/span>                                    <span class=\"hljs-keyword\">No<\/span>\r\n     <span class=\"hljs-number\">2<\/span>  LASTNAME              <span class=\"hljs-built_in\">Character<\/span>                      <span class=\"hljs-number\">20<\/span>                                    <span class=\"hljs-keyword\">No<\/span>\r\n     <span class=\"hljs-number\">3<\/span>  AGE                   <span class=\"hljs-built_in\">Numeric<\/span>                         <span class=\"hljs-number\">3<\/span>                                    <span class=\"hljs-keyword\">No<\/span>\r\n     <span class=\"hljs-number\">4<\/span>  <span class=\"hljs-keyword\">ID<\/span>                    <span class=\"hljs-built_in\">Integer<\/span> (AutoInc)               <span class=\"hljs-number\">4<\/span>               <span class=\"hljs-keyword\">Asc<\/span>   Machine        <span class=\"hljs-keyword\">No<\/span>          <span class=\"hljs-number\">1<\/span>          <span class=\"hljs-number\">1<\/span>\r\n** Total **                                                  <span class=\"hljs-number\">48<\/span>\r\n<\/code><\/pre>\n<p>Then you can dump the structure to another DBF via the VFP Command Window like this:<!--more--><\/p>\n<pre class=\"lang-sql s-code-block hljs\"><code>cd \\temp\r\n<span class=\"hljs-keyword\">use<\/span> mytable\r\ncopy structure <span class=\"hljs-keyword\">extended<\/span> <span class=\"hljs-keyword\">to<\/span> mytablestruct.dbf\r\n<\/code><\/pre>\n<p>You can then open the table that contains structure info and dump it to XLS format:<\/p>\n<pre class=\"lang-sql s-code-block hljs\"><code><span class=\"hljs-keyword\">use<\/span> mytablestruct\r\ncopy <span class=\"hljs-keyword\">to<\/span> struct.xls <span class=\"hljs-keyword\">type<\/span> xl5\r\n<\/code><\/pre>\n<p>In Excel that will look like:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/i.stack.imgur.com\/8YOFR.png\" alt=\"enter image description here\" \/><\/p>\n<p>With regard to indexes you would have to code a small routine like this:<\/p>\n<pre class=\"lang-sql s-code-block hljs\"><code>    <span class=\"hljs-keyword\">Create<\/span> <span class=\"hljs-keyword\">Cursor<\/span> indexinfo (idxname C(<span class=\"hljs-number\">254<\/span>), idxtype c(<span class=\"hljs-number\">254<\/span>), idxkey c(<span class=\"hljs-number\">254<\/span>), ;\r\n        idxfilter c(254), idxorder c(254), idxcoll c(254))\r\n    <span class=\"hljs-keyword\">Use<\/span> mytable <span class=\"hljs-keyword\">In<\/span> <span class=\"hljs-number\">0<\/span>\r\n    <span class=\"hljs-keyword\">Select<\/span> mytable\r\n    lnTags = ATagInfo(laTemp)\r\n    <span class=\"hljs-keyword\">For<\/span> i = <span class=\"hljs-number\">1<\/span> <span class=\"hljs-keyword\">to<\/span> lnTags\r\n        <span class=\"hljs-keyword\">Insert<\/span> <span class=\"hljs-keyword\">into<\/span> indexinfo <span class=\"hljs-keyword\">values<\/span> (laTemp[i, <span class=\"hljs-number\">1<\/span>], laTemp[i, <span class=\"hljs-number\">2<\/span>], laTemp[i, <span class=\"hljs-number\">3<\/span>], laTemp[i, <span class=\"hljs-number\">4<\/span>], laTemp[i, <span class=\"hljs-number\">5<\/span>], laTemp[i, <span class=\"hljs-number\">6<\/span>])\r\n    EndFor\r\n    <span class=\"hljs-keyword\">Select<\/span> indexinfo\r\n    Copy <span class=\"hljs-keyword\">To<\/span> indexinfo.xls <span class=\"hljs-keyword\">type<\/span> xl5\r\n<\/code><\/pre>\n<p>Opening the resultant indexinfo.xls:<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Alternatively if you want to pursue the Excel route manually then &#8230; If you have a table MYTABLE.DBF with the following structure: Structure for table: C:\\TEMP\\MYTABLE.DBF Number of data records: 0 Date of last update: 01\/05\/2014 Code Page: 1252 Field Field Name Type Width Dec Index Collate Nulls Next Step 1 FIRSTNAME Character 20 No [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[13],"tags":[],"class_list":["post-24148","post","type-post","status-publish","format-standard","hentry","category-13"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p6cOVM-6hu","_links":{"self":[{"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=\/wp\/v2\/posts\/24148","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=24148"}],"version-history":[{"count":1,"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=\/wp\/v2\/posts\/24148\/revisions"}],"predecessor-version":[{"id":24149,"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=\/wp\/v2\/posts\/24148\/revisions\/24149"}],"wp:attachment":[{"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=24148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=24148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tom.tomwork.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=24148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}