No announcement yet.

Edit the top row only of a text file to replace spaces with underscore?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Edit the top row only of a text file to replace spaces with underscore?

    Hi all

    Hope you guys can help. I've googled and can't find exactly what I need, and your forums look like having very good responses to other, similar, questions.

    I have a text (Customers.CSV) file that has data like this:

    "ID","Customer Name","Customer Town"
    "234","Brian Smith","Little Village"
    "521","Ted Copeland","Townsville"
    Before importing this CSV into a GIS program I need to rename only the column headers, to remove the spaces and replace with underscore. The ideal result is a CSV that has:

    "234","Brian Smith","Little Village"
    "521","Ted Copeland","Townsville"
    My searching online has turned up a few scripts to do a global replace within a text file, but I only want to affect the first row, not the names, I don't want to end up with "Brian_Smith".

    My apologies for this bit but ... I don't really know all that much about scripting. I'm thinking a batch file would be convenient for me. This is a nightly task and I don't want to edit the text file manually each time. I'm capable enough to set a batch file to run before my GIS import, but not really with generating a starting suggestion script and asking for mods.

    Searching the internet produced the below kind of result for total search/replace, which I've modified (bolded) with my own settings, in the idea that it would replace all spaces, including in Brian's name, and I'd figure out how to get a first row only from there. But it didn't work.

    @echo off
    copy Customers.csv text.tmp
    for /f %%a in (text.tmp) do (
    set foo=%%a
    if !foo!==" " set foo="_"
    echo !foo! >> result.csv)
    del text.tmp
    Rather than generate my data with underscores for every space, it replaced the first space in every line with an end-of-line-delete-all-after thing - the result was:

    Because of the lack of double-quote at the end, this is not interpreted correctly as a csv - those aren't considered distinct lines.

    Is there some way to build on this, or start with something else entirely, to just change space to underscore, on just the first row?

    Thanks in advance. Please let me know if there is any further information that would be helpful. I've tried to put in as much as I can.


  • #2
    Re: Edit the top row only of a text file to replace spaces with underscore?

    You can use SED for this.

    :: assuming Sed.exe and the dlls are in the same folder as the batch file.
    @echo off
    :: define variables
    Set "InputFile=Customers.CSV"
    Set "OutputFile=tempfile.txt"
    :: convert unicode- to ansi format
    rem  type "%InputFile%" > "%InputFile%.tmp"
    rem  move /y "%InputFile%.tmp" "%InputFile%"
    :: define a block that should be processed only
    Set "firstLine=1"
    Set "lastLine=1"
    Set "ReplaceSpaceWith=_"
    :: run sed, output to temp file
    Set "SED=%~0\..\sed.exe"
    >"%OutputFile%" Call :SED
    :: enable the next line to actually overwrite(!) the original file with updated data
    rem  move /y "%OutputFile%" "%InputFile%"
    GOTO:EOF   - - - begin of sub routine - - -
    "%SED%" -e "%firstLine%,%lastLine% s/[ \t]/%ReplaceSpaceWith%/g" "%InputFile%"
    exit /b 0

    (Similar posts
    Last edited by Rems; 9th March 2012, 18:56.

    This posting is provided "AS IS" with no warranties, and confers no rights.


    ** Remember to give credit where credit's due **
    and leave Reputation Points for meaningful posts


    • #3
      Re: Edit the top row only of a text file to replace spaces with underscore?

      Thank you so much Rems! Perfect!