Tuesday, August 27, 2013

Find Count of Files in a Folder at the Command Prompt

So you want to find a count of the number of files in a directory using the command prompt or a batch file, etc. Here is a really easy way to do it.

Just run the dir command and pipe the results to the find command, using the count parameter:

dir|find /c ".tif"

This will return a count of all the TIF files in your folder. You could replace ".tif" with whatever extension you want, such as ".pdf"

What about a folder with multiple types of files in it? Just add the /b parameter to the dir command to get the bare filenames, and then just look for the single period in the find command, like so:

dir /b|find /c "."

An alternate version that would count all the files in the directory and any sub directories (by adding the /s parameter to the dir command) would be:

dir /b /s|find /c "."

The above won't work if the filenames have other periods in them, like some.name.txt, but for the average filename, this should work just fine.

Lastly, here is the approach that will work with filenames, like some.name.txt, that have multiple periods in them, because it counts the lines, not the periods, in the output:

dir /b /a-d | find /c /v ""

That last one came from Joey:

Go Visit Joey's explanation...

Hope this helps someone out there...

Keywords: batch file, batch script counting files in a folder

Monday, August 12, 2013

SQL Server 2005 Restore Database Error: The operating system returned the error '5(Access is denied.)'

I was trying to restore some old SQL Server 2005 databases to get some data they contained, when I ran into this error:

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\test.mdf'. (Microsoft.SqlServer.Smo)

Your error message may look similar, except for the file location on the C drive, or whatever drive you are using.

After some frustration, I figured out the problem. I was actually trying to write my .mdf and .ldf files in a folder where they should not be located. That is why "Access is Denied" and it should be! You should write the files where your other database files are written. (If you need to find where they are, in SQL 2005, set up your Restore Database popup to restore from a currently active database, then go to the Options page on the left, and under "Restore the database files as:" look at "Restore As" which will contain the correct file location.)

In my error case, this location was not where my currently active databases where located, but was set to:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\

Your settings may be similar. In my case, this was definitely the wrong location, in fact, the wrong drive!

So to fix my situation, (and hopefully yours), I had to choose a new path and location for my .mdf and .ldf files. This is where the newly restored database files will be stored. (Which should be in the same folder as your other active databases):

  1. Starting from the Restore Database popup, look to the left and choose the Options page (in SQL 2005), then look for "Restore the database files as" right in the middle of the page.
  2. Just look to the right of the Restore As cell (on the Restore Database Options page), and click on the edit box symbol, which will bring up the Locate Database Files popup. Choose the correct path to the directory where you want the .mdf file to be stored, then enter the correct filename. Do the same for the .ldf file. 
Once I did this for both files, I was able to restore the database successfully. My assumption is that this will be similar for issues with SQL 2008 and 2012, etc.

Hope this helps you...

Tuesday, June 4, 2013

I was having trouble on Windows 2008 R2 running a batch script from a (local) shared drive as an Administrator (by right clicking and choosing run as Administrator). The command prompt window would just flash and never run successfully run the script, so doing the following made the shared drive "visible" to the Administrator profile, and would then allow successful running of the script.

Here is what I did, using ideas gleaned from the websites listed below... and copying some of their comments... Thanks guys...

--I wanted to create a service that would allow me to spawn a command prompt under the context of the local system

sc create syscommand binpath= "cmd /K start" type= own type= interact

--Then I started the service

sc start syscommand

--Voila, apart from a few minor errors, the command prompt popped up (sigh of relief)

--Run Net to map a drive for the system context, then every user account, including the Administrator, will have access to that shared drive

NET USE Z: \\MyLocalMachine\MyLocalShare

--removed the service account

sc delete syscommand

Ideas taken from:




Thursday, March 28, 2013

Convert Excel .xls to .xlsx and .xlsm using VBA

Here are some Excel macros to quickly convert .xls files to .xlsx and .xlsm formats for newer versions of Microsoft Excel. Just paste this in your VBA window (in a module) in a .xlsm file, and use Excel to automatically do its own conversions. You then run either macro, which will ask you to choose a folder, and then all .xls files will be converted to .xlsx or .xlsm depending on whether they have macros or not...

Sub Copy_XLS_as_XLSX()

    Convert_XLS_to_XLSX False
End Sub

Sub Delete_XLS_after_Copy_XLS_as_XLSX()

    Convert_XLS_to_XLSX True
End Sub

Sub Convert_XLS_to_XLSX(ByVal deleteXLS As Boolean)
    ' Allow user to choose a folder,  where all .xls files in that folder will be converted to
    ' .xlsx or .xlsm format, depending on whether they have macros or not...
    Dim xDirect$, xFname$, InitialFoldr$
    Dim wbk As New Workbook
    Dim msg As Integer
    InitialFoldr$ = "c:\temp\"    'Startup folder to begin searching from
    If deleteXLS = True Then  'as user if they really want to delete .xls files
        msg = MsgBox("Do you want to delete all .xls files after you have created a copy in .xlsx format? If you are not sure, click NO!", vbYesNo, "Ready to delete .xls files?")
    End If
    If msg = vbNo Then  'user doesn't want to delete files...
        deleteXLS = False
    End If
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder containing the .xls files you want to convert..."
        .InitialFileName = InitialFoldr$
        If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1) & "\"
            xFname$ = Dir(xDirect$, 7)
            Do While xFname$ <> ""  'loop through all filenames in folder
                If Right(xFname$, 4) = ".xls" Then  'only convert .xls files
                    Application.DisplayAlerts = False  'turn off any unwanted messages
                    Set wbk = Workbooks.Open(Filename:=xDirect$ & xFname$)
                    If wbk.HasVBProject Then  ' convert Excel files containing Macros
                      wbk.SaveAs Filename:=xDirect$ & xFname$ & "m", _
                    Else  ' convert standard Excel files
                       wbk.SaveAs Filename:=xDirect$ & xFname$ & "x", _
                    End If
                    wbk.Close SaveChanges:=False
                    If deleteXLS = True Then  'delete existing xls files if desired
                        With New FileSystemObject 'include Excel reference to Microsoft Scripting.Runtime library... or this won't work...  Go to Tools>References in the VBA editing window
                            If .FileExists(xDirect$ & xFname$) Then
                                .DeleteFile xDirect$ & xFname$
                            End If
                        End With
                    End If
                    Application.DisplayAlerts = True  'turn messages back on
                End If
                xFname$ = Dir  ' get next filename in folder
        End If
    End With
    xRow = MsgBox("All .xls files have now been converted.", , "Finished!")
End Sub