Converting Text delimited files from , to ;

ntsabman
ntsabman used Ask the Experts™
on
Some one have an idea how to convert a files that is texte delimited by , and change it to ; but i need this to be donne automatic from a at command in Windows so a batch files
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If there are no occurrences of comas in the file besides for the delimiter it can be done.

Does it have to be done within Access or would running a Vb Script be OK too?

Author

Commented:
Could be vbscript since i link in the file from access but copy the files during the nigth and there only comas for the dilimiter
OK, well here is a vbs you can try, if you want it can easily be converted to Access.
Option Explicit

Dim objFSO, strFolder, objFolder, objFile
Dim strOldValue, strNewValue, objRead, strContents, objWrite

Const ForReading = 1
Const ForWriting = 2

strFolder = InputBox("Enter Directory", "Data Entry")
strOldValue = ","
strNewValue = " ;"

' Retrieve specified folder.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strFolder)

' Enumerate all files in the folder.
For Each objFile In objFolder.Files
    ' Read file with textstream object.
    Set objRead = objFSO.OpenTextFile(objFile.Path, ForReading)
    ' Trap error if file is empty or cannot read.
    On Error Resume Next
    strContents = objRead.ReadAll
    If (Err.Number <> 0) Then
        On Error GoTo 0
        Wscript.Echo "Cannot read: " & objFile.Path
        strContents = ""
    End If
    On Error GoTo 0
    objRead.Close
    ' Check contents for specified string.
    If (InStr(strContents, strOldValue) > 0) Then
        ' Open file with textstream object to overwrite.
        strContents = Replace(strContents, strOldValue, strNewValue)
        Set objWrite = objFSO.OpenTextFile(objFile.Path, ForWriting)
        objWrite.Write strContents
        objWrite.Close
    End If
Next

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
First always check with the entity that creates these files.
Simply ask them if they can provide the file with the needed delimiter...
(in many cases, they can, ...all you have to do is ask...)

;-)

JeffCoachman

Author

Commented:
i did but the problem is being i work in french and in french de , comas is for $ and the sql server is in English.

Author

Commented:
i dont do VBScript alot here may path and files i need to convert when i put it in your script i get some error

Path = W:\transport\DonneesServicentre
Files = EquipData
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Oh, I see,

Then investigate MacroShadow's post above...
I'm not sure what you mean by: Files = EquipData

Anyway, here is the above code in vba which you can use in Access.

Option Explicit

Sub Test()
    Dim objFSO As Object
    Dim objFolder, objFile, objRead, objWrite
    Dim strOldValue As String, strNewValue As String, strFolder As String, strContents As String

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Const ForReading = 1
    Const ForWriting = 2

    strFolder = "W:\transport\DonneesServicentre"
    strOldValue = ","
    strNewValue = " ;"

    ' Retrieve specified folder.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(strFolder)

    ' Enumerate all files in the folder.
    For Each objFile In objFolder.Files
        ' Read file with textstream object.
        Set objRead = objFSO.OpenTextFile(objFile.path, ForReading)
        ' Trap error if file is empty or cannot read.
        On Error Resume Next
        strContents = objRead.ReadAll
        If (Err.Number <> 0) Then
            On Error GoTo 0
            MsgBox "Cannot read: " & objFile.path
            strContents = ""
        End If
        On Error GoTo 0
        objRead.Close
        ' Check contents for specified string.
        If (InStr(strContents, strOldValue) > 0) Then
            ' Open file with textstream object to overwrite.
            strContents = Replace(strContents, strOldValue, strNewValue)
            Set objWrite = objFSO.OpenTextFile(objFile.path, ForWriting)
            objWrite.Write strContents
            objWrite.Close
        End If
    Next

End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial