使用VBScript拆分CSV列数据并生成新CSV文件的技术需求

Got it, let's build a VBScript that does exactly what you need—no modifications to the original CSV, just a new output file with each unique value from your two columns duplicated across two columns.

Here's the full script: #

Option Explicit

' Configure your file paths here
Dim strInputFile, strOutputFile
strInputFile = "C:\path\to\your\original.csv" ' Replace with your input CSV path
strOutputFile = "C:\path\to\your\new_output.csv" ' Replace with your desired output path

Dim fso, objInputFile, objOutputFile, strLine, arrColumns, uniqueValues, value
Set fso = CreateObject("Scripting.FileSystemObject")

' Check if input file exists
If Not fso.FileExists(strInputFile) Then
    WScript.Echo "Error: Input file not found at " & strInputFile
    WScript.Quit 1
End If

' Initialize dictionary to store unique values (avoids duplicates)
Set uniqueValues = CreateObject("Scripting.Dictionary")
uniqueValues.CompareMode = vbTextCompare ' Case-insensitive comparison

' Read input file and collect all values from the two columns
Set objInputFile = fso.OpenTextFile(strInputFile, 1) ' 1 = Read mode

' Skip the header line (remove this line if your input CSV has no header)
strLine = objInputFile.ReadLine

Do Until objInputFile.AtEndOfStream
    strLine = objInputFile.ReadLine
    arrColumns = Split(strLine, ",") ' Split line by comma (adjust if your CSV uses a different delimiter)
    
    ' Add value from column 1 (index 0) if it's not empty
    If UBound(arrColumns) >= 0 And Trim(arrColumns(0)) <> "" Then
        value = Trim(arrColumns(0))
        If Not uniqueValues.Exists(value) Then
            uniqueValues.Add value, value
        End If
    End If
    
    ' Add value from column 2 (index 1) if it's not empty
    If UBound(arrColumns) >= 1 And Trim(arrColumns(1)) <> "" Then
        value = Trim(arrColumns(1))
        If Not uniqueValues.Exists(value) Then
            uniqueValues.Add value, value
        End If
    End If
Loop
objInputFile.Close

' Create and write to the output file
Set objOutputFile = fso.CreateTextFile(strOutputFile, True) ' True = Overwrite existing file

' Write the header line (matches your expected output)
objOutputFile.WriteLine "列1 列2" ' Use "列1,列2" if you want standard CSV comma delimiter

' Write each unique value duplicated across the two columns
For Each value In uniqueValues.Keys
    objOutputFile.WriteLine value & " " & value ' Use value & "," & value for comma-separated CSV
Next

objOutputFile.Close

' Cleanup objects
Set objInputFile = Nothing
Set objOutputFile = Nothing
Set uniqueValues = Nothing
Set fso = Nothing

WScript.Echo "Success! Output file created at " & strOutputFile

How this works: #

1. File Path Configuration: Update strInputFile and strOutputFile with your actual file locations.
2. Input Validation: Checks if the original CSV exists before proceeding to avoid errors.
3. Unique Value Collection: Uses a dictionary to gather all non-empty values from both columns, ensuring no duplicates even if a value appears multiple times in the original file.
4. Output Generation: Creates the new CSV, writes the header, then loops through each unique value to write a line where the value is repeated in both columns.
5. Delimiter Flexibility: The script uses spaces as the delimiter to match your expected output. If you need standard comma-separated CSV, just replace the space delimiters in the write lines with commas (comments in the script point out exactly where to adjust this).

Example Output: #

If your original CSV has:

列1,列2
Reptiles,Purity
Health,
Hygiene,

The output file will look exactly like you requested:

列1 列2
Reptiles Reptiles
Health Health
Hygiene Hygiene
Purity Purity

内容的提问来源于stack exchange，提问作者Ecinev