Open and update Excel Workbook and saveas Question (1 Viewer)

[silentwolf]

I would calculate the length of the name and shorten it in increments until it works.
Their might be a limit to full pathname.
Put it higher up the folder structure first with same name and try that.

Thanks,
unfortunatelly same issue when I put it into a higher folder .( to reduce the path and file name

 

[ebs17]

"C:\Users\Albert\Documents\AJL\CSV_Daten\Test\umsaetze_girokonto_AT221111011010110011_EUR_2021_10_13_08_17_32.csv"

Are you able to capture the full path shown via code?
At this moment you would be able to read the IBAN and timestamp from the file name (you would certainly need such information for a complete import including documentation) and then immediately provide the file with a usable path and name using Name/FileCopy and thus to start the import.

 

[silentwolf]

Hi Eberhard,

Are you able to capture the full path shown via code?

Not really sure what you mean with that sorry?

At present I get the fullpath and filename with this procedure

Private Sub btnBrowse_Click()
    Dim diag As Office.FileDialog
    Dim item As Variant
    
    Set diag = Application.FileDialog(msoFileDialogFilePicker)
    
    With diag
        .AllowMultiSelect = False
        .Title = "Excel oder CSV-Datei auswählen"
        .Filters.Clear
        .Filters.Add "Excel Spreadsheets", "*.xls,*.xlsx,*.csv"
        .FilterIndex = 2
        
        .InitialFileName = p_cstrCSVTestDir 'Verzeichnis Konstante noch ändern
    
        If .Show And .SelectedItems.Count > 0 Then
            Me!txtFilePath.Value = .SelectedItems.item(1)
        End If
    End With
    
    Set diag = Nothing

End Sub

I would not mind beeing able to rename the file according to what the file content is but I am not able to get any information on the net via text file.

Only found this function on MrExcel.com but forgot to save the link
To get or retrieve the first line of the text file.

Function GetFirstLine(MyFile As String) As String
    If Dir(MyFile) <> "" Then
        Open MyFile For Input As #1
            Line Input #1, GetFirstLine
        Close
    End If
End Function

Sub TestValidationFirstLine()
    Dim strFullName As String
    Dim strFirstLine As String
    
    strFullName = "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\11222_10_12NurTest.csv"
    strFirstLine = GetFirstLine(strFullName)
    
    If strFirstLine = "IBAN;Auszugsnummer;Buchungsdatum;Valutadatum;Umsatzzeit;Zahlungsreferenz;Waehrung;Betrag;Buchungstext;Umsatztext" Then
        Debug.Print strFirstLine
    Else
        Debug.Print "False Data"
    End If

End Sub

This code above is just for testing purpose to check if the first line of the textfile is correct and can proceed if it is correct.

But can't find anything as yet to get data from csv out from a certain column so I am able to get the datefrom and dateTo of that file.

I guess I just have to rename each file manually and save it to a new location to be able to savely import it to access.

At this moment you would be able to read the ISBN and timestamp from the file name

ISBN oop not sure .. never used it or not sure what or where I get that information from the file?

 

[spaLOGICng]

Hi again guys!
it hast been a while and I hope all of you are very well.
However now I am back in the game and like to work on following where I like to get some advice please.

I have a form where I can either open an CSV File in Excel.. for viewing purpose.
Update and Save the file into new location ("in progress "
What I like to know is is it possible to open the file via code and update that file save it to new location but keep the opend file?

So if I browse for the file and I need to update and save that file into a new location after it has been updated will the original file be lost?
If yes then I guess I need to create a copy of that file first save it to a new location and after that I will have to modify that new file to suit?

Or can I just open one file update it, saveas "newName.csv" to new location and let the original untoucht?

Many thanks for any advice of how you guys going about that in your projects.

Cheers

Albert

Yes.

In VBA you can use the SaveCopyAs method under the Workbook Object. Yous simply provide the the Filename that will include the folder path where you want to save it to.

This saves the Workbook/CSV as a duplicate. The current opened version is not affected in any way.

 

[Pat Hartman]

I would not mind beeing able to rename the file according to what the file content is but I am not able to get any information on the net via text file.

The VBA is
Open "filename" For Input as FileNum

Input statement (VBA) | Microsoft Learn

Since the file you are working with is a .csv, you can load each row as you read it into an array and look at the data in the specific column you want to examine.

 

[ebs17]

ISBN was a misnomer that I quickly corrected.
AT221111011010110011 - I think this is the IBAN (bank and account number).

The other thing you want to do now is unclear to me: Above, an import via SQL/Recordset or TransferText or TransferSpreadsheet was possible. But something like this requires standard structures within the CSV.

Now you want to look at the first line of content. So the structure seems to be anything but standardized; the name of the file does not guarantee expected and immediately usable content.

You still have no overview or concept.

 

[silentwolf]

Hi guys,
thanks for your replies!
@ebs17

AT221111011010110011 - I think this is the IBAN (bank and account number).

yes it is.

Now you want to look at the first line of content. So the structure seems to be anything but standardized; the name of the file does not guarantee expected and immediately usable content.

The Bank Statements have for the first line of content always a certain structure.
IBAN; Auszugsnummer; Buchungsdatum; Valutadatum; Umsatzzeit; Zahlungsreferenz; Waehrung; Betrag; Buchungstext; Umsatztext

You are right that maybe if someone has modified that file in any way there could be an issue.
But it is is from the original Download so to speak than the file is in the same structure.

In the Column of Buchungsdatum I would like to find lowest Date and Highes Date, currently Code for workbookopen and looping through the Column,
As you guys mentioned that is not the savest way I was trying to do that with the InputStatement, but still working on it and not just yet able to do so.

You still have no overview or concept.

My concept was open workbook and check for the first line of content. Check for the Account which is the last five digits of the IBAN,
Check for the lowestDate and highestDate in Buchungsdatum, and the Year of that file again from either the lowest or highest Date Value in Buchungsdatum.

Build a filename according to that information and save it to "MyFiles/2023/12345/12345_01To10_2023.csv" for example.

Once that has been done.
Import the files with transferText into my Database into Table IMPORT.

ImportSpecifications for that matter have been created and are able to work once the fileName is someting like 2023_01_09_12345.csv
or 12345_2023_01_09.csv or which ever sequence it would be.

If a file has been renamed and is saves already with a different name. I would like to just view the file first and if it is valid file then do the above and save it to the right location and the right file name.

@spaLOGICng

In VBA you can use the SaveCopyAs method under the Workbook Object. Yous simply provide the the Filename that will include the folder path where you want to save it to.

This saves the Workbook/CSV as a duplicate. The current opened version is not affected in any way.

Ok that is great but again there still remains the issue of getting the fileName I like to get out of the File content of the IBAN " & "Dates

@CJ_London

I looked at the imput function
Currently just able to do the first line of content with code but not to sure how to get the dates out of it

 

[ebs17]

umsaetze_girokonto_AT221111011010110011_EUR_2021_10_13_08_17_32.csv

The name of the original file is so meaningful that you don't have to look into the file because of its contents. Account statements are created automatically and therefore in a standardized manner. I can expect the same structures from the same sender.

Build a filename according to that information and save it to "MyFiles/2023/12345/12345_01To10_2023.csv" for example.

I don't really understand the renaming of files. I would import the data into my database and do all my overviews and evaluations there. Account statements remain as they are. They are read once and then stored.

My understanding of an automated process would be expressed in code as follows. New CSV would have to be moved to the examined folder beforehand.

Sub Import_XX()
    Dim db As DAO.Database
    Dim sPath As String
    Dim sFile As String
    Dim sSQL As String

    Set db = CurrentDb
    sPath = "X:\Anywhere\Importfiles\"

    sFile = Dir(sPath & "*pattern*.csv")
    Do While sFile > vbNullString
        'Debug.Print sFile
        ' extracting informations from the file name
        ' FileCopy, Rename ??
        sSQL = "INSERT INTO TargetTable (Feld1, Feld2, Feld3)" & _
            " SELECT T.Feld1, T.Feld2, T.Feld3" & _
            " FROM [Text;DSN=NameSpecification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;" & _
            "DATABASE=" & sPath & "].[" & sFile & "] AS T"
      
        db.Execute sSQL, dbFailOnError

        ' document processing, move, delete file
      
        sFile = Dir
    Loop
    Set db = Nothing
End Sub

The Dir loop can handle patterns. This allows you to select specific files for the same processing.

 

[silentwolf]

Hi Eberhard,
many thanks for your explaination and how you would do it!
I will look into it and let you know I it went!

Cheers!

Albert

 

[ebs17]

By the way: Since the CSV is read via query, you could read out a minimum and maximum date for other purposes.

 

[silentwolf]

You mean once it is importet in access?

 

[ebs17]

You can read the CSV using a select query before appending. You can also read the target table after import. I'm just talking about options.

 

[silentwolf]

Ok Thanks Eberhard!

Here is what I have tried for now...
There is a issue with

Sub InsertIntoStatements()
    Dim db As DAO.Database
    Dim sPath As String
    Dim sFile As String
    Dim sArchive As String
    Dim sSQL As String
 
    Set db = CurrentDb
    sPath = "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\Umsaetze\"

    sFile = Dir(sPath & "umsaetze*.csv")
    
    Do While sFile > vbNullString
        'Debug.Print sFile
        ' extracting informations from the file name
        ' FileCopy, Rename ??
        
        sSQL = "INSERT INTO VBImport ( IBAN, Auszugsnummer, Buchungsdatum, Valutadatum, Umsatzzeit, Zahlungsreferenz, Waehrung, Betrag, Buchungstext, Umsatztext ) " & _
        "SELECT VBImport.IBAN, VBImport.Auszugsnummer, VBImport.Buchungsdatum, VBImport.Valutadatum, VBImport.Umsatzzeit, VBImport.Zahlungsreferenz, VBImport.Waehrung, VBImport.Betrag, VBImport.Buchungstext, VBImport.Umsatztext " & _
        "FROM [Text;DSN=Volksbank Import Spezification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;" & _
                "DATABASE=" & sPath & "].[" & sFile & "] AS T"
                
        db.Execute sSQL, dbFailOnError
        
        sFile = Dir
    Loop
    
    Set db = Nothing

End Sub

Here I get an Error 3061

10 parameters were expected, but too few parameters were passed

So I counted all the parameters and I do have all 10 fields in the table..

Also what I am not sure from the above is the FROM Clause.

"FROM [Text;

does that need to be changed?

Cheers

 

[silentwolf]

Sorry spotet the problem!

        sSQL = "INSERT INTO VBImport ( IBAN, Auszugsnummer, Buchungsdatum, Valutadatum, Umsatzzeit, Zahlungsreferenz, Waehrung, Betrag, Buchungstext, Umsatztext ) " & _
        "SELECT VBImport.IBAN, VBImport.Auszugsnummer, VBImport.Buchungsdatum, VBImport.Valutadatum, VBImport.Umsatzzeit, VBImport.Zahlungsreferenz, VBImport.Waehrung, VBImport.Betrag, VBImport.Buchungstext, VBImport.Umsatztext " & _
        "FROM [Text;DSN=Volksbank Import Spezification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;" & _
                "DATABASE=" & sPath & "].[" & sFile & "] AS VBImport"

However the code runs without any issues.. but when I open the VBImport Table... not all Data gets inserted correctly..

So perhaps the Specification is an issue? But not exactly sure how to spot it

 

[CJ_London]

not all Data gets inserted correctly

what does that mean exactly?

if if this is a csv file, perhaps try my suggestion in post#14 - no need to provide a DSN, IMEX or characterset

 

[silentwolf]

Hi CJ,

what does that mean exactly?

Some date gets inserted correctly.. some other with "" if a field is empty or on the last field Umsatztest it starts like "Some text"
And on some there is no Date Values in the Valutadatum or Buchungsdatum ("Datum = Date

I will look at your post again see if I can manage ) get back to you!

Cheers

 

[CJ_London]

Some date gets inserted correctly.. some other with "" if a field

sounds like this is being treated as text, not a date field - so convert to a date using cDate

on the last field Umsatztest it starts like "Some text"

don't know what that means

And on some there is no Date Values in the Valutadatum or Buchungsdatum ("Datum = Date

does that mean in the csv file? or the destination table

 

[silentwolf]

Hi CJ,

Public Sub UsingDao01()
    Dim rs As DAO.Recordset
    Dim fpath As String
    Dim fname As String
    
    fpath = "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles\"
    fname = "TestDaten.csv"

    CurrentDb.Execute ("INSERT INTO IMPORT SELECT * FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")
    
End Sub

Public Sub UsingDao02()
    Dim rs As DAO.Recordset
    Dim fpath As String
    Dim fname As String
    
    fpath = "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles\"
    fname = "TestDaten.csv"

    CurrentDb.Execute ("INSERT INTO IMPORT (IBAN, Auszugsnummer, Buchungsdatum, Valutadatum, Umsatzzeit, Zahlungsreferenz, Waehrung, Betrag, Buchungstext, Umsatztext) SELECT IBAN, Auszugsnummer, Buchungsdatum, Valutadatum, Umsatzzeit, Zahlungsreferenz, Waehrung, Betrag, Buchungstext, Umsatztext FROM (SELECT * FROM  [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ")  AS txt")
    
End Sub

If I run UsingDoa01, I get an runtime Error of 3127 Fieldnames not recongnized...

If I run UsingDao02, I get an runtime Eirror of 3061.. 10 Parameters...

don't know what that means

Just ment that the Text shows with " at the beginning of the Text in the Umsatztext field
Or if one field is empy on Zahlungsreferenz it shows "" so double double quote

sounds like this is being treated as text, not a date field - so convert to a date using cDate

Where do I have to convert it?

does that mean in the csv file? or the destination table

It means in the Destination Table..

 

[ebs17]

#54:
It's not very clever, and probably more prone to errors, if you give the source table the same name as the target table.
There was a reason why I gave the long expression for the source table a short, succinct alias. Of course you also have to use the alias.

not all Data gets inserted correctly..

Then an error message should appear again with a meaningful message, of course with the first error detected. There seems to be a whole catalog of atrocities.
dbFailOnError ... hould actually rollback in addition to returning a message, so nothing is appended. (I'm really surprised by some of the descriptions.)

Errors are easy to detect in an append query. In order for something like this to work completely, you may
- no data type errors occur,
- no index errors occur,
- no violations of validity rules occur,
- no violations of the set referential integrity occur.

Instead of guessing and wandering around: Maybe you feel able to upload an example DB with a table and import specification as well as a problematic CSV here, of course sufficiently anonymized, but please not distorting the meaning.

 

[silentwolf]

Hi Eberhard,
sorry I guess there is a missunderstanding..
I just got one Table called IMPORT in my Access Database.
And the source is just the folder where I got my CSV Files located..

i.E.
fpath = "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles\"

in that path I got files starting with umsaetze-girokontoXXXXX.csv

With your code

well modified for my needs it looks like this

Sub InsertIntoStatementsNeu01()
    Dim db As DAO.Database
    Dim sPath As String
    Dim sFile As String
    Dim sArchive As String
    Dim sSQL As String
 
    Set db = CurrentDb
    sPath = "C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles\"

    sFile = Dir(sPath & "umsaetze*.csv")
    
    Do While sFile > vbNullString
        'Debug.Print sFile
        ' extracting informations from the file name
        ' FileCopy, Rename ??
        
        sSQL = "INSERT INTO IMPORT ( IBAN, Auszugsnummer, Buchungsdatum, Valutadatum, Umsatzzeit, Zahlungsreferenz, Waehrung, Betrag, Buchungstext, Umsatztext ) " & _
        
"SELECT IMPORT.IBAN, IMPORT.Auszugsnummer, IMPORT.Buchungsdatum, IMPORT.Valutadatum, IMPORT.Umsatzzeit, IMPORT.Zahlungsreferenz, IMPORT.Waehrung, IMPORT.Betrag, IMPORT.Buchungstext, IMPORT.Umsatztext " & _
        
"FROM [Text;DSN=Volksbank Import Spezification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;" & _
                "DATABASE=" & sPath & "].[" & sFile & "] AS IMPORT"
                
        db.Execute sSQL, dbFailOnError
        
        sFile = Dir
    Loop
    
    Set db = Nothing

End Sub

I am sure there is a issue how I use the SELECT and the FROM Statement
The specification is Called "Volksbank Import Spezification"

When I run the above code it gets all files from that folder but only some of those files are getting inportet correctly.. I have checked the files but all seam with the same structure..

Instead of guessing and wandering around: Maybe you feel able to upload an example DB with a table and import specification as well as a problematic CSV here, of course sufficiently anonymized, but please not distorting the meaning.

Ok I will try to put something together..

Many thanks