OK, I’ve made progress with this – it’s been a bit of painful experience, but I’ve now got the answers I want. I’m sure there’s a more refined approached to sorting this out, so please step forward if you’ve got a suggestion, but to share it with you this is what I did:
- Performed a ‘split vector layer’ on the my 400m buffer layer,
which created over 100 shapefiles and I dumped these into their own folder
- Located the ‘join attributes by location’ in the Processing toolbox and right clicked it then selected ‘execute as batch process’. I then populated the table that popped up with the individual buffer shapefiles and bus stop layer file – it was a slow
process given the amount of files I had, but when hit run button the
thing did what I wanted.
- I then had over a 100 output files that I needed to merge. In the end I decided the best way to go was to combine the separate
dbf files into a single dbf file in Excel. I did this using the
instructions as below – all credit for this goes to somebody
called Brian Johnson who posted this on the ESRI website, to which
I extend my thanks
This is an Excel VBA solution. Hopefully the code is commented enough to follow.
1) Open a new Excel workbook.
2) Go to the VBA Editor and insert a 'Module'.
3) Paste the code shown below into the Module.
4) Save this Excel workbook (as an XLSM if you are using Excel 2007 or above).
5) Change the FolderPath assignment to the folder with your DBF files.
6) Run the Merge_DBF_Files sub.
7) You can use the Column A list of source file names to come up with an Excel formula to extract out the data you need from the file name using FIND, MID, etc. Delete other DBF field columns you don't need.
I tested this on a small set of DBFs and it works fine. Not sure how it will work with 1,000 files...
' VBA routine to merge all DBF files in one folder into one Excel worksheet
' Brian Johnson
' [email protected]
Dim SummarySheet As Worksheet, WorkBk As Workbook
Dim NRow As Long, FileName As String
Dim SourceRange As Range, DestRange As Range
Dim FolderPath As String
' Create a new workbook and set a variable to the first sheet.
Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
' Modify this folder path to point to the files you want to use.
FolderPath = "C:\temp\DBF_Files\"
' NRow keeps track of where to insert new rows in the destination workbook.
NRow = 1
i = 0
' Call Dir the first time, pointing it to all DBF files in the folder path.
FileName = Dir(FolderPath & "*.dbf")
' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)
Debug.Print Format(i, "00") & ":" & FolderPath & FileName
' Set the cell in column A to be the file name.
SummarySheet.Range("A" & NRow).Value = "FileName"
' Set the source range
RowCount = Application.CountA(Range("A:A"))
Set SourceRange = WorkBk.Worksheets(1).Range("A1:BB" & RowCount)
SummarySheet.Range("A" & NRow + 1 & ":A" & (RowCount + NRow - 1)).Value = FileName
' Set the destination range to start at column B and be the same size as the source range.
Set DestRange = SummarySheet.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, SourceRange.Columns.Count)
' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value
' Increase NRow so that we know where to copy data next.
NRow = NRow + DestRange.Rows.Count
' Close the source workbook without saving changes.
' Use Dir to get the next file name.
FileName = Dir()
i = i + 1
' Call AutoFit on the destination sheet so that all data is readable.