Good old DOS

Hi all

When Dave Peterson posted this the first time in the newsgroup to a question about
merging CSV files I say WOW that’s great.

1) Windows Start Button | Run
2) Type cmd and hit enter (“command” in Win 98)
3) Go to the folder with the CSV files (for help enter “help cd”)
4) Type copy *.csv all.txt and hit enter to copy all data in the files into all.txt.
5) Type exit and hit enter to close the DOS window

Very simple and also fast.

I add a VBA example to my site that do the same thing, see
http://www.rondebruin.nl/csv.htm

Question: do you use other things like this in your work

Ron de Bruin
http://www.rondebruin.nl

Posted in Uncategorized

16 thoughts on “Good old DOS

  1. i only use dos for about 2 things,
    1, formating HDD – i just like doing it in dos for some reason
    2, network things like netsend, IP pings, getting ip address.

    Other than that i don’t really use it much

  2. I like the solution :)

    I’ve created 8-10 solutions with a third party ActiveX control that handle different types of textfiles including merging files.

    It’s remarkable, but I still need to maintain an application that deals with the prn filformat…

    Hm, I wonder when the good old formats will *fully* be replaced by xml?

    Kind regards,
    Dennis

  3. I often do a

    dir /B > list.txt

    or some other combination of Dir

    to get a quick list of files in a directory,
    import the list.txt file and voila

    Hui…

  4. Actually I still use DOS (or at least the emulation of it) for a couple of things. I guess old habits don’t die. Anyone here administrating a domain server probably uses DOS commands for startup scripts for users.
    I reckon you could use this commands from Excel to for various purposes.

    A couple of things I use:

    1) Create a network share from a batch file:
    subst Z: \aserverashare
    This will create a virtual drive “Z:” linked to a share

    2) Or create a virtual drive of a directory:

    Subst R: D:ImageRouteplanner

    (Works for me to share a routeplanner of harddisk rather then from CDROM on our network.)

    3) Synchronize time with another computer:

    NET TIME \Computername /SET

    4) Or print a date sorted directory listing directly to a local printer:

    dir C:Data /ad /od > LPT1

    6) Delete an entire directory and it’s subdirectories from the prompt without question (use with caution):

    echo Y | del D:Somedir*.* /s

    etc. Everything can be combined; e.g. print the computers in your network neighberhood:

    net view > LPT1

    Basically everything that is handy to use on the prompt can be used within Excel to. .

    Rembo

  5. There are so many Windows tools (most of them third party) that do the same things as lot of these DOS commands. I have found all sorts of neat tools on download.com.

    Ironically, about the only thing I use DOS for is to use UNIX commands, because I download some toolset of UNIX tools for DOS, so that I can use stuff like SED and GREP in DOS.

  6. I weekly have to export 9 .csv files from out horrible data system and then I wrote a VBA function to combine them into one file. I like the ease of this info you posted here, I’ll see if processing the files this was is any faster. Here is what I currently do:

    ’32-bit API declarations
    Declare Function SHGetPathFromIDList Lib “shell32.dll” _
    Alias “SHGetPathFromIDListA” (ByVal pidl As Long, ByVal pszPath As String) As Long

    Declare Function SHBrowseForFolder Lib “shell32.dll” _
    Alias “SHBrowseForFolderA” (lpBrowseInfo As BROWSEINFO) As Long

    Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
    End Type

    Sub Combine_SnapShots()

    Dim strWorkfile As String
    Dim strPath As String

    ‘Skip Over Any Errors
    On Error Resume Next

    ‘Set Variables for File Name
    strWorkfile = “Combine BOM SnapShots”
    strPath = “\arlfs03sharedEngineeringENGYacht Development – MeridianInfoBOM”

    ‘Delete Previous File
    If Len(Dir(strPath & strWorkfile & “*.*”)) > 0 Then
    SetAttr strPath & strWorkfile & “.xls”, vbNormal
    Kill strPath & strWorkfile & “*.*”
    End If

    ‘Save Current File as Snapshot
    ActiveWorkbook.SaveAs filename:= _
    strPath & strWorkfile & “.xls”, _
    FileFormat:=xlNormal, Password:=””, WriteResPassword:=””, _
    ReadOnlyRecommended:=False, CreateBackup:=False

    Current_File = ActiveWorkbook.Name

    ‘Locate Directory of CSV files
    Msg = “Select a location containing the files you want to list.”
    Directory = GetDirectory(Msg)
    If Directory = “” Then Exit Sub
    If Right(Directory, 1) “” Then Directory = Directory & “”
    With Application.FileSearch
    .NewSearch
    .LookIn = Directory
    .filename = “*.csv”
    .SearchSubFolders = False
    .Execute

    Application.ScreenUpdating = False
    ‘Loop to copy the BOM Snapshots into Current_File
    For i = 1 To .FoundFiles.Count
    Workbooks.Open (.FoundFiles(i))
    New_WorkBook = ActiveWorkbook.Name
    Range(“A10?).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows(Current_File).Activate
    ActiveCell.SpecialCells(xlLastCell).Select
    ActiveCell.Offset(1, 0).Select
    Selection.End(xlToLeft).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows(New_WorkBook).Close SaveChanges = False
    Next i
    End With

    ‘Clean up the file
    Format_SnapShot
    ActiveWorkbook.Save
    SetAttr strPath & strWorkfile & “.xls”, vbReadOnly
    Application.ScreenUpdating = True
    Range(“A1?).Select

    End Sub

    Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer

    ‘ Root folder = Desktop
    bInfo.pidlRoot = 0&

    ‘ Title in the dialog
    If IsMissing(Msg) Then
    bInfo.lpszTitle = “Select a folder.”
    Else
    bInfo.lpszTitle = Msg
    End If

    ‘ Type of directory to return
    bInfo.ulFlags = &H1

    ‘ Display the dialog
    x = SHBrowseForFolder(bInfo)

    ‘ Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
    pos = InStr(path, Chr$(0))
    GetDirectory = Left(path, pos – 1)
    Else
    GetDirectory = “”
    End If
    End Function

    -Steve

  7. Well, technically, if you are using cmd.exe it isn’t DOS. It’s the NT command line console that can be used as a less than perfectly compatible DOS emulation. The difference is only significant if you are tasked with getting old DOS programs to run under NT.

    In any case, I work with a *LOT* of CSV files coming from POS systems. I build flat file databases in excel with them which I then review for coding errors before copying them into Solomon. From time to time I have a use for command line tools as well. I use a variety of the gnuwin32 ports such as awk, sed and grep. The complete collection is available at:

    http://gnuwin32.sourceforge.net/

    An interesting graphical tool for working with CSV files is the CSVed. It and some other unique and useful text tools are available for free from:

    http://home.hccnet.nl/s.j.francke/t2t/text2table.htm

  8. There’s also PCDOS, DRDOS etc. If it’s just a commander you want, you might want to take a look at 4DOS (now free but unsupported). I’ve been using that from the beginning to create ‘magical’ batchfiles, with colored inputboxes etc. They were using pushd and popd before MS even heard about it. Great commander that I still use every now and then, even under Windows XP.

    http://www.jpsoft.com/download.html

    Rembo

  9. i tried the

    copy *.csv all.txt

    and it is wrapping the end of each file with the new csv file (or 2nd file rather).

    is there a way to have it put in a “hard return” at the end of each file so it keeps each record on one line rather than wrapping?

    thanks!

  10. steven: not using copy. The copy command reproduces each file EXACTLY, so if any are missing newlines at the end, those lines will be missing newlines in the combined file, so the first line of the next file will continue on that line in the combined file.

    You’d need to use a for loop calling more.

    (for %f in (*.csv) do @more %f) > all.txt

    Or you could fix your CSV files.

    for %f in (*.csv) do echo/>> %f

  11. I’m a big fan of CMD.EXE under Windows XP (NT version was pretty good too). You can do some powerful stuff (and quickly) once you get round some counter-intuitive syntax issues.

    As an application support monkey on a domain with 3,500+ XP clients I’m happy to take advantage of anything I can to make life easier.

    For those who want to get into CMD scripts, I recommend Tim Hill’s excellent “Windows NT Shell Scripting”. Doesn’t cover XP and above but the first couple of chapters are invaluable.

    I haven’t found a better definitive introduction online.

    If I ever get my own website running I’ll be posting my favourite CMD script tips.

  12. I have several .csv files in one folder. I have used the copy c:walley*.csv fred.csv. I need all of the merged csv files in one csv with a new name. This works fine, but the first row of each csv file are the same. I quess what I am asking is there a way to copy ranges.

  13. Marcus, this isn’t the best forum for help with batch files. Better to use the alt.msdos.batch.nt or microsoft.public.win2000.cmdprompt.admin newsgroups.

    That said, if you want to skip a given number of lines in each file but the first, you could try the following batch file.

    @setlocal enableextensions
    @echo off
    if “%~1? == “” if “%~2? == “” (
    echo usage: “%~0? filemask targetfile [skiplines]
    goto :EOF
    )
    set tf=”%~2?
    if exist %tf% del %tf%
    if not “%~3? == “” set skip=+%~3
    for %%f in (“%~1?) do call :PROC “%%f”
    goto :EOF
    :PROC
    more %skipswitch% “%~1? >> %tf%
    set skipswitch=%skip%


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *