Member Avatar for battlescar

I have a mass of files, all named 1.txt, 2.txt, etc. all the way up to 100.txt in the same directory (let's say C:\Example1) that have come from a website and are either of one of the two following formats:

"blah blah blah, 1234, blahblah" (Always 4 numbers, as it's a year)

or, alternatively:

"blah blah blah, 1234, blah blah"
" , , "

I'd like to convert them to one CSV format, using the commas inside the text files at the moment as the delimiters in the CSV file, with all of the data in the

  • 2 Contributors
  • 3 Replies
  • 203 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by codeorder

Dani AI

Generated

A concise clarification and a robust approach that complements 's quick rename idea.

The dataset here is a set of plain-text files that hold comma-separated pieces of data; some files use outer quotes and a few include a second line that is just comma placeholders. The simple conversion shown earlier only changes the extension and does not normalize fields, remove placeholder lines, or escape quotes for CSV import. A safer workflow is: enumerate the .txt files, read each non-empty line, strip an outer pair of quotes if present, skip lines that are only separators (only commas/whitespace), split on commas, trim each field, escape any internal double-quotes, wrap fields that need quoting, and write rows to a single CSV (or one CSV per file) using a StreamWriter.

Example VB.NET routine (adjust the folder paths as needed):

Imports System.IO

Module ConvertTxtToCsv
    Sub ConvertFolder(inputFolder As String, outputCsv As String)
        If Not Directory.Exists(inputFolder) Then
            Throw New DirectoryNotFoundException("Input folder not found: " & inputFolder)
        End If

        Dim files() As String = Directory.GetFiles(inputFolder, "*.txt")
        Using writer As New StreamWriter(outputCsv, False, System.Text.Encoding.UTF8)
            For Each f As String In files
                For Each rawLine As String In File.ReadAllLines(f)
                    Dim line As String = rawLine.Trim()
                    If String.IsNullOrWhiteSpace(line) Then Continue For

                    ' remove surrounding quotes if whole line is quoted
                    If line.Length >= 2 AndAlso line.StartsWith(Chr(34)) AndAlso line.EndsWith(Chr(34)) Then
                        line = line.Substring(1, line.Length - 2)
                    End If

                    ' skip lines that are only comma placeholders
                    If line.Replace(",", "").Trim().Length = 0 Then Continue For

                    Dim parts() As String = line.Split(","c)
                    For i As Integer = 0 To parts.Length - 1
                        parts(i) = parts(i).Trim()
                        If parts(i).Contains(Chr(34)) Then
                            parts(i) = parts(i).Replace(Chr(34), Chr(34) & Chr(34)) ' double quotes
                        End If
                        If parts(i).Contains(",") OrElse parts(i).Contains(Chr(34)) OrElse parts(i).Contains(ControlChars.Cr) OrElse parts(i).Contains(ControlChars.Lf) Then
                            parts(i) = Chr(34) & parts(i) & Chr(34)
                        End If
                    Next
                    writer.WriteLine(String.Join(",", parts))
                Next
            Next
        End Using
    End Sub

    Sub Main()
        Dim inputFolder As String = "C:\Example1" ' change as needed
        Dim outputCsv As String = Path.Combine(inputFolder, "Combined.csv")
        ConvertFolder(inputFolder, outputCsv)
    End Sub
End Module

Notes and troubleshooting: back up the source folder before running on all files. Test on a small subset first. Watch file encoding (BOM/ANSI/UTF‑8) when reading; modify the StreamReader/Writer encoding if necessary. If any fields legitimately contain embedded commas and complex quoting rules, consider a proper CSV parser (TextFieldParser or a dedicated CSV library) rather than a naive split. This preserves the intent of ’s quick method while adding the parsing, cleaning and CSV escaping needed for reliable imports.

Recommended Answers

All 3 Replies

Member Avatar for codeorder

See if this helps.

'// original .txt Folder with .txt Files.
        Dim myTXT_folder As String = "C:\tempTXT\"
        '// .csv Folder to save Files to.
        Dim myCSV_folder As String = "C:\tempCSV\"
        '// create a temporary TextBox.
        Dim tempTextBox As New TextBox With {.Multiline = True}
        '// loop thru the .txt Files Folder.
        For Each myTXT_File As String In My.Computer.FileSystem.GetFiles _
                                            (myTXT_folder, FileIO.SearchOption.SearchTopLevelOnly, "*.txt")
            '// load .txt File in your temporary TextBox.
            tempTextBox.Text = IO.File.ReadAllText(myTXT_File)
            '// save temporary TextBox text as new .csv File and keep the same File Name.
            IO.File.WriteAllText(myCSV_folder & IO.Path.GetFileNameWithoutExtension(myTXT_File) & ".CSV", tempTextBox.Text)
        Next
        MsgBox("Done.", MsgBoxStyle.Information) '// display confirmation when done.
        tempTextBox.Dispose() '// dispose of the temporary TextBox.
Member Avatar for battlescar

Does this code also load the data from C:\Example1, or will I need extra code to do that?(I don't know how to do this in VB.NET - I can do it in VB 6.0 no problem, but VB.NET is a whole new world to me)

Member Avatar for codeorder

My previously posted code will only "convert" the files from .txt to .csv in a specified folder.

The code loads each .txt file from a folder into a temporary TextBox and saves that loaded text with the same FileName and a new File extension, .csv in this case.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.