Convert a folder of CSV files to TXT and stored them in a different folder

[Carcuro123x]

Hi everyone, I would like to get some help with a VBA program that converts CSV to TXT files.

Description: A bunch of CSV files is located in a folder and by pressing a button all those files are converted into TXT files and stored in a different folder that is initially empty.

But the thing is that each line in the CSV files has to be converted into a single TXT file. For example, if a CSV file has 10 lines of data, then 10 TXT files have to be created with the data of each line. I have to mention that each CVS files contain some headers in the first line so data starts from the second line. I also have to mention that the number of lines of data in each CSV file is not fixed.

Each TXT file has specific formatting with the headers and the data provided. The name of each TXT file created is like ABCPaymentInfo1234.txt where "1234" is the FileId column provided in the CSV file.

Finally, all CSV files are moved to the Recycle Bin folder. (This step is optional)

Any help or suggestion is greatly appreciated, thanks.

 

[Coding4Fun]

This should be a good start.

A few comments...
You have "Payment Method debit" in the .txt file but it does not seem to come from the CSV - this has been assumed as a hard coded value applied to all files
You don't seem to transfer project number from the csv to txt as its not in the screenshot for the txt file

This will loop through all files in the specified path you set under "folderName" in the code

Replace with the path to your CSV Files
folderName = "C:\Users\UserName\Desktop\Testing"

It will output the txt files to the folder you specify under "myFileName" in the code

Replace with the path you want to save your txt files
myFileName = "C:\Users\UserName\Desktop\Testing2\ABCPaymentInfo" & StrLineElements(0) & ".txt"

Sub LoopAllFilesInFolder()

Dim folderName As String
Dim FSOLibrary As Object
Dim FSOFolder As Object
Dim FSOFile As Object
Dim OpenFile As Object

'Set the file name to a variable
folderName = "C:\Users\UserName\Desktop\Testing"

'Set all the references to the FSO Library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(folderName)
Set FSOFile = FSOFolder.Files

'Use For Each loop to loop through each file in the folder
For Each FSOFile In FSOFile

    MyPath = FSOFile.Path
    Set OpenFile = FSOLibrary.OpenTextFile(MyPath)
    Index = 1
    Do While OpenFile.AtEndOfStream = False
        StrLine = OpenFile.ReadLine
        StrLineElements = Split(StrLine, ",")
       
        If Index <> 1 Then
        Range("A1").Value = "FileId: " & StrLineElements(0)
        Range("A3").Value = "Payment Method Debit"
        Range("A4").Value = "Acc Name: " & StrLineElements(2)
        Range("A5").Value = "Acc Number: " & StrLineElements(3)
        Range("A7").Value = "City: " & StrLineElements(4)
        Range("A8").Value = "State: " & StrLineElements(5)
        Range("A9").Value = "Zip Code: " & StrLineElements(6)
       
        myFileName = "C:\Users\UserName\Desktop\Testing2\ABCPaymentInfo" & StrLineElements(0) & ".txt"
       
        ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlTextWindows
        Cells.Clear
        End If
    Index = Index + 1
    Loop
    OpenFile.Close
    FSOFile.Delete
    Set OpenFile = Nothing
   
Next

'Release the memory
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Set FSOFile = Nothing

End Sub