FileSystemObject error when the file names has special letters (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 01:21
Joined
Mar 14, 2017
Messages
8,738
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:

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

You've got your good things, and you've got mine.
Local time
Today, 04:21
Joined
May 21, 2018
Messages
8,463
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.


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()

Code:
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)

Code:
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

Lifelong Learner
Local time
Today, 01:21
Joined
Mar 14, 2017
Messages
8,738
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.


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()

Code:
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)

Code:
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've got your good things, and you've got mine.
Local time
Today, 04:21
Joined
May 21, 2018
Messages
8,463
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

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

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:21
Joined
May 21, 2018
Messages
8,463

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:21
Joined
Feb 28, 2001
Messages
27,001
Darn - beat me to that answer by 3 minutes. Good catch, MajP.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:21
Joined
May 21, 2018
Messages
8,463
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
Code:
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 ~

Code:
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

You've got your good things, and you've got mine.
Local time
Today, 04:21
Joined
May 21, 2018
Messages
8,463
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.
 

Attachments

  • ©ª«ÑÐɷɺʣϓϔϕЋЏВ֍֏۞۩.txt
    11 bytes · Views: 214
  • MoveAndLink.accdb
    736 KB · Views: 88

Dreamweaver

Well-known member
Local time
Today, 08:21
Joined
Nov 28, 2005
Messages
2,466
Sorry All my downloads are members only (You can thank those who came on my site and downloaded everything they could)
 

Ocicek

New member
Local time
Today, 11:21
Joined
Jan 19, 2021
Messages
25
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?
 

Attachments

  • Ekran Resmi 2021-01-20 18.25.20.png
    Ekran Resmi 2021-01-20 18.25.20.png
    248.7 KB · Views: 214
  • Ekran Resmi 2021-01-20 18.25.40.png
    Ekran Resmi 2021-01-20 18.25.40.png
    320 KB · Views: 215

Dreamweaver

Well-known member
Local time
Today, 08:21
Joined
Nov 28, 2005
Messages
2,466
That don't look right objFile As Scripting.File (new code)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:21
Joined
Feb 28, 2001
Messages
27,001
@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.


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

You've got your good things, and you've got mine.
Local time
Today, 04:21
Joined
May 21, 2018
Messages
8,463
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.
 

Users who are viewing this thread

Top Bottom