automated import data from other file

[stefanocps]

Hello i want to build an automation that would let me import data from another file
The file is a bank report, so it has several column
First i need to filter what records to i mport: looking at the money amount i should import all records that have a - before the amount. Like -100. -120, -70 ecc
Then i must be able to decide to what sheet import and if there are already data to append them at the end

Is that possible?
thank you

 

[arnelgp]

is there a way to identify the uniqueness of each row on your excel?
if none, how can you prevent duplicate imports?

 

[stefanocps]

it is a bank report, there are 4 column: date1, date2,ampount,description..it's difficult that duplicate come..not impossible though.
also it depend on the wise of who import..
when import the montly (and this is what normally happen) report no problem..each month has its own sheet; problem can occur if importing 2 or 3 times during a month. In that case he willto make sure that will import what has not been imported already

 

[CJ_London]

Surprised it does not include a balance column as well

if it did then you can use a left join to the destination table on all fields to exclude records already imported. Not 100% foolproof since you could get a sequence where the balance is the same because of an entry between the two entries negates the first transaction e.g.

date1......date2....detail...amt... bal
1/1/23....1/1/23...desc1...-50...-50
1/1/23....1/1/23...desc2...+50...0
1/1/23....1/1/23...desc1...-50...-50

but this can be detected by by using a count of duplicates (across all fields). You would need to inspect this count to determine whether it is a genuine duplicate or not

rather than left joining all the fields, you can concatenate them together for this inspection - perhaps to create string like this

1/1/23;1/1/23;desc1;-50;-50

But to answer your question, I think you would need to use sql in Excel to do the imports, so yes, it's possible

 

[stefanocps]

i don't know how to do it!

for the report, i must ask for original one, i have one file but i am not sure it is original or it is in some way edited already

 

[CJ_London]

have one file but i am not sure it is original or it is in some way edited already

You really need to know that - always better to use the original. If someone is editing it - get them to add a unique ID (for all time, not just that specific file)

I envisage your process would be something like

1. open the source file
2. inspect for duplicates and resolve
3. filter to exclude negative values
4. compare the data with the destination table to exclude already imported
5. import the remaining records

In SQL, steps 3-5 can be accomplished in one SQL statement

There may be excel functions which will do the same as SQL, I don't know

Not sure what you are asking but you might use power automate

Run SQL queries on Excel files - Power Automate

Run SQL queries on Excel files

learn.microsoft.com

or you might use ADO

Performing SQL queries on an Excel Table within a Workbook with VBA Macro

I am trying to make an excel macro that will give me the following function in Excel: =SQL("SELECT heading_1 FROM Table1 WHERE heading_2='foo'") Allowing me to search (and maybe even insert) data...

stackoverflow.com

or you might use DAO - requires adding the MS Access Object Library to references - coding similar to the ADO link

 

[stefanocps]

let me clarify
the file that come from the bank don't have duplicate, for sure
Duplicate may occur if i import more than once data from the original bank's file. si don't think there is a problem with it
just need how to make asemiautomted import

 

[CJ_London]

just need how to make asemiautomted import

take a look at the links provided

 

[GaP42]

That file from the bank sounds like the standard output from banks, that is / can be provided in various styles: QIF, CSV, ...
As a transaction list it is for a period of time (which unless an automated process) means the period is determined by the person operating the download/extract. That can bring in the risk of duplicate data in the import process.
You need to adopt a position :
What data will tell you a record is unique?
- overwrite any existing matching records- as duplicates, and append any records determined as new records (I do not think the bank will output a different set of data for a record that is determined to be the same transaction!).
- ignore any which match to existing records in the transaction list - appending just the new ones.

The file downloaded if in the form suggested does not contain any information which identifies the account.
You need to be really careful ensuring the file to be imported is from an account of interest, and if your database is supporting transaction data for multiple accounts that the right account is selected/associated with the import.

There was a thread of a similar nature?

(1) CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag) | Access World Forums (access-programmers.co.uk)

 

[Gasman]

You have some work on your hands.
I can buy several rounds in the pub on the same day and they will 'appear' to be duplicated, but are not.
Same day, payee, and amount.

 

[stefanocps]

That file from the bank sounds like the standard output from banks, that is / can be provided in various styles: QIF, CSV, ...
As a transaction list it is for a period of time (which unless an automated process) means the period is determined by the person operating the download/extract. That can bring in the risk of duplicate data in the import process.
You need to adopt a position :
What data will tell you a record is unique?
- overwrite any existing matching records- as duplicates, and append any records determined as new records (I do not think the bank will output a different set of data for a record that is determined to be the same transaction!).
- ignore any which match to existing records in the transaction list - appending just the new ones.

The file downloaded if in the form suggested does not contain any information which identifies the account.
You need to be really careful ensuring the file to be imported is from an account of interest, and if your database is supporting transaction data for multiple accounts that the right account is selected/associated with the import.

There was a thread of a similar nature?

yes exactly, my file 99% is the standard output and it does not have a 100% unique identifier for each record, so it depends on the attention of who import
In general, there is tthe montlyu report which is imported in one sheet. Next month in another sheet
For what i know this is the standard. Then i don't know if it may happen, that the operator will download a report at half month, and then again at the end of the month..and in this case there is the possibility of duplicate
This can be solved putting as mandatory the unique import at the end of the month...they arenot data that needs to be treated urgently..t is normal accountability
So if we start form this..what is the path?

 

[stefanocps]

You have some work on your hands.
I can buy several rounds in the pub on the same day and they will 'appear' to be duplicated, but are not.
Same day, payee, and amount.

it is not easy..it is a company , and those are report for the expenses..and it is very rare or impossible that the same day they do the same buy, same description, same amount... But for example the royalties for every money transfer looke just the same..so in the same days could be 2,3,4 or more royalties
But those are not important for th enext step, which is to assign a code for each movement of th emonth, movemente that are related to the activity, (buy this, buy that, pay paul..ecc), and for that i have another thred asking help

 

[stefanocps]

take a look at the links provided

hey are too hard for me