Converting Text delimited files from , to ;

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
LVL 1
ntsabmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
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?
0
ntsabmanAuthor 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
0
MacroShadowCommented:
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

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
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
0
ntsabmanAuthor 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.
0
ntsabmanAuthor 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
0
Jeffrey CoachmanMIS LiasonCommented:
Oh, I see,

Then investigate MacroShadow's post above...
0
MacroShadowCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.