Change CSV FileName before Import (1 Viewer)

[silentwolf]

Hi guys,

I have a code to import CSV Files into my Database. All works well. However what I like to do is change the FileName or Update the FileName prior to import.

As some FileNames are not in the correct nameing convention for the import I was thinking of changing the FileName automatically and then import.

For Example.

The User picks a file with the Name: "C:\Documents\MyFile-01-12-12-2021.csv"
I can't import the file as there are characters not allowed to do so.

Is there a way to change the fileName prior to the import automatically?

When the user clicks on "btnDataImport" that it checks whether it is a valid name if it is then import else save as "newFileName" and close the Form.

Public Sub ImportCSVFile(FileName As String)
    On Error GoTo Err
    
    DoCmd.TransferText acImportDelim, "VBAuszug", TableName, FileName, True, , 1252
    
Exit_Import:
    Exit Sub
Err:
    MsgBox "Die Datei hat keinen gültigen Namen bitte Dateiname ändern!", vbCritical
    Resume Exit_Import

End Sub

Above just a simple code to import...

Hope that Error Handling is ok like that?

Is there a way I could check if the FileName is Valid for the import and if not than change it accordingly and perhabs saveAs "NewFileName" end close the form?

Or how would you go about it?

Cheers and hopefully I explained it correctly?

 

[Eugene-LS]

If I understand you correctly:

Spoiler: Code

Public Sub ImportCSVFile(FileName As String)
Dim sVal As String
    On Error GoTo Err
    sVal = GetFileNameByPath(FileName)
    If InStr(sVal, "-") > 0 Then
        sVal = Mid(FileName, 1, Len(FileName) - Len(sVal)) & "00NewName.csv"
        'Debug.Print sVal
        FileCopy FileName, sVal
        DoEvents
    Else
        sVal = FileName
    End If
   
'Import:
    DoCmd.TransferText acImportDelim, "VBAuszug", TableName, sVal, True, , 1252
   
Exit_Import:
    Exit Sub
Err:
    MsgBox "Die Datei hat keinen gultigen Namen bitte Dateiname andern!", vbCritical
    Resume Exit_Import

End Sub

Private Function GetFileNameByPath(varPath As Variant) As String
'Returns the name of the file (by the full path specified in the argument).
'--------------------------------------------------------------------
On Error GoTo GetFileNameByPath_Err
    GetFileNameByPath = Mid(varPath, InStrRev(varPath, "\") + 1)
    Exit Function

GetFileNameByPath_Err:
    GetFileNameByPath = "FilePach ERR#" & Err.Number
End Function

 

[silentwolf]

Hi thanks for your reply!

I will try that and get back to you!

Cheers!

 

[Gasman]

Someone on here turned me on to a neater way to get a filename from a path.

Dir(PathToAVeryLongPathAndFile)

Also returns nothing if path or file does not exist.[/code]

 

[arnelgp]

the dash "-" is a valid character on filename.

Public Sub ImportCSVFile(FileName As String)
    On Error GoTo Err
    
    'arnelgp
    'check if there are invalid characters in filename
    'offer an alternative, if refused, don't proceed
    'since it will error out anyway.
    FileName = fncNewFile(FileName)
    If Len(FileName) = 0 Then
        Exit Sub
    End If
    
    DoCmd.TransferText acImportDelim, "VBAuszug", TableName, FileName, True, , 1252
    
Exit_Import:
    Exit Sub
Err:
    MsgBox "Die Datei hat keinen gültigen Namen bitte Dateiname ändern!", vbCritical
    Resume Exit_Import

End Sub

Public Function fncNewFile(path As String) As String
Dim drive As String, folder As String, file As String, ext As String
Dim newName As String
fncNewFile = path
With WizHook
    .Key = 51488399
    Call .SplitPath(path, drive, folder, file, ext)
End With
newName = FriendlyName(file)
newName = drive & folder & newName & ext
If (newName <> path) Then
    fncNewFile = ""
    If MsgBox(path & " has illegal characters as filename." & vbCrLf & _
    "Do you want to use " & newName & " instead?", vbQuestion + vbYesNo) = vbYes Then
        fncNewFile = newName
    End If
End If
End Function


Public Function FriendlyName(pText As String) As String

    With CreateObject("VBScript.RegExp")
    
        .Pattern = "(\s|\\|/|<|>|\|\||\?|:)"
        .Global = True
        .IgnoreCase = True
        
        FriendlyName = .Replace(pText, "_")
        
    End With
    
End Function

 

[silentwolf]

Hi guys,

thanks for your replies!
I will test all those many thanks!!

Already been working on Eugene-LS and it is pretty good!
Will look at your other suggestions as well!

Cheers!!

 

[silentwolf]

Hi again,

just wondering if someone knows why a fileName like
"umsaetze-einkonto_YZ11111555555666665_EUR_2022-02-03_10-43-10.csv" is not a valid name?

there are not really invalid characters or is it due to the length of the filename?

Cheers

 

[Gasman]

Hi again,

just wondering if someone knows why a fileName like
"umsaetze-einkonto_YZ11111555555666665_EUR_2022-02-03_10-43-10.csv" is not a valid name?

there are not really invalid characters or is it due to the length of the filename?

Cheers

Works for me?

and opens in Excel no problem.

 

[silentwolf]

Hmm well yes it does open in excel but I like to pick that file and import into Access and then it jumps into the messagebox when I select that file.

Public Sub ImportCSVFile(FileName As String)
On Error GoTo Err

'arnelgp
'check if there are invalid characters in filename
'offer an alternative, if refused, don't proceed
'since it will error out anyway.
FileName = fncNewFile(FileName)
If Len(FileName) = 0 Then
Exit Sub
End If

DoCmd.TransferText acImportDelim, "VBAuszug", TableName, FileName, True, , 1252

Exit_Import:
Exit Sub
Err:
MsgBox "Die Datei hat keinen gültigen Namen bitte Dateiname ändern!", vbCritical
Resume Exit_Import

End Sub

so in that line marked in red.

if I pick some different file then it does run true the code as expected.

 

[Gasman]

Outputting the error number might help?
Along with the FileName ?

Hmm well yes it does open in excel but I like to pick that file and import into Access and then it jumps into the messagebox when I select that file.



so in that line marked in red.

if I pick some different file then it does run true the code as expected.

 

[silentwolf]

Hi,
the error number is 3011.

And the Filename is as mentioned above so I am not sure why it does that..

If I use a different filename e.g. "C:\MyFolder\10212_01_03_2021.csv" it works fine.

 

[Gasman]

And what is 3011 ?
I do not have to want to look up codes all the time.
I would walk through the code step by step if this was me. Interactively change the filename to be a charcter shorter or two?, yet I do not think file length is the issue.
You are also supplying the filename in one issue and full path for successful?

Remember we cannot see your system, we DO NOT know what you are doing.
So all we can go on is what you post, the more detail the better.

I would copy the file as various shorter names, less 1 char, less 2 etc.
Then put a breakpoint after you get the errant filename and change it in the debug window (or code) to one of the shorter names.

Alternatively put enough together and upload and see if anyone can replicate the program?
We have already established as far as windows (and Excel) is concerned, there s nothing wrong with the filename.

I decided to google 3011 and first link mentions NonAscii characters? I cannot see any such in what you posted, but still.
Another says file not found. So show fncNewFile code
Try a DIR(FileName) and see if that returns a name?

 

[silentwolf]

Sorry for the lack of information.

Was not indendet just thought it was clear and there was enough information.


My appologies!!

I did try as you mentioned with stepping through the code and I do have sucess when I reduced to FileName to 64 Characters.
In the FileName there is nothing strange about it just "umsaetze-meinkonto_XY123456789101111112_EUR_2021-02-03_10-43-10.csv"

They are always like that accept the Numbers might change of numbers depanding on which Account and what date download.


It would not be hard to do a saveAs after download with a correct Name but there is always room for forgetting or write it wrong,
so I was hoping to be able to have a Errorhandling in place to make sure the system won't crash.

After stepping through the code and changing the fileNames to 64 characters the import runs correctly.

And I am just using Arnelgp's Code

Public Sub ImportCSVFile(FileName As String, TableName As String)

    On Error GoTo Err

    

    'arnelgp

    'check if there are invalid characters in filename

    'offer an alternative, if refused, don't proceed

    'since it will error out anyway.

    FileName = fncNewFile(FileName)

    If Len(FileName) = 0 Then

        Exit Sub

    End If

    

    DoCmd.TransferText acImportDelim, "VBAuszug", TableName, FileName, True, , 1252

    

Exit_Import:

    Exit Sub

Err:

    Debug.Print Err.Number

    MsgBox "Die Datei hat keinen gültigen Namen bitte Dateiname ändern!", vbCritical

    Resume Exit_Import


End Sub


Public Function fncNewFile(path As String) As String

    Dim drive As String, folder As String, file As String, ext As String

    Dim newName As String

    fncNewFile = path

    With WizHook

        .Key = 51488399

        Call .SplitPath(path, drive, folder, file, ext)

    End With

    newName = FriendlyName(file)

    newName = drive & folder & newName & ext

    If (newName <> path) Then

        fncNewFile = ""

        If MsgBox(path & " has illegal characters as filename." & vbCrLf & _

        "Do you want to use " & newName & " instead?", vbQuestion + vbYesNo) = vbYes Then

            fncNewFile = newName

        End If

    End If

End Function


Public Function FriendlyName(pText As String) As String


    With CreateObject("VBScript.RegExp")

    

        .Pattern = "(\s|\\|/|<|>|\|\||\?|:)"

        .Global = True

        .IgnoreCase = True

        

        FriendlyName = .Replace(pText, "_")

        

    End With

    

End Function

And just a command button on the form to test it.

If the FileName ="umsaetze-meinkonto_XY123456789101111112_EUR_2021-02-03_10-43-10 - Kopie.csv"

I receive the Message from fncNewFile

Public Function fncNewFile(path As String) As String
    Dim drive As String, folder As String, file As String, ext As String
    Dim newName As String
    fncNewFile = path
    With WizHook
        .Key = 51488399
        Call .SplitPath(path, drive, folder, file, ext)
    End With
    newName = FriendlyName(file)
    newName = drive & folder & newName & ext
    If (newName <> path) Then
        fncNewFile = ""
        If MsgBox(path & " has illegal characters as filename." & vbCrLf & _
        "Do you want to use " & newName & " instead?", vbQuestion + vbYesNo) = vbYes Then
            fncNewFile = newName
        End If
    End If
End Function

So could it be that the issue lies in the lenght of the FileName?

Hope this is now better to understand?


And I am just calling the procedure in the button_click event.

Public Sub btnImport_Click()

    modImportCSV.ImportCSVFile Me!txtStatement.Value, "AUSZUG"
    
End Sub

 

[Gasman]

Ok, it would seem the length is a problem in some way.
Can you rename to less than 64 and still identify as you wish?
I must admit, it did not appear to be that long.

Edit: Just checked on computer and 65 characters, without any folders included

See the bottom of this link. https://social.msdn.microsoft.com/F...-of-filename-for-transfertext?forum=accessdev

So I would check the length of the FULL pathname and modify to suit.
I'd check that a long folder path with a short filename still longer than 64 causes the same problem.

 

[silentwolf]

hmm yes thanks for the link!

Can you rename to less than 64 and still identify as you wish?

Well I guess I need to lol

I guess it is not possible to rename and import it at the same time is it?

I mean if you pick a file with file Dialog and if it is invalid change the name and do the import?

Edit: Just checked on computer and 65 characters.

So it is 65 Characters and it worked on your maschine?

 

[Gasman]

No, I just created a file with that name and as it was .csv it opened in excel.
Yes, you can pick a file, check it's length, if more than 64,then do something with it, until it is no more than 64. Rename it, then continue with the rest of your code.

 

[silentwolf]

Oh ok,

So directly in the file Dialog? And then you can rename it automatically and then import it or how would you do that?

So you mean open the file dialog if the file name is to long then a messagebox letting the user know and then rename it?
Can you use the same File Dialog to open or does it need to be closed?

You would not have a sample code by any chance or explain how it would work?

 

[Gasman]

No, no code. I would work out the steps, then google for what I needed, and adapt.
Think about it, you have filenameA which is too long, so you rename it to FilenameB.
Then use FilenameB from then on, as filenameA no longer exists.

 

[arnelgp]

forget to Rename the file:

Public Function fncNewFile(path As String) As String
Dim drive As String, folder As String, file As String, ext As String
Dim newName As String
fncNewFile = path
With WizHook
    .Key = 51488399
    Call .SplitPath(path, drive, folder, file, ext)
End With
newName = FriendlyName(file)
newName = drive & folder & newName & ext
If (newName <> path) Then
    fncNewFile = ""
    If MsgBox(path & " has illegal characters as filename." & vbCrLf & _
    "Do you want to use " & newName & " instead?", vbQuestion + vbYesNo) = vbYes Then
        Name path As newName
        fncNewFile = newName
    End If
End If
End Function

 

[silentwolf]

Hi all,
thanks for reply and thanks for the code I will look into it in a short while )

Never seen the Wizhook "function" I will look into that too... found something already but need to study further..

Thanks for sharing arnelgp!