Macro to convert TXT to CSV files

[neobavesten]

Hi,

I have a bulk of TXT files organized in same format. Is the a way to write a macro that could convert those text files to *csv files?

Text file contains multiple sentences. I would like to keep that text as one block in CSV file.

I would really appreciate if someone could help me with this.
Many thanks!

 

[Ruddles]

Do you want each line of the text file to be converted to a single line in the CSV file or do you want each line of the text file split up into multiple comma-separated values?

If the latter, what's the rule for splitting the line up?

Posting a sample of the data would help, preferably in a fixed-width font like Courier New.

 

[diddi]

the project is not especially difficult, but we need a decent sample of text file and how you want it formatted (where do the commas go)

 

[neobavesten]

Hello,

Please find below, how each of those text files look like. All files have
the same format.

Headings (Title,Word Count, Summary...) should be on the first line of the
CSV file. Below, there should be adequate data delimited by comma.

Since there is around 1000 of those TXT files, process should be looped.
I hope this makes things more clear. Any ideas if this can be done?

Title: 
Your Options in Car Financing

Word Count:
623

Summary:
There are so many car financing options available how do you know which one is right for you? Read on to obtain information about all of the different options available and how to determine which one will provide you with the best benefits.


Keywords:
car, finance, loan, personal, borrow, lending, auto, insurance, purchase, cost, interest


Article Body:
There are so many car financing options available how do you know which one is right for you? Read on to obtain information about all of the different options available and how to determine which one will provide you with the best benefits....

 

[diddi]

so what do you want it to look like

 

[neobavesten]

It should look like this:

Title, Word Count, Summary, Keywords, Article Body
TITLE1, 1, Text1 Summary, Text1 Keywords, Article1
TITLE2, 2, Text2 Summary, Text2 Keywords, Article2
TITLE3, 3, Text3 Summary, Text3 Keywords, Article3
.
.
.

So basically, this macro should loop through all those text files and create CSV file in this format. Please note that "Article body" is text containing multiple sentences. I guess it should be under "".

Please let me know if this is clear. i would really appreciate your help.

 

[diddi]

this is just a simple starting point to give you a bit of an idea

Sub TxtToCSV()
    open "C:\Output.txt" for output as #1
    open "C:\Textfile.txt" for input as #3
    res=""
    while not eof(3)
        line input #3,sdata
        if len(sdata)>18 then
            res=res & chr(9) & sdata
        end if
    wend
    print #1,res
    close
end sub

 

[diddi]

make a userform with a listbox1, label1 and commandbutton2 on it

put this code in the userform code area:

Dim sFiles() As String, row As Long, SelFolder As String

Private Sub CommandButton2_Click()
    DoConvert
End Sub

Sub GetFolder()
    ListBox1.Clear
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select your folder of text files"
        .ButtonName = "Open"
        .Show
        SelFolder = .SelectedItems.Item(1) & "\"
    End With
    
    sFiles = AllFiles(SelFolder)

    For row = 0 To UBound(sFiles)
        If Right(sFiles(row), 4) = Right(ComboBox1.List(ComboBox1.ListIndex, 1), 4) Then
            ListBox1.AddItem sFiles(row)
        End If
    Next row
    Select Case ListBox1.ListCount
        Case 0
            Label1.Caption = "Didnt find any files - choose another folder"
            CommandButton2.Visible = False
        Case Is > 1
            Label1.Caption = "Found these " & ListBox1.ListCount & " files..."
            CommandButton2.Visible = True
        Case 1
            Label1.Caption = "Found just this 1 file..."
            CommandButton2.Visible = True
    End Select
End Sub

Function AllFiles(ByVal FullPath As String) As String()
    Dim oFs As New FileSystemObject
    Dim sAns() As String
    Dim oFolder As Folder
    Dim oFile As File
    Dim lElement As Long
    
    Label1.Caption = "Searching for files..."
    DoEvents
    Application.Cursor = xlWait
    ReDim sAns(0) As String
    If oFs.FolderExists(FullPath) Then
        Set oFolder = oFs.GetFolder(FullPath)
     
        For Each oFile In oFolder.Files
          lElement = IIf(sAns(0) = "", 0, lElement + 1)
          ReDim Preserve sAns(lElement) As String
          sAns(lElement) = oFile.Name
        Next
    End If

    AllFiles = sAns
    Application.Cursor = xlDefault
    
ErrHandler:
    Set oFs = Nothing
    Set oFolder = Nothing
    Set oFile = Nothing
End Function

 

[neobavesten]

Many thanks for quick solutions. I just implemented the second one.
When I run the macro, userform is displayed in the spreadsheet.

I Clicked on Ccommandbutton and got the following error: "Sub or Function not Defined" pointing to the following piece of code.

Any help?

Private Sub UserForm_Click()
Dim sFiles() As String, row As Long, SelFolder As String

Private Sub CommandButton2_Click()
    DoConvert
End Sub

 

[diddi]

now add another commandbutton1

Private Sub CommandButton1_Click()
    GetFolder
End Sub

commandbutton1 is the one that get its going