Convert CSV files to TXT using VBA

[Sharda]

I need to convert these CSV files to TXT, I succeeded with below code.
However need help with two things:
1) This code should be applied to all CVS files in given path. Because It gave result on only given number.
2) I want on TXT top line as: LEDES98BI V2[] instead of LEDES98BI V2[]|||||||||||||||||||||||||||||||||||||||||||||||||||

Sub csv2mat()
Dim Filename As String
Dim Filenamenew As String
Dim str As String
'Change the path to the Files and and create a txt file
Filename = "C:\Users\Rimpi Pihuja\Desktop\New folder (2)\New folder\New folder\1040448.csv"
Filenamenew = "C:\Users\Rimpi Pihuja\Desktop\New folder (2)\New folder\New folder\1040448.txt"
Open Filenamenew For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL] 
Open Filename For Input As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
Do While Not EOF(1)
    Line Input [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , str
    str = Replace(str, ",", "|")
    Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL] , str
Loop
Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL] 
End Sub

 

[Stiuart_W]

So request #2 You mean you want the first line to be converted to LEDES98BI V2[] from LEDES98BI V2[]|||||||||||||||||||||||||||||||||||||||||||||||||||? Id this the same exact text in very file? Or is ALWAYS the first 14 characters?

 

[Sharda]

Yes The first line is same in every file as LEDES98BI V2[] and nothing beyod this.

 

[Stiuart_W]

You'll need to change the file paths back to your own but this will read all the csv files and convert to txt

Sub csv2mat()
Dim Filename As String
Dim Filenamenew As String
Dim str As String
Dim FilePath, MyFile As String
FilePath = "C:\Users\bz477\Desktop\Test_macro\"
MyFile = Dir("C:\Users\bz477\Desktop\Test_macro\*.csv")
Do While MyFile <> ""
    Filename = FilePath & MyFile
    Filenamenew = FilePath & Left(MyFile, Len(MyFile) - 3) & "txt"
    Open Filenamenew For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 
    Open Filename For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , str
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] , "LEDES98BI V2[]"
    Do While Not EOF(1)
     Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , str
     str = Replace(str, ",", "|")
     Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] , str
    Loop
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 
    'Get next file name
      MyFile = Dir
  Loop
End Sub

 

[Sharda]

Thanks Stiuart, but this is not working.

 

[Sharda]

Thanks Stiuart, but this is not working.

 

[philwojo]

I would suggest you post back your exact code and then tell us what it is doing rather than a generic it isn't working. The more descriptive you can be the more helpful others can be.

 

[Sharda]

Hi Phil, I used code as below:

Sub csv2mat_New()
Dim Filename As String
Dim Filenamenew As String
Dim str As String
Dim FilePath, MyFile As String
FilePath = "C:\Users\Rimpi Pihuja\Desktop\New folder (2)\New folder\New folder\"
MyFile = Dir("C:\Users\Rimpi Pihuja\Desktop\New folder (2)\New folder\New folder")
Do While MyFile <> ""
    Filename = FilePath & MyFile
    Filenamenew = FilePath & Left(MyFile, Len(MyFile) - 3) & "txt"
    Open Filenamenew For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 
    Open Filename For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , str
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] , "LEDES98BI V2[]"
    Do While Not EOF(1)
     Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , str
     str = Replace(str, ",", "|")
     Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] , str
    Loop
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 
    'Get next file name
      MyFile = Dir
  Loop
End Sub

However I'm neither getting any error nor any results.

 

[Stiuart_W]

1) This line MyFile = Dir("C:\Users\Rimpi Pihuja\Desktop\New folder (2)\New folder\New folder") should have \*.csv at the end

2) Also - Likely that you have NO files in the destination - the loop will no loop if MyFile is blank (Ie if it is an empty folder or has no csv files once you make the change above.).


Put a break point in the code and check the value of the variables

 

[Sharda]

Still not difference, below script is for reference. Kindly correct me if I'm wrong.
I also tried to paste screen shot of given path, to confirm there are CSV files saved on given path.

Sub csv2mat_21()
Dim Filename As String
Dim Filenamenew As String
Dim str As String
Dim FilePath, MyFile As String
FilePath = "C:\Users\Rimpi Pihuja\Desktop\New folder (2)\New folder\Macro Test"
MyFile = Dir("C:\Users\Rimpi Pihuja\Desktop\New folder (2)\New folder\Macro Test\[B][COLOR=#ff0000]*.csv[/COLOR][/B]")
Do While MyFile <> ""
    Filename = FilePath & MyFile
    Filenamenew = FilePath & Left(MyFile, Len(MyFile) - 3) & "txt"
    Open Filenamenew For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 
    Open Filename For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , str
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] , "LEDES98BI V2[]"
    Do While Not EOF(1)
     Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , str
     str = Replace(str, ",", "|")
     Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] , str
    Loop
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 
    'Get next file name
      MyFile = Dir
  Loop
End Sub