How do I insert a CRLF after a comma in a csv file with no crlf's?

Question

Hi,

I have been passed a csv file which is just a lod of numbers with commas between then

e.g.

1,2,3,4,5,6

etc.

There are no crlf's after the commas so I can't import this into excel or a database to further process.

Does anyone know how I can process this file using either batch file or VBA to insert a crlf after each comma?

Thanks, Steve

Answer 1

You could do it with something like notepad2...

ctrl+H -> replace "," with ",\r\n" and tick "Transform Backslashes"

Answer 2

You can use the linux or cygwin utility tr

cat FILENAME | tr , '\n'

Answer 3

YOu could also use sed:

sed 's/,/,\r\n/g' FILENAME

(You can use the -i option for an in-place replacement in the file too)

Answer 4

If you have access to VBA, write a find and replace function that reads from one file and writes to another. Normally, I'd use the FileSystemObject but it is not always available in controlled environments.

Public Sub ReplaceInFile(sOld As String, sNew As String, _
 sFind As String, sReplace As String)
'-----------------------------------------------------
' Procedure : ReplaceInFile
' Author    : cmsjr
' Date      : 9/15/2009
' Purpose   : Make up for lack of text manipulation in windows shell
' Sample    
' Call ReplaceInFile("C:\DelimisFun.txt", "c:\NewFile2.txt", ",", "," & vbCrLf)
' Parms     :
' sOld is the file you want to change
' sNew is the new file, should not be the same as the old ;)
' sFind what you are looking for
' sReplace, what you would like to see instead
'
'------------------------------------------------------------
'
On Error GoTo Error_Proc

    Dim lin, lout As Integer 'file handles
    Dim strBuff As String 'buffer
    If Dir(sOld) = "" Then 'can't read what's not there
        MsgBox "Invalid File"
    Else
        lin = FreeFile
        Open sOld For Input As #lin 'open input
         lout = FreeFile
        Open sNew For Append As #lout 'open output
        Do Until EOF(lin) 'loop through file
            Line Input #lin, strBuff 'read the old
            Write #lout, Replace(strBuff, sFind, sReplace) 'write the new
        Loop
    End If
Exit_Proc:
    Close #lin
    Close #lout
    Exit Sub
Error_Proc:
    MsgBox Err.Description, vbOKOnly
    Resume Exit_Proc
End Sub

Super Evil bad bat file approach

But what if we didn't have VBA or any *nix style tools. Here's one inadvisable way

1. Create a bat file that will loop over it's command line parameters and echo them to a hard coded file. Note the use of shift, this shifts the next command line argument down one index ($2 value is now in $2 etc), this works for us because the bat file will ignore commas in command line arguments (per For loop man page), Note we are also replacing the comma since it was ignored. I haven't tested this on large data sets, it worked well for 2000 lines.

   @ECHO OFF :Loop IF "%1"=="" GOTO Continue

   echo %1, >> NewFile.txt
   

   SHIFT GOTO Loop :Continue

2. Wrap the call to the bat file in a for loop, so we are basically passing each comma delimited value in the text file as a command line argument to the bat file.

   for /F %x in (delimisfun.txt) do c:\bats\winreplace.bat %x

Answer 5

SPlitCSV.cmd

@echo off

For /f "tokens=*" %%A in (%1) do call :ProcessLine %%A

goto :eof

:ProcessLine

echo %1
shift
if [%1]==[] goto :eof
goto :ProcessLine

goto :eof

Test.CVS 1,2,3,4,5

SplitCSV.cmd Test.csv

1 2 3 4 5