We help IT Professionals succeed at work.

How to query out specific text from a dbf to an Excel file or even text file if neccessary

mpearson29
mpearson29 used Ask the Experts™
on
I have a text file that lists all the source file information included in a GIS MXD project.  I'd like to search the file and reformat the results into a new table.  I'm not a programmer - just sort of dinking around with this to see if I can get it to work.  

I'd like to capture the information after each of the following  and add them to a row or record in a new text file or excel file...   For each MAPDOUMENT there are many of the the other three fields I want to capture.  

" MAPDOCUMENT: "

"LAYER: "
"Data Source:  "  
"Dataset type: "

I've attached a dummy file to show the file structure.  Again Each Mxd has many layers.  I need the layer name, data source, and dataset type for each one.  Any ideas?  I am hoping for some sort of simple query or something i can do in note pad or Excel.

Goal would be a table with fields for the MapDocument, Layer, Data Source, and Dataset type.
dummy-MXDReport-sani.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
I copied the report text into a worksheet and did the following:
1. inserted a new first row
2. Created four distinct column header values (Col1, Col2, Col3, Col4) in A1:D1
3. Selected all the used cells from A1 through the end Ctrl+Shift+End
4. Turned on the Data Filter
5. Applied the following filters
Col1 := Does not begin with "MAPD"
Col3 := Does not begin with "LAYER"
Col4 := Does not begin with "Data"

6. Selected the filtered rows, starting in row 2, and delete them
7. Removed the filter

At this point, you can (optionally) delete row 1 and column B.

Note: You can record these steps as a macro.

================
You haven't shown us how you want this data to appear in your worksheet.  Please post a Workbook with the data laid out as you envision it.

Author

Commented:
Thank you so much for replying.  I'll give it a shot.  I've never used filters so can't see how this will work but will go try now.  Here is a sample results file attached with what I'd like the end product to look like.  Am considering dropping the "dataset Type" field, but will leave in for now.
Sample-Results.xlsx
All can be done in VBA. Look example. Enter file name to cell A1, then press button. Results will be in Sheet2.
Book1.xlsm
Top Expert 2014

Commented:
very good, als.

Author

Commented:
I am embarrassed to say nothing happens when I run the macro (hit the button).  I clicked enable macros when I opened the file.  Tried it on my home computer with Office 2007 and work laptop with 2010.  
The path to my text file is correct.  The first attempt had spaces in the path, but second I moved it to a simpler file location (c:\_working\test\MXDreport.txt)
Is there something else I am missing to enable macros?  I'll only be on a another 20 - 30 min this a.m.  Will be available again tonight for a bit, but am on AK time zone.

Author

Commented:
I did get the filters to work on the laptop (2010) aikimark.  Thank you.  A couple of the MXDReport files have over 2000 lines, so I think the VBA solution will be the one I'm looking for if I can figure out how to run it.
Top Expert 2014

Commented:
I used filters to get a better look at the data, eliminating the non-pertinent lines, and progress the problem solution process.  I had played with RegExp last night, trying to find a simple parsing solution.  I didn't get a working solution and had resolved to roll up my sleeves this morning to parse the data with VBA code.

My code would have looked similar to the als code.
Try to save this xlsm file to "trusted location". You should also reopen file after macros were allowed. As a result you should not have any warnings during file opening. (If you like to see VBA code, press Alt+F11 and look to Modules-Module1)

Author

Commented:
Thanks.  Will try again at work later this a.m.  Looked at the VBA yesterday - seems straight forward and it should work.  Will let you know!  Thanks!

Author

Commented:
Wow!  Very good indeed.  Thank you!

Author

Commented:
Wow!  Very slick and EXACTLY what I needed.  I could have banged my head against the wall for a week or two trying to manually extract what I needed or coming up with a less exact solution.  Thank you!
You are welcome

Commented:
This is to mpearson29, how did you get your MXD into a text document in the first place? (I'm brand new to Arc, so I appologize if my question is super basic).

Author

Commented:
It's a list of MXD info extracted with a python sample script called MultiMXDReport. It comes in a set of samples on the ESRI script site.  "SampleArcPyMappingScriptTools" by Jason Barrette.  Lots of good samples in the set and it was very straightforward to use.  Download the set and follow instructions. I tried to copy and paste out of a post on the user forum & the format was messed up.  
M

Commented:
Thanks!