Open and update Excel Workbook and saveas Question

[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

 

[ebs17]

The specification is Called "Volksbank Import Spezification"

I would generally avoid spaces and special characters because they can quickly become problematic.

First all tables are called VBImport, now Import => you didn't understand the principle of DIFFERENT naming.
Oh, your path is very rocky, and common sense should not be ignored at any point.

 

[silentwolf]

Yes it is called like that
I will change it to no spaces No problem!

Oh sorry that was just another test trying to work it out with a different table!

Oh, your path is very rocky, and common sense should not be ignored at any point.

Oh hmm ok

 

[CJ_London]

suggest upload a copy of your file. We are over 60 posts on something that is pretty straight foward. Most of the issues appear to be you do not understand what you are being told.

As previously suggested, start with the basics using the sql window, not VBA

best guess it would be

SELECT * FROM (SELECT *
FROM [Text;DSN=Volksbank Import Spezification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;DATABASE=C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles\].[TestDaten.csv ]) AS T

or my version

SELECT * FROM (SELECT * FROM  [TEXT;DATABASE=C:\Users\Albert\Documents\AJL\CSV_Daten\Test\MyTestFiles;HDR=Yes].TestDaten.csv)  AS T

execute it - do you see the required data?

if not, make a note of what is wrong - dates or numbers as text? extra rows?

now go to the query view (forget the sql view for now)

add in criteria to remove rows not required, use CDate of similar functions to convert text to numeric values etc. Every time you make a change, run the query to ensure you get the intended result.

Once you are happy you have the right data, convert the query to an append query and append the appropriate fields

execute the query - does it work as intended?

if not, delete the rows in the destination table, modify your query and run again.

keep doing this until you get the required result.

Now you can go back to the sql view to see what your query looks like, copy and paste to vba and do the required modifications to the string to replace path and table name with your constants/whatever.

use debug.print to compare the sql produced with the original query you created in the query builder. If it is exactly the same (subject to path and file name) you should be good to go the execute the query.

This process should only take you a few minutes to get right.

 

[CJ_London]

following on from my previous post, this is an example of one of the files I import (displayed in excel so I can hide the sensitive data)

this is the sql that imports the file (path removed)

In green is the basic select query having resolved datatypes

In Yellow - HDR=no - this defaults field headings to F1, F2, etc so I can eliminate the unwanted rows in the WHERE statement

I left join to the destination table to prevent duplicates

 

[silentwolf]

Hi CJ_London,

Sorry that this is going on for a long time!

But there are just a few things that just don't work the right way.

I found the issue or where the problems are:

Attached are pictures of what the issues are.
First of all I can not create a External Data Import if the filename is the original Filename Attachment : "ErrorMessage_Originalfile"
Basically it tells me that the file can not be found.. check the file name or location.....

However so I for testing purposes I did create somefiles with a different names just so I can create an import specification and import that Data.

Some of the files even they are exactly the same structure and are "original" Files! They show in the Specification a double Quote so I created two different Specifications.. one Called "SpcecificationWithoutTextqualifier" one "SpecificationWithTextQualifier" .

It imports but get an import error for some of the Date Values.. those values are then not imported.

For the code it does work it does get all files within a folder and import those even with the original filename such as
"umsaetze-girokont_
with the original name and import those into the Import Table.

So I can not understand why some files show a double quote for the texqualifier and some not.
And how I could overcome this issue.

For your guys Input I did understand it but was not able to find the issue es mentioned above.

Cheers

 

[ebs17]

Regarding the second image: A saved import is created. This is something SIGNIFICANTLY DIFFERENT than importing via TransferText or SQL, as shown above.
The name of a saved import is also different than the name of an import specification.

Otherwise, your picture book shows little of what you REALLY do.
Apparently you jump into every puddle you can reach. Please don't act like importing from a CSV is a big deal. Something like that is basic, every popular program should be able to do something like that.