Code does not support certain characters such as full-width symbols or Chinese (1 Viewer)

[naobao]

Function ImportTxtFilesInFolder(folderPath As String, ByRef fileList As String) As Integer
' This function imports the contents of txt files in the specified folder into the Access table "tbl1"
' It returns the number of txt files and updates the fileList variable with a sorted list of file names

Dim fileName As String
Dim fileContent As String
Dim fileNumber As Integer
Dim splitContent() As String
Dim fileCount As Integer

' Ensure that the folder path ends with a backslash
If Right(folderPath, 1) <> "\" Then
folderPath = folderPath & "\"
End If

' Initialize the file count and file list
fileCount = 0
fileList = ""

' Get the first txt file
fileName = Dir(folderPath & "*.txt")

' Iterate through txt files in the folder
Do While fileName <> ""
' Increment the file count
fileCount = fileCount + 1

' Add the file name to the file list
If fileList <> "" Then
fileList = fileList & vbCrLf
End If
fileList = fileList & fileName

' Initialize file content and file number
fileContent = ""
fileNumber = 1

' Open the txt file
Open folderPath & fileName For Input As #1

' Read the txt file content, skipping the first line
Do While Not EOF(1)
Line Input #1, fileContent
If fileNumber > 1 Then
' Split the file content by tab
splitContent = Split(fileContent, vbTab)

' Check if the length of splitContent array is sufficient
If UBound(splitContent) >= 4 Then
' Insert the split content into the five columns of the Access table "ST_DATA"
CurrentDb.Execute "INSERT INTO ST_DATA (a_code, Accpac_code, item_code, [desc], Qty) VALUES (" & _
"'" & Replace(splitContent(0), "'", "''") & "', " & _
"'" & Replace(splitContent(1), "'", "''") & "', " & _
"'" & Replace(splitContent(2), "'", "''") & "', " & _
"'" & Replace(splitContent(3), "'", "''") & "', " & _
IIf(IsNumeric(splitContent(4)) And splitContent(4) <= 10000000, splitContent(4), "NULL") & ");"
Else
' Handle the case where the length of splitContent array is insufficient
MsgBox "File content format is incorrect! File name: " & fileName & vbCrLf & "File content: " & fileContent, vbExclamation
End If
End If
fileNumber = fileNumber + 1
Loop

' Close the file
Close #1

' Get the next txt file
fileName = Dir
Loop

' Sort the file list in descending order
SortDescending fileList

' Return the file count
ImportTxtFilesInFolder = fileCount
End Function

I use the above code to import content from some text files into a table, but it does not support certain characters such as full-width symbols or Chinese. How can I modify it?

 

[ebs17]

To import from standard text files you should use the TransferText method. In the import specification to be created, you can also set, among other things, the code page to be used.
I prefer linking the file using this method. As part of an append query from one table to another table, you can carry out additional operations in the same move, as happens for your fifth value.