如何通过正则表达式在VBS中将空白/''转为vbNull处理CSV导入？

Absolutely! You can absolutely get this done in VBScript—no need to panic about regex if it feels intimidating. I’ll walk you through two approaches: a dead-simple non-regex method (perfect for beginners) and a regex-based one if you need more flexibility for edge cases.

This is the easiest way to handle empty strings, blank spaces, and whitespace-only values. We’ll use VBScript’s built-in Trim() function to strip leading/trailing whitespace, then check if the result is an empty string.

Here’s a reusable function:

Function CleanCSVValue(value)
    ' Strip leading/trailing whitespace (spaces, tabs, etc.)
    Dim trimmedValue
    trimmedValue = Trim(value)
    
    ' If the trimmed value is empty, return vbNull; otherwise return the original value
    If trimmedValue = "" Then
        CleanCSVValue = vbNull
    Else
        CleanCSVValue = value
    End If
End Function

How it works: #

• Trim(value) removes all leading and trailing whitespace characters (spaces, tabs, carriage returns, etc.)
• If the original value was "" (empty string), " " (just spaces), or even " " (tabs), Trim() will turn it into "", triggering the switch to vbNull.

If you need to handle more complex whitespace scenarios (like strings with only newlines or mixed whitespace), regex can help. This method matches any string that consists entirely of whitespace (or is empty) and converts it to vbNull.

Here’s the regex-powered function:

Function CleanCSVValueWithRegex(value)
    Dim regex
    Set regex = New RegExp
    
    ' Regex pattern: matches strings that are empty OR only contain whitespace
    regex.Pattern = "^[\s]*$"
    regex.Global = True ' Not strictly necessary here, but good practice for regex objects
    
    ' Test if the value matches the pattern
    If regex.Test(value) Then
        CleanCSVValueWithRegex = vbNull
    Else
        CleanCSVValueWithRegex = value
    End If
End Function

Regex Breakdown (No Fear!) #

Let’s break down ^[\s]*$ so it makes sense:

• ^: Matches the start of the string
• [\s]*: Matches zero or more whitespace characters (spaces, tabs, newlines, etc.)
• $: Matches the end of the string

This means any string that’s either empty or made up entirely of whitespace will trigger the conversion to vbNull.

To use these functions with your CSV file, you’ll read each line, split it into cells, process each cell, then write the cleaned data back. Here’s a basic example (note: this works for simple CSVs—if your file has commas inside quoted fields, you’ll need a more robust CSV parser):

Dim fso, inputFile, outputFile, line, cells
Set fso = CreateObject("Scripting.FileSystemObject")

' Open input and output files
Set inputFile = fso.OpenTextFile("your_input.csv", 1) ' 1 = Read-only mode
Set outputFile = fso.CreateTextFile("cleaned_output.csv", True) ' True = Overwrite existing file

' Process each line of the CSV
Do Until inputFile.AtEndOfStream
    line = inputFile.ReadLine
    cells = Split(line, ",") ' Split line into individual cells
    
    ' Clean each cell using your chosen function
    For i = 0 To UBound(cells)
        cells(i) = CleanCSVValue(cells(i)) ' Swap with CleanCSVValueWithRegex if needed
    Next
    
    ' Write the cleaned line to the output CSV
    ' Note: If your database expects a specific null representation, adjust this line
    outputFile.WriteLine Join(cells, ",")
Loop

' Clean up objects
inputFile.Close
outputFile.Close
Set fso = Nothing

Quick Note: #

If your CSV contains quoted fields with commas (e.g., "Doe, John",30), the simple Split(line, ",") will break those fields. For that scenario, you’ll want to use a dedicated CSV parsing function, but that’s a separate topic!

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