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

[ebs17]

I believe above the second step is taken before the first step. I heard the statement that you want to SEE the data.
To do this, you would link the text file as a table in the frontend:

DoCmd.TransferText acLinkDelim, ...

In many cases I prefer to make this link directly in a query, which has the advantage that access only lasts as long as the query is used and the link does not have to be deleted when the action is completed.

SELECT T.*
FROM [Text;DSN=NameSpezification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;DATABASE=C:\temp\].[datei01.csv] AS T

This query can be used as an SQL statement or as a saved query.
In order to view and further work, it is extremely important that the text file is read correctly. Settings for separators, data types, regional settings, code page, etc. can be made via a specification or alternatively via a Schema.ini.
Only when I see the table data correctly and the way I need it can I think about follow-up actions such as recordsets and action queries.

After you have the opportunity to see the data directly in your Access environment, the question arises as to what exactly you want to do afterwards. With a usable table, almost anything is possible.

@Gasman
dbOpenDynaset makes no sense on a text file; writing actions via Edit or AddNew are not possible.

 

[CJ_London]

still some issues with insert into statement

use the normal debugging procedures

dim sqlStr as string
sqlStr="INSERT INTO import SELECT * FROM (SELECT * FROM [TEXT;DATABASE=" & fpath & ";HDR=Yes]." & fname & ") AS txt"
debug.print sqlStr
'now copy/paste the sql string from the immediate window into a new query and view it/execute it to test

'If really stuck, debug.print the openrecordset sql, copy to a new query, check it works then modify to an insert query, then view the sql to check the syntax

db.Execute (sqlStr)




not related but

1. if you are declaring db then you should be using
Set db = CurrentDb
Set rs = db.OpenRecordset(.....

2. No need to open the recordset and movelast if you are not going to do anything with it, you just need the insert code

EDIT:

The ISERT INTO Statement has following unknown fieldnames
IBAN; Auszusnummer; and so on

I do have in my IMPORT table all those fields and also those fields are given in the csv file.

check the spelling and the order

 

[Gasman]

Sorry Gasman what do you mean?
I did not say that anyone gave me this code.

Just was trying out.

Ok, you cannot throw a filename into an openrecordset like that. You either need a domain name or a select statement, like the one that follows.

 

[silentwolf]

Hi guys,
many thanks to you all for helping me and giving me good tips and hints!
I will go through those and get back to you!

Really appreciate your help!

Cheers

 

[ebs17]

Consecutive numbering could be added directly to the CSV.
- You read the contents of the CSV into an ADODB recordset. Query about this:

SELECT 0 AS SeqNum, T.*
FROM [Text;DSN=NameSpezification;FMT=Delimited;HDR=yes;IMEX=2;CharacterSet=850;DATABASE=C:\temp\].[datei01.csv]

- Then you do a one-time run through the recordset and number SeqNum. This is much faster than the DCount method mentioned above.
- The third step is to jump to the beginning of the recordset using MoveFirst and use the Getstring method to create a string from the entire contents of the recordset. This string, now with consecutive numbering, is now written back into the CSV or into a copy of it.

 

[Pat Hartman]

You may already have what you need so feel free to ignore this post. I am only posting because I have a process that imports bank files and thought you might want to see what it includes. Just for clarity, this particular app was used to process bank accounts that were tied to gift cards and refunds that were issued to new customers. Most states have rules on what happens to uncashed refunds and gift cards and they confiscate the money. It doesn't revert to the issuing company So, aside from importing the bank data, the app made monthly reports for all the states in which we did business and figured out how much money we needed to send them in unclaimed rewards.
Background:
1. the default file locations are stored in a table and are used to populate the form below. The user can change the locations if their process changes and that will change them permanently since this form is bound.
2. The user has to log onto the bank files monthly and download the .pdf files. There are three separate accounts. The files are downloaded to a local folder named ....\Output.
3. The user starts the OCR tool which converts the .PDF's to .XLXS files
4. The user then opens the Access app to the form "Import Bank Statement" and presses the Import button
5. That starts the process which is the code below.

Code Summary:
. validate the input folder and set the FSO object to the folder
. Loop through the files in the folder and for each file
....Make sure the files are imported in sequence.
....Create log record to generate batch ID
.....GoSub - handles the details of each file process which is pretty complicated and irrelevant to this example so you don't need to read it
....Update the log with the import record count

To answer the question in the mind of some of our pickier experts - a lot of the queries use DoCmd.OpenQuery to run update queries. Why? they all took parameters and so I decided to write one line of code for each rather than a half dozen. Call it laziness. that also meant I needed to turn warnings off and then on again to supress the error messages. I do that with macros because I turn the hourglass on when I turn warnings off so that I have the super annoying hourglass in my face if when I'm testing, I stop the code and manage to leave the warnings off. It is seriously dangerous to leave warnings off and so I use the macro because I can just go click the warnings on macro to turn them back on.

There is code I deleted which copies the empty template file I use to hold the bank data while I process it and before I append it to the permanent tables. This is a way to prevent bloat by not having the temp tables in the FE or BE. they are in a separate database that is replaced every month with an empty copy at the start of the import process. If you want a sample for that, I can upload one.

Private Sub cmdImport_Click()

    ''' requires reference to Microsoft Scripting Runtime Library
  
    Dim db                  As DAO.Database
    Dim td                  As DAO.TableDef
    Dim rs                  As DAO.Recordset
    Dim strProcessed        As String
    Dim fs                  As Scripting.FileSystemObject
    Dim folder              As Scripting.folder
    Dim file                As Scripting.file
    Dim filefolder
    Dim ToFolderName        As String
    Dim CountBankInput      As Long
    Dim CountChecksBefore   As Long
    Dim CountChecksAfter    As Long
    Dim FileCounter         As Long
    Dim FileCount           As Long
    Dim FileName            As String
    Dim LastImport          As Variant
    Dim HoldAsOfDT          As Date
    Dim HoldBankAcctNumber  As String
    Dim HoldSourceFileName  As String
    Dim HoldSourcePathName  As String
    Dim strUpdateSQL        As String
  
    On Error GoTo Err_Proc

    DoCmd.RunMacro "mWarningsOff"  (macro turns warnings off and hourglass on for safety)
    If Me.txtFolder & "" = "" Then
        MsgBox "Please select a folder to import.", vbOKOnly
        Exit Sub
    End If
  
    Set db = CurrentDb()
    Set td = db.TableDefs!tblImportLog
    Set rs = td.OpenRecordset
  
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set folder = fs.GetFolder(Me.txtFolder)
    Set filefolder = folder.Files 
  
    FileCounter = 0
    FileCount = folder.Files.Count
  
    For Each file In filefolder
        If file.Type = "Microsoft Excel Worksheet" Then
            FileCounter = FileCounter + 1
            FileName = folder.Path
            If Right(FileName, 1) = "\" Then
                FileName = FileName & file.Name
            Else
                FileName = FileName & "\" & file.Name
            End If
            HoldSourceFileName = Mid(FileName, InStrRev(FileName, "\") + 1)
            HoldSourcePathName = Left(FileName, InStrRev(FileName, "\"))
            HoldAsOfDT = CDate(Left(HoldSourceFileName, 4) & "/" & Mid(HoldSourceFileName, 5, 2) & "/" & Mid(HoldSourceFileName, 7, 2))
            HoldBankAcctNumber = Mid(HoldSourceFileName, 10, 9)
          
            ''' make sure that files are imported in sequence.
            LastImport = DMax("SourceFileName", "tblImportLog", "BankAcctNumber = '" & HoldBankAcctNumber & "'")
            If file.Name > LastImport Or IsNull(LastImport) Then
            Else
                If file.Name = DLookup("SourceFileName", "tblImportLog", "SourceFileName = '" & file.Name & "'") Then
                    MsgBox "This file has already been imported.  Please remove it from the import folder and start again.", vbOKOnly
                    Exit Sub
                Else
                    MsgBox "This file name is from a prior period.  Please have the programmer validate the data before continuing.", vbOKOnly
                    Exit Sub
                End If
            End If
            Me.txtFile = FileName
            DoEvents
            Me.Repaint
            rs.AddNew
                rs!SourceFileName = HoldSourceFileName
                rs!SourcePathName = HoldSourcePathName
                rs!AsOfDT = HoldAsOfDT
                rs!BankAcctNumber = HoldBankAcctNumber
                rs!ImportDT = Date
                Me.txtBatchID = rs!BatchID            'will not work with SQL Server (need to switch to @@Identity for that)
              
''''''''''  Bug in Access is preventing the query from "seeing" the txtBatchID field to add it to records so I had to use a TempVar
                TempVars.Add "tBatchID", Me.txtBatchID.Value
              
            rs.Update
          
            GoSub FileProcess
            strUpdateSQL = "Update tblImportLog Set importRecCount = " & DCount("*", "tblBankInput", "BatchID = " & Me.txtBatchID)
            strUpdateSQL = strUpdateSQL & ", CheckRecCount = " & DCount("*", "tblChecks", "BatchID = " & Me.txtBatchID)
            strUpdateSQL = strUpdateSQL & " WHERE BatchID = " & Me.txtBatchID
      
            DoCmd.RunSQL strUpdateSQL

        End If
    Next

Exit_Proc:
  
    DoCmd.OpenQuery "qUpdtCustInfo"
    MsgBox "Complete " & FileCounter & " files imported.", vbOKOnly
    DoCmd.OpenQuery "qBankSummary"
  
    DoCmd.RunMacro "mWarningsOn" (turns warnings back on and hourglass off)
    Exit Sub
  
FileProcess:
    strProcessed = Me.txtFolderOut     ' Replace(Me.txtFile, "\Output\", "\Processed\")
    ToFolderName = Left(strProcessed, InStrRev(strProcessed, "\"))
  
    CountBankInput = 0
    CountChecksBefore = 0
    CountChecksAfter = 0
'empty work tables
    DoCmd.OpenQuery "qDelSheet1"
'import spreadsheet
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Sheet1", Me.txtFile, False
'append to work table
    DoCmd.OpenQuery "qAppFirstColumns"
    DoCmd.OpenQuery "qAppSecondColumns"
    CountBankInput = DCount("*", "tblBankInput")
    CountChecksBefore = DCount("*", "tblChecks")
'append new items to final table
    DoCmd.OpenQuery "qAppChecks"
    CountChecksAfter = DCount("*", "tblChecks")
'update existing items in final table
    DoCmd.OpenQuery "qUpdChecks_CD1"
    DoCmd.OpenQuery "qUpdChecks_CD2"
    DoCmd.OpenQuery "qUpdChecks_CD3"
    DoCmd.OpenQuery "qUpdChecks_CD4"
    DoCmd.OpenQuery "qUpdChecks_CD5"
    DoCmd.OpenQuery "qUpdChecks_CDNone"

        fs.CopyFile Source:=Me.txtFile, Destination:=ToFolderName
        Kill Me.txtFile

    Return
Err_Proc:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Exit_Proc
            Resume
    End Select
End Sub

 

[silentwolf]

Hi guys,

many thanks for your help! To clearify things to me!
Unfortuantelly the next few days I will not be able to process all your help but will do a soon I can and get back to you!

However I do have just one question which would be in my case the first step what needs to be done.

The bank statements are named all with certain beginnings like "umsaetze-girokonto_IBAN_Day-Mont-Year-Time.csv".
So they include "-" which is not a valid name in Access so if I process those original files I run into problems..

Therefore I like to rename those files all in a certain structure.
"12345_JanToMar_2023.csv" which I get from the last 5 Digits of the IBAN, lowest Month of the file and the Highest Month of the File and the Year of that periode.

Also an validation incase that is not even the right file I included in my case checking the header row. If the header row has all the field names in the correct order and names it is the correct file and can be processed.
That validation I would like to have as some are older files and have been renamed already either manually or via code.
As I like to have it better structured I like to rename all in the same structure and then delete duplicate files and exclude those for my import.

Again all of that was writen in open workbook and check for the header row, check lowest and highest Date and IBAN Number.

As you guys mentioned that there could be an issue of doing it like that I was wondering how it could be done without facing any problems?

Many thanks

Albert

 

[CJ_London]

Use the name command

name oldname as newname

don’t think your new name is very good - the display order will be all over the place. Suggest

2023_01__04

 

[Pat Hartman]

So they include "-" which is not a valid name in Access so if I process those original files I run into problems..

What problems? A file name is just data. I've only run into two situations where file names are a problem.
1. File names with multiple "." such as abc.eef.csv
3. File names with specific extensions that windows determines are dangerous such as .dat and .exe.

 

[silentwolf]

Hi guys,
@CJ_London
well I would like to include the BankName so to speak so maybe it needs to be like 2023_01_04_12345.csv Would that be ok?

And what do I supose to use like can I somehow open the file to check the dates and soforth as discriped withouth any issue?

@Pat,
well when I have included a file name with "-" then it tells me for the import that it is not a valid file name so to speak not sure if that is the case all the time but I did get this kind of error before.
This was the case when I tried to import that file with the original name straight into access.

But perhabs is also a problem as the file name is in the original name about 60 plus characters.

 

[Pat Hartman]

There is a circular problem. If Access objects to the file name, you can't open the file using TransferText to examine it to pull out the data you need to rename it. That would mean that you would need to rename the file to some intermediary name, link it using TransferText, get the start/end months, close it and rename it again.

Even if TransferText objects to the file name, you might still be able to use the File Open method and use VBA to loop through the file record by record rather than as a linked table using SQL.

 

[silentwolf]

Sorry Pat,
I am a little lost right now

You would not have an example of how to get the data out of the csv file as requiered by any chance?

Cheers

 

[ebs17]

FileCopy statement (VBA)

Office VBA reference topic

learn.microsoft.com

Name statement (VBA)

Office VBA reference topic

learn.microsoft.com

You can do this immediately when you first access the file (file selection dialog, loop in the directory).

 

[silentwolf]

Hi Eberhard,

thank you! I will try that many thanks!

Cheers

 

[CJ_London]

well I would like to include the BankName so to speak

I was only showing the date part - include a bank name either before or after (I would go for before)

As far as a data process is concerned, it depends on your situation.

I have one client, they download a csv file from their bank at the end of the month and as and when required. The process there is

1. download file from bank
2. using FSO, they select the file from the download folder
3. the app then copies the file from the download folder to another 'import' folder with a standard name e.g. 'BankStatement.csv', replacing whatever
4. the app then (using the sql method I outlined above) imports the data, ignoring any records already imported (when matched on date, detail, amount and balance - very slight risk here of duplicates, but in 12 years with this client, never been a problem)
5. once successfully imported, the file is then copied to an archive folder

Another client has 5 bank accounts which are auto downloaded daily. For them the process is similar except steps 1 and 2 are replaced with process to loop through the download folder, typically identifying a file by bank name and date then continuing from step 3.

There is a further refinement, each bank can have a different format - additional columns, different column names, a different column order, different currency and date formats, identity rows at the top for example. So the sql a) has to modify the criteria to exclude the identity rows, b) sort out the names and order and c) do some data type conversion.

This is handled in a meta table to construct the sql in VBA as required for each bank. I also set HDR=No which gets around the problem of illegal field names. If you do this then the columns are automatically named F1, F2 etc. So for one Bank I would assign F1 to transactionDate, for another it might be F2. The meta table looks like this

PK....BankFK....destFld.....impFld....conversion
1............1...........tranDate.....F1
2............1...........detail............F6
3............2...........trandate......F2
4............2............detail...........F5

 

[silentwolf]

Thanks CJ,
I will look into that..

Cheers!

 

[silentwolf]

Hi guys,

I am back and trying to work out CSV Import.
First question still remains why I can not import a File with a name like following:
"umsaetze_girokonto_AT221111011010110011_EUR_2021_10_13_08_17_32.csv"

If I run this Code below In the Imidate window "Call ImportCSVFile("umsaetze_girokonto_AT221111011010110011_EUR_2021_10_13_08_17_32.csv")
I get a runtime Error of 3011
If the filename is "TestCSV.csv") it works.
Therefore I thought of renaming the file as mentioned in previous posts.

Public Sub ImportCSVFile(FileName As String)
    
    DoCmd.TransferText acImportDelim, "Volksbank Import Spezification", "VBImport", _
        FileName, True, , 1252
        
End Sub

Also for the sake of knowing which CSV File it is I wanted to rename it but can't seam to find a way of doing it as jet.

Cheers

Albert

 

[Gasman]

And runtime error 3011 is?
You do need the FULL path for the file?

 

[silentwolf]

Hi Gasman,
as soon as I use a filename as mentioned I get the same error.

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

Error

"C:\Users\Albert\Documents\AJL\CSV_Daten\Test\01-03_2016.csv"

Works

So what could the issue be? The error says can't find the file but the file exists is correct writen but it always runs into the same Error if a file is with similar name.

 

[Gasman]

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.