FileSystemObject error when the file names has special letters

[Isaac]

Hello All

I'm not bad on Microsoft Access but I am quite new on VBA codes.

I spent almost 24 hours to get solution below.

I have several csv files I am importing them to table with bellowing code:

Dim FSO As Object, objFolder As Object, objFile As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder(Me.MB_Klasor_Konumu)


For Each objFile In objFolder.Files
  If Right(objFile.Name, 3) = "csv" Then

    DoCmd.TransferText acImportDelim, "MB3", "MB_Sheet_Ham", objFolder & "\" & objFile.Name, False

  End If
  i = i
Next objFile

Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing

It does work but doesn't work with if files has some special letter like "İ, Ş, Ğ, Ö,Ü, Ç"

I don't know what I should do?

I research for rename files before import but I couldn't found the solution, maybe there is a else trick ?

Is there someone to help me about?

Thank you in advance.

What line errs?

 

[MajP]

Here is the issue. Whatever that character at the very end of the name VERI it is extremely strange. If you look at it, it is clearly not an "I" since there is something over the I. However if you read each character it says it is Character 73 which is an uppercase "I". I took one file and replaced that character with an I and compared to another reading character by character and printing their ascii code.

B1_14__VERI_.csv
B 66
1 49
_ 95
1 49
4 52
_ 95
_ 95
V 86
E 69
R 82
I 73  'It is an I
_ 95
_ 95
. 46
c 99
s 115
v 118

B1_17__VERI.csv
B 66
1 49
_ 95
1 49
7 55
_ 95
_ 95
V 86
E 69
R 82
I 73  'It says "I" but it is not
. 46
c 99
s 115
v 118

The issue is it cannot be found or replaced. It thinks it is an I but it is not. It is not one to the I variants. I tested my updated replace function with this file
B1-15 VERIÍÌÎÏií.csv
and it worked fine.

So the easy solution is to rename it to whatever you want or copy it. You can choose to delete or not.
Public Sub test2()

Dim FSO As FileSystemObject, objFolder As Object, objFile As Scripting.File
Dim OldName As String
Dim NewName As String
Dim FolderPath As String
Dim I As Integer
Dim j As Integer
Set FSO = CreateObject("Scripting.FileSystemObject")


Set FSO = New FileSystemObject
'Set objFile = New Scripting.File
On Error GoTo errlog
FolderPath = CurrentProject.Path & "\" & "Sample CSV Files"
Set objFolder = FSO.GetFolder(FolderPath)
For Each objFile In objFolder.Files

  If Right(objFile.Name, 3) = "csv" Then
    OldName = objFile.Name
    j = j + 1
   
    NewName = "Import_" & Format(Date, "yyyymmdd") & "_" & j & ".csv"
   
   ' For I = 1 To Len(NewName)
   '   Debug.Print (Mid(NewName, I, 1)) & " " & Asc(Mid(NewName, I, 1))
   ' Next I
    Debug.Print OldName & " " & NewName
    Debug.Print
    'Choose to copy
    FSO.CopyFile FolderPath & "\" & OldName, FolderPath & "\" & NewName
    'choose to rename
    'FSO.MoveFile FolderPath & "\" & OldName, FolderPath & "\" & NewName
    DoCmd.TransferText acImportDelim, "MB3", "MB_Sheet_Ham", FolderPath & "\" & NewName, False
    FSO.DeleteFile FolderPath & "\" & NewName
  End If
Next objFile
Exit Sub
errlog:
  Debug.Print Err.Number & " " & Err.Description & " new name: " & NewName
  Resume Next
Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing
End Sub

Here is my expanded replace function. (the looping gave me some good ideas)

Public Function ReplaceInternationalCharacters(ByVal strText As String) As String
    Dim I As Integer
    ReplaceInternationalCharacters = Replace(strText, "Ö", "O")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ü", "U")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ç", "C")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ý", "I")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Þ", "S")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ð", "G")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ö", "O")
    For I = 204 To 207
     If InStr(ReplaceInternationalCharacters, Chr(I)) > 0 Then MsgBox Chr(I)
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(I), "I")
    Next I
    For I = 236 To 239
      If InStr(ReplaceInternationalCharacters, Chr(I)) > 0 Then MsgBox Chr(I)
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(I), "i")
    Next I
    For I = 0 To 45
      If InStr(ReplaceInternationalCharacters, Chr(I)) > 0 Then MsgBox Chr(I)
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(I), "_")
    Next I
    For I = 213 To 223
      If InStr(ReplaceInternationalCharacters, Chr(I)) > 0 Then MsgBox Chr(I)
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(I), "_")
    Next I
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(75), "I")
End Function

I would be curious for anyone to look at this. File 14 is a standard I and all others have the thing over the top.

 

[Isaac]

Here is the issue. Whatever that character at the very end of the name VERI it is extremely strange. If you look at it, it is clearly not an "I" since there is something over the I. However if you read each character it says it is Character 73 which is an uppercase "I". I took one file and replaced that character with an I and compared to another reading character by character and printing their ascii code.

B1_14__VERI_.csv
B 66
1 49
_ 95
1 49
4 52
_ 95
_ 95
V 86
E 69
R 82
I 73  'It is an I
_ 95
_ 95
. 46
c 99
s 115
v 118

B1_17__VERI.csv
B 66
1 49
_ 95
1 49
7 55
_ 95
_ 95
V 86
E 69
R 82
I 73  'It says "I" but it is not
. 46
c 99
s 115
v 118

The issue is it cannot be found or replaced. It thinks it is an I but it is not. It is not one to the I variants. I tested my updated replace function with this file
B1-15 VERIÍÌÎÏií.csv
and it worked fine.

So the easy solution is to rename it to whatever you want or copy it. You can choose to delete or not.
Public Sub test2()

Dim FSO As FileSystemObject, objFolder As Object, objFile As Scripting.File
Dim OldName As String
Dim NewName As String
Dim FolderPath As String
Dim I As Integer
Dim j As Integer
Set FSO = CreateObject("Scripting.FileSystemObject")


Set FSO = New FileSystemObject
'Set objFile = New Scripting.File
On Error GoTo errlog
FolderPath = CurrentProject.Path & "\" & "Sample CSV Files"
Set objFolder = FSO.GetFolder(FolderPath)
For Each objFile In objFolder.Files

  If Right(objFile.Name, 3) = "csv" Then
    OldName = objFile.Name
    j = j + 1
  
    NewName = "Import_" & Format(Date, "yyyymmdd") & "_" & j & ".csv"
  
   ' For I = 1 To Len(NewName)
   '   Debug.Print (Mid(NewName, I, 1)) & " " & Asc(Mid(NewName, I, 1))
   ' Next I
    Debug.Print OldName & " " & NewName
    Debug.Print
    'Choose to copy
    FSO.CopyFile FolderPath & "\" & OldName, FolderPath & "\" & NewName
    'choose to rename
    'FSO.MoveFile FolderPath & "\" & OldName, FolderPath & "\" & NewName
    DoCmd.TransferText acImportDelim, "MB3", "MB_Sheet_Ham", FolderPath & "\" & NewName, False
    FSO.DeleteFile FolderPath & "\" & NewName
  End If
Next objFile
Exit Sub
errlog:
  Debug.Print Err.Number & " " & Err.Description & " new name: " & NewName
  Resume Next
Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing
End Sub

Here is my expanded replace function. (the looping gave me some good ideas)

Public Function ReplaceInternationalCharacters(ByVal strText As String) As String
    Dim I As Integer
    ReplaceInternationalCharacters = Replace(strText, "Ö", "O")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ü", "U")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ç", "C")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ý", "I")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Þ", "S")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ð", "G")
    ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, "Ö", "O")
    For I = 204 To 207
     If InStr(ReplaceInternationalCharacters, Chr(I)) > 0 Then MsgBox Chr(I)
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(I), "I")
    Next I
    For I = 236 To 239
      If InStr(ReplaceInternationalCharacters, Chr(I)) > 0 Then MsgBox Chr(I)
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(I), "i")
    Next I
    For I = 0 To 45
      If InStr(ReplaceInternationalCharacters, Chr(I)) > 0 Then MsgBox Chr(I)
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(I), "_")
    Next I
    For I = 213 To 223
      If InStr(ReplaceInternationalCharacters, Chr(I)) > 0 Then MsgBox Chr(I)
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(I), "_")
    Next I
      ReplaceInternationalCharacters = Replace(ReplaceInternationalCharacters, Chr(75), "I")
End Function

I would be curious for anyone to look at this. File 14 is a standard I and all others have the thing over the top.

Thanks, I was interested in getting at the root cause.
Maybe the issue happened when the files were first placed in the folders; maybe the destination system couldn't handle the REAL character, and replaced it with random things.

I've been fighting a bit recently with Access-to-Sharepoint and certain characters being randomly replaced with the weird version of U's, I's, etc.
The U's and I's aren't the problem - they weren't really there to begin with. In my case, the section/subsection symbol was the original problem.

 

[MajP]

You can download post 13 and look at the file names. I tried zooming in on the file name to make it out. The I does not look like one of the international characters which are
204-207 and 236 - 239

International Character Table

It looks like an uppercase I with a single dot over it as best I can see.

 

[MajP]

And now that I said that I think I may have answered my own question. It is a unicode U+130

Find all Unicode Characters from Hieroglyphs to Dingbats – Unicode Compart

U+0130 is the unicode hex value of the character Latin Capital Letter I with Dot Above. Char U+0130, Encodings, HTML Entitys:İ,İ,İ, UTF-8 (hex), UTF-16 (hex), UTF-32 (hex)

www.compart.com

 

[The_Doc_Man]

Darn - beat me to that answer by 3 minutes. Good catch, MajP.

 

[MajP]

Yes that is the answer and @The_Doc_Man will have to explain it to me, because I do not know much about unicode
Added function

Public Function ReplaceUnicode(txt As String) As String
    Dim regEx As Object

    Set regEx = CreateObject("vbscript.regexp")
    regEx.Pattern = "[^\u0000-\u007F]"
    ReplaceUnicode = regEx.Replace(txt, "~")

End Function

So this works replacing the weird I with ~

Public Sub test()

Dim FSO As FileSystemObject, objFolder As Object, objFile As Scripting.File
Dim OldName As String
Dim NewName As String
Dim FolderPath As String
Dim I As Integer

Set FSO = CreateObject("Scripting.FileSystemObject")

Set FSO = New FileSystemObject
'Set objFile = New Scripting.File
On Error GoTo errlog
FolderPath = CurrentProject.Path & "\" & "Sample CSV Files"
Set objFolder = FSO.GetFolder(FolderPath)
For Each objFile In objFolder.Files
 
  If Right(objFile.Name, 3) = "csv" Then
    OldName = objFile.Name
    NewName = ReplaceInternationalCharacters(objFile.Name)
    NewName = ReplaceUnicode(NewName)
   ' For I = 1 To Len(NewName)
   '   Debug.Print (Mid(NewName, I, 1)) & " " & Asc(Mid(NewName, I, 1))
   ' Next I
    Debug.Print OldName & " " & NewName
    Debug.Print
    FSO.MoveFile FolderPath & "\" & OldName, FolderPath & "\" & NewName
    DoCmd.TransferText acImportDelim, "MB3", "MB_Sheet_Ham", FolderPath & "\" & NewName, False
  End If
Next objFile
Exit Sub
errlog:
  Debug.Print Err.Number & " " & Err.Description & " new name: " & NewName
  Resume Next
Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing
End Sub

 

[MajP]

So after chasing this for a long time, I learned that file names are in Unicode. Good to know. What is nice about that is you can make some real fun file names.

 

[Dreamweaver]

I have an example of a find and replace system Here

 

[MajP]

I have an example of a find and replace system Here

I get an error on download.

 

[Dreamweaver]

Sorry All my downloads are members only (You can thank those who came on my site and downloaded everything they could)

 

[Dreamweaver]

I'll PM You a copy if you want @MajP

 

[drakopp]

I'll PM You a copy if you want @MajP

How can I register on your site?

 

[Ocicek]

Dear @MajP , dear @theDBguy, dear @drakopp, dear @MickJav, dear @The_Doc_Man dear @Isaac Thank you for attend time for my case. I'm sorry to late to checking. Because of our time zone.

I'll be testing now.

 

[Dreamweaver]

I've updated the main page

 

[drakopp]

I've updated the main page

I got
Please contact the forum owner...4

 

[Ocicek]

Dear @MajP

I used what you posted. Bu I got Defination error FileSystemObject.

Dim FSO As FileSystemObject, objFolder As Object, objFile As Scripting.File (new code)

Dim FSO As Object, objFolder As Object, objFile As Object (old one)

What I did missing?

 

[Dreamweaver]

That don't look right objFile As Scripting.File (new code)

 

[The_Doc_Man]

@MajP - UNICODE simply has a way to map more than 256 characters. It is not uniform, however. Technically, UCS-2, UTF-8, UTF-16, and several other extensions are all UNICODE. I can explain it but this article probably does it better.

Unicode - Wikipedia

en.wikipedia.org

The SHORT answer is that you need to know two things to identify a character - (a) which scheme you are using and (b) the character and the byte or bytes that follow it.

 

[MajP]

That don't look right objFile As Scripting.File (new code

It is correct. For debugging purposes I did early binding and added a reference to FSO. You can turn that all back to Object.