如何在CSV开头删除NUL字符串；VBA [英] How to remove NUL strings at begining of CSV; VBA

问题描述

我有一个导出物料清单的程序(请参见后文中的CSV示例数据).

I have a program that exports a bill of material (see CSV example data lower in post).

我编写了一个VBA宏，该宏使用FileSystemObject遍历用户选择的文件夹，打开CSV文件以进行读取，并使用ReadAll将数据读取到内存中，然后在excel中存储和排序数据.

I wrote a VBA macro that uses FileSystemObject to loop through a user-selected folder, open the CSV files for reading and read the data into memory using ReadAll, then store and sort the data in excel.

我的问题是导出CSV文件后，我运行了宏，并且CSV文件中的文本存储在我的字符串变量(以下代码中的sTemp)中，为多个方括号.我复制了字符串变量的值，并将其粘贴到文本编辑器中，它显示为几个重复的包含"SOH"的黑框.由于数据读取不正确，因此宏在下游失败.

The problem I have is after exporting the CSV files, I run the macro and the text in the CSV file is stored in my string variable (sTemp in the code below) as several square braces. I copied the value of the string variable and pasted it in a text editor and it appears as several repeating black boxes containing "SOH". Because the data is read incorrectly, the macro fails downstream.

如果我打开原始CSV文件，则前两个字符是黑盒子，上面写着"NUL".NUL NUL样式"，数量"，尺寸"，材料"，描述"，长度(ft)"2041 ,,"3"，"，"316L不锈钢Sch 10S PE"，202041 ,,"3"，"，"316L不锈钢Sch 10S PE"，2.215044,1，"3"，不锈钢:304L"，"DET S10 SS BUTT WELD"，2523,1，"3"，不锈钢:316L"，"316L-SS-SCH10短半径90"，2522,1，"3"，不锈钢:304L"，"DET S10 SS BUTT WELD"，

If I open the original CSV file, the first two characters are black boxes that say "NUL". NUL NUL "Pattern","Qty","Size","Material","Description","Length (ft)" 2041,,"3","","316L Stainless Sch 10S PE",20 2041,,"3","","316L Stainless Sch 10S PE",2.21 5044,1,"3","Stainless Steel: 304L","DET S10 SS BUTT WELD", 2523,1,"3","Stainless Steel: 316L","316L-SS-SCH10 Short Radius 90", 2522,1,"3","Stainless Steel: 304L","DET S10 SS BUTT WELD",

如果我将CSV文件保存在Excel中将其关闭，则两个"NUL"字符将消失:样式"，数量"，尺寸"，材料"，描述"，长度(英尺)"2041 ,,"3"，"，"316L不锈钢Sch 10S PE"，202041 ,,"3"，"，"316L不锈钢Sch 10S PE"，2.215044,1，"3"，不锈钢:304L"，"DET S10 SS BUTT WELD"，2523,1，"3"，不锈钢:316L"，"316L-SS-SCH10短半径90"，2522,1，"3"，不锈钢:304L"，"DET S10 SS BUTT WELD"，

If I save the CSV files in Excel close them, the two "NUL" characters are gone: "Pattern","Qty","Size","Material","Description","Length (ft)" 2041,,"3","","316L Stainless Sch 10S PE",20 2041,,"3","","316L Stainless Sch 10S PE",2.21 5044,1,"3","Stainless Steel: 304L","DET S10 SS BUTT WELD", 2523,1,"3","Stainless Steel: 316L","316L-SS-SCH10 Short Radius 90", 2522,1,"3","Stainless Steel: 304L","DET S10 SS BUTT WELD",

在保存的文件上运行宏之后，字符串变量(以下代码中的sTemp)在CSV文件中包含正确的文本.

After running the macro on the saved files, the string variable (sTemp in the code below) contains the proper text in the CSV file.

我想知道如何在CSV开头消除NUL字符串，并尽可能避免以编程方式打开和保存CSV文件.

I would like to know how to eliminate the NUL strings in the beginning of the CSV and would like to avoid opening and saving the CSV files programmatically if at all possible.

'Variables for accessing and cleaning text files
Dim oFSO As FileSystemObject 'File System Object grants access to computer files and folders
Dim sSourceFolder As String 'Path of oSourceFolder
Dim oSourceFolder 'Folder the original CSV file is stored in
Dim oFile 'The orignal CSV
Dim sFileToCopy As String 'Path of the copied CSV
Dim sTargetFolder As String 'The path of oTargetFolder
Dim oTargetFolder 'The folder the new CSV file is stored in
Dim oCopiedText 'The copied text file
Dim oCopiedFile 'The copied file itself
Dim oInput 'Represents the text in the CSV ror readin in CSV to memory
Dim sTemp As String 'For storing CSV data in memory
Dim cFiles As New Collection 'Storage for all the files in all the sub folders
Dim sPath As String 'The path of one of the files, will be reused for each file

'variables for progress
Dim iFiles As Integer
Dim Progress As Double

'Constants for reading and writing to text files
Const ForReading = 1
Const ForWriting = 2

'File System Object grants access to computer files and folders
Set oFSO = CreateObject("Scripting.FileSystemObject")

'Select the folder to get CSV files from
sSourceFolder = GetFolder("C:\", "Select Folder Containing CSV files")
Set oSourceFolder = oFSO.GetFolder(sSourceFolder) 'Tell File System Object to get a hold of oSourceFolder so you can look into it

'Check/create the _Cleaned folder inside of the source folder
On Error Resume Next
Err.Clear
sTargetFolder = oSourceFolder.Path & "\" & oSourceFolder.Name & "_Cleaned"
Set oTargetFolder = oFSO.GetFolder(sTargetFolder)
If Err.Number <> 0 Then
    'create the folder
    oTargetFolder = oFSO.CreateFolder(sTargetFolder)
End If
On Error GoTo 0

'Loop through and clean each CSV so it can be opened in excel properly
iFiles = oSourceFolder.Files.Count
Progress = 0

For Each oFile In oSourceFolder.Files 'go through each file
    Application.StatusBar = "Progress: " & Progress & " of " & CStr(iFiles) & ": " & Format(Progress / iFiles, "0%")
    If Right(oFile, 3) = "csv" Then   'if it is a text file...
        'Copy the original file path
        sFileToCopy = oFile.Path

        'Open txt file in memory
        Set oInput = oFSO.OpenTextFile(sFileToCopy, ForReading)

        'Input txt from file to memory
        sTemp = oInput.ReadAll
        'sTemp contains a repeating SOH string if I do not save the files first and the macro fails downstream
        'If I open the CSV file in excel, save it, close it, then run the macro, sTemp contains the string data in the file and the macro runs fine

'我现在可以以某种方式删除NUL字符串吗?..

'Can I somehow delete the NUL strings at this point? . . .

我希望sTemp值能够读取CSV文件中的实际文本:样式，数量，大小，材料，描述，长度(英尺)2041，，3,316L不锈钢Sch 10S PE，20"

I expect the sTemp value to read the actual text in the CSV file: "Pattern,Qty,Size,Material,Description,Length (ft) 2041,,3,,316L Stainless Sch 10S PE,20"

相反，VBA Locals窗口中的sTemp值为:"[[[[[[[[[[[[[[[[[[[[当我复制该值并将其粘贴到文本编辑器中时，它显示为:"1SOH SOH SOH SOH SOH SOH"

Instead the sTemp value in the VBA locals window is: "[[[[[[[[[[[[[[[[[[[[[[[[" And when I copy the value and paste it into a text editor it reads: "1 SOH SOH SOH SOH SOH SOH"

文件保存在此处: https://www.dropbox.com/sh/e8ohn61bxqidejd/AAB9CMno8N_EXdlA83TBX602a?dl = 0 .

感谢您的帮助.

推荐答案

您确实有一个文件的前两个字符为ASCI 0.

You indead do have a file with the first two characters as ASCI 0.

看到两个主要选项:

1. 修复创建这些文件的导出文件，以不生成NULL，或者
2. 将开头的NULL字符剥离.

对于option2，似乎ReadAll失败，但是ReadLine可以工作.

For option2, it seems ReadAll fails, but ReadLine works.

剥离NULL的演示

Sub Demo()
    Dim oFSO As FileSystemObject
    Dim oInput As TextStream
    Dim sTemp

    Set oFSO = New FileSystemObject
    Set oInput = oFSO.OpenTextFile("Your\File.csv")
    sTemp = oInput.ReadLine
    Do While Left$(sTemp, 1) = Chr(0)
        sTemp = Mid$(sTemp, 2)
    Loop
    Do Until oInput.AtEndOfStream
        sTemp = sTemp & vbCrLf & oInput.ReadLine
    Loop
End Sub

这篇关于如何在CSV开头删除NUL字符串；VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！