DATA Step, Macro, Functions and more

Reading ADODB files

Reply
New Contributor
Posts: 2

Reading ADODB files

Lately I have encounterd files that contains ADODB information where the column headers are described in the start of the dataset. So far I havnt been able to read them with out having to write an explicit program to read the files.

But I guess that it must be a samrter and more dynamic way to read these files.

 

All inputs are apreciated

 

<trv_possessions mapp="Bapmapp T18" period="T18.1" layer="3" type="BAP" description="Trafikverket Fastställda Banarbete T18" week_from="201749" week_to="201849" timestamp="2017-09-21 22:17:08" userid="Jonas" session="TRVVDI-40187::s001of" version="TrainPlan 3.11.2.2" format="internal">
 <vity.recordset.possessions records="3" type="adodb.recordset">
  <fields>
   <FOLDERREF type="131" size="5"/>
   <POSSESSION_REF type="131" size="10"/>
   <LAYER type="131" size="5"/>
   <PLANNING_REGION type="129" size="1"/>
   <POSSESSION_NUMBER type="131" size="10"/>
   <CREATE_DATE type="135" size="16"/>
   <CREATE_USER type="200" size="6"/>
   <MOD_DATE type="135" size="16"/>
   <MOD_USER type="200" size="6"/>
   <PARENT_POSSESSION type="200" size="8"/>
   <STATION_BANK type="200" size="8"/>
   <IS_BANK_REVERSED type="129" size="1"/>
   <DESCRIPTION type="200" size="100"/>
   <FROM_LOCATION type="200" size="8"/>
   <IS_FROM_WORKED_AT type="129" size="1"/>
   <TO_LOCATION type="200" size="8"/>
   <IS_TO_WORKED_AT type="129" size="1"/>
   <FROM_WEEK type="131" size="6"/>
   <TO_WEEK type="131" size="6"/>
   <OBJECT_OWNER type="200" size="8"/>
   <CLASSIFICATION type="200" size="20"/>
   <IS_PLANNING_MEETING_NEEDED type="129" size="1"/>
   <IS_SINGLE_TRACK_OPERATION type="129" size="1"/>
   <IS_TRANSPORT_NEEDED type="129" size="1"/>
   <MISCELLANEOUS_NOTES type="200" size="1000"/>
   <STATUS type="131" size="5"/>
   <REVISION_NO type="131" size="5"/>
   <INFRASTRUCTURE_AREA type="200" size="8"/>
   <IS_DEVIATION type="129" size="1"/>
   <OBJECT_MANAGER type="200" size="100"/>
   <OPERATOR_ID type="200" size="8"/>
   <REJECTION_COMMENT type="200" size="200"/>
   <IS_MANNING_REQUIRED type="129" size="1"/>
   <ORIG_OFFSET type="131" size="5"/>
   <DEST_OFFSET type="131" size="5"/>
   <CONTRACTOR_ID type="200" size="8"/>
   <MAX_DETAIL_ID type="131" size="5"/>
   <PROPOSAL_NO type="131" size="5"/>
   <PLANNING_COST type="131" size="12"/>
   <APPLIED_PRODUCTION_COST type="131" size="12"/>
   <INTERNAL_NOTES type="200" size="1000"/>
   <WORKFLOW_STATUS type="131" size="3"/>
   <CHANGE_STATUS type="131" size="1"/>
   <EXCLUDE_FROM_GRAPH type="129" size="1"/>
   <EFORMS type="129" size="1"/>
   <EDATE type="135" size="16"/>
   <APPROVAL_STATUS type="200" size="1"/>
  </fields>
  <row f0="380" f1="3001541" f2="3" f3="S" f4="211533" f5="08-11-2016 09:19:09" f6="johken" f7="16-08-2017 10:23:39" f8="s007yp" f10="S02" f11="Y" f12="Kontaktledningsbesiktning T-18" f13="AL" f14="Y" f15="FLP" f16="Y" f17="201804" f18="201809" f19="S_THEKRI" f20="Övrigt" f22="Y" f24="TrafikpÃ¥verkan:&#xA;EnkelspÃ¥r Arlöv - Flackarp." f25="0" f26="17" f27="UHOS" f29="142096" f30="SWECO" f33="77" f34="72" f35="SWECO" f36="25" f37="0" f38="0" f39="0" f40="Arbetsledare Tomas Hammarsten 0761322291&#xA;Absolut minsta tid 5h men helst 6 timmar sÃ¥ dom hinner med enligt plan.&#xA;TRV2014/471." f41="100" f42="0" f43="0" f46="A"/>
  <row f0="380" f1="3001542" f2="3" f3="S" f4="211625" f5="08-11-2016 10:07:04" f6="johken" f7="16-08-2017 10:24:46" f8="s007yp" f10="S02" f11="Y" f12="Kontaktledningsbesiktning T-18" f13="Ã…KN" f14="Y" f15="LU" f16="Y" f17="201810" f18="201816" f19="S_THEKRI" f20="Övrigt" f22="Y" f24="TrafikpÃ¥verkan:&#xA;EnkelspÃ¥r Lund - Ã…karp N." f25="0" f26="14" f27="UHOS" f29="142096" f30="SWECO" f33="74" f34="71" f35="SWECO" f36="16" f37="0" f38="0" f39="0" f40="Tomas Hammarsten 076-115 28 28&#xA;&#xA;TRV2014/471" f41="100" f42="0" f43="0" f46="A"/>
  <row f0="380" f1="3000937" f2="3" f3="S" f4="215571" f5="14-04-2016 11:57:16" f6="axelss" f7="14-09-2017 15:22:37" f8="liner2" f10="S04" f12="Skruv ny mötesstation, totalstopp, samt efterarbeten" f13="LO" f15="EM" f17="201749" f18="201849" f19="S_ANDHEN" f20="Övrigt" f24="TrafikpÃ¥verkan: Totalstopp Lo-Em v1805,1809 ca 80 timmar To-MÃ¥&#xA;Totalstopp v1826,1834.1840 ca 33 timmar F-Sö. &#xA;&#xA;V1834 sö kl 0700 -  sö v1840 kl 0700 spÃ¥r 1 i Skruv avstängt mellan si 31 och 32, trafiken leds pÃ¥ nya spÃ¥r 2." f25="0" f26="39" f27="IVsy2" f29="7973" f30="TRV" f33="31" f34="33" f35="TRV" f36="70" f37="0" f38="0" f39="0" f40="Anders Grönberg 072-224 49 00" f41="100" f42="0" f43="0" f46="A"/>
 </vity.recordset.possessions>
</trv_possessions>

Valued Guide
Posts: 576

Re: Reading ADODB files

Posted in reply to anders_hellsten_sj_se

There isn't, as far as I know, an ADODB file type. Instead ADODB is a database abstraction layer allowing you to write code to connect to and extract from any database which supports it in a DB agnostic way. This data is returned in what are called recordsets which can then be manipulated by the calling program.

 

It is possible to save these recordsets directly to a file in either a proprietary ADTG format or in XML format, which is what you appear to have. I've never worked with these files directly in SAS but you can find more information about them here -> https://docs.microsoft.com/en-us/sql/ado/guide/data/more-about-recordset-persistence

 

It may be that you can create an XML Map which will make life a bit easier but I don't believe there's any way of using them directly in SAS otherwise as they are not really intended to be used as data transfer files in the same way as CSVs are.

New Contributor
Posts: 2

Re: Reading ADODB files

Posted in reply to ChrisBrooks

Tks Chris.

 

Yeah I understand that it's basically a database dump and I thought that it might be possible to map it as a Library in SAS, I have tried to create an xml map with SAS XML mapper but so far with out any success as its not really comply with any xml logic.

Super User
Posts: 13,358

Re: Reading ADODB files

Posted in reply to anders_hellsten_sj_se

anders_hellsten_sj_se wrote:

Tks Chris.

 

Yeah I understand that it's basically a database dump and I thought that it might be possible to map it as a Library in SAS, I have tried to create an xml map with SAS XML mapper but so far with out any success as its not really comply with any xml logic.


If it is a dump of one of your organizations data bases perhaps you can connect to the DB directly using SAS/Access. Depending on your current license this may require an addition to the license. If you can document time spent with alternate approaches you might show current costs (man hours, data availability and such) vs license fee increase. You would have to see if SAS/Access supports your DB though.

Valued Guide
Posts: 576

Re: Reading ADODB files

Another option might be (if you have access to Visual Studio and are familiar with, say, C#) to write a short program in a .NET language which converts that file into a CSV using the GetString method defined here https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/getstring-method-ado

Ask a Question
Discussion stats
  • 4 replies
  • 150 views
  • 0 likes
  • 3 in conversation