Appending SAP exports Every Day

[ShennyP]

I am very new to access and am struggling to find a good way to append daily exports from SAP with some error checking.

To be more detailed we export 2 SAP files every day and I need the new data added to their appropriate master table. I know there is an import excel file function where you can append the new data to the table which works well however I don't think I can put duplicate and error checking using this method. I want to try and make it as easy as possible however I don't want any chance of wrong data going in. What is going the be the best way of doing this?

[June7]

What format are these exported files in - CSV or Excel? Options:

1. possibly set links to the files and work with them like tables

2. VBA automation code to process data one line or row at a time

[ShennyP]

Could export as either of those formats. These files will change tho and I need the historic data to stay in the table.

[June7]

Appending should not impact existing records. If anything, the INSERT could fail to add new records but would not change existing.

[ArviLaanemets]

1. You have e.g. an Excel table on shared resource. New info is entered/copied/read into this table. In case every new row must be added into Access table, every row in this Excel data table must have some unique (time) template/counter saved at creation time, which will be stored into Access table too. In case some Access table must be updated with data from Excel table, then the Excel table must have columns allowing to identify the data to be updated;
2. You link the Excel table into your Access database. NB! The users will not have any access to linked table - not directly, and not through any forms!;
3. You create a VBA procedure in Acccess database, which reads data from linked table, evaluates the validity of data, and inserts valid data into Access table(s) or updates Access table(s) with valid data. NB! This is the only use for linked table!;
4. You create some control or form event which runs the procedure. Or you write a script run by scheduled task on some server/computer, which opens the Access database and runs the procedure.

[ShennyP]

1. You have e.g. an Excel table on shared resource. New info is entered/copied/read into this table. In case every new row must be added into Access table, every row in this Excel data table must have some unique (time) template/counter saved at creation time, which will be stored into Access table too. In case some Access table must be updated with data from Excel table, then the Excel table must have columns allowing to identify the data to be updated;
2. You link the Excel table into your Access database. NB! The users will not have any access to linked table - not directly, and not through any forms!;
3. You create a VBA procedure in Acccess database, which reads data from linked table, evaluates the validity of data, and inserts valid data into Access table(s) or updates Access table(s) with valid data. NB! This is the only use for linked table!;
4. You create some control or form event which runs the procedure. Or you write a script run by scheduled task on some server/computer, which opens the Access database and runs the procedure.

This sounds like it could work well. Sounds like a good way of doing it without mistakes.

So what I would do is have the export write over the top of the old export every day and someone would have to prompt a macro in this time to import the new data on top. This macro would obviously have conditions so that the identifier field (in my case order number) isn't a duplicate. I would probably set this up on a form and I assume i would have it as a push button much like vba?

Would there be an example somewhere of someone writing a vba button doing this append with duplicate checking?

[CJ_London]

what you require may be simple to resolve, or it may be complex. it sounds like you need more than just being pointed in the right direction. To do this we really need some example data. For example you say 'doing this append with duplicate checking'. Duplicate of what? a single field? some fields? all fields? do the SAP records have a primary key? is the SAP data normalised? Are your destination tables normalised? Does the data need some conversion? What sort of error checking to you require? Does the SAP data need to relate to existing data either manually maintained or sourced from another system?

Broadly speaking you will have 4 types of comparisons to make to determine

1. those records in SAP data that are also in your destination table(s) and are identical in all fields
2. those records in SAP data that are also in your destination table(s) and are different in some fields
3. those records in SAP data that are not in your destination table(s)
4. those records in your destination table(s) that are not in SAP data

these have to be compared in light of your requirement - i.e. do you just want a current view - in which case the type of action required is likely to be

1. ignore
2. update destination tables with SAP data
3. append SAP data to your destination table(s)
4. delete those records in destination data

or alternatively (although not recommended unless being stored in a separate, temporary back end)
a. delete all data in destination tables (better to delete the temporary database and create a new one)
b. append SAP data to destination table(s)


Or perhaps you need to maintain a history so you can track changes - in which case you would likely do

1. ignore
2. append SAP data to your destination tables perhaps with the addition of a timestamp field
3. append SAP data to your destination tables
4. ignore or perhaps flag in some way that the record is no longer current

Then you need to consider the metrics - typically how many records per load? how many fields? How many destination tables? frequency?

you mention the need for error checking so the following may be relevant
SAP has a habit of exporting data in a way that is not easy to import - such as starting the table of data a number of rows down (which might vary from file to file) and/or having one or more rows at the end of the report or blank rows in the middle of the dataset. I've also known it to produce reports where the worksheet names change (e.g. using week numbers or country names for sheet names)

And do the columns have consistent data types? - no 'NULL' text in a numeric column for example? Does the SAP data have numeric columns which contain a lot of blank values - particularly in the top few rows - these will be interpreted as text

There are other questions around automation but probably outside the scope of your question.

You will find many examples out there for importing data - which one is right for you depends on how you answer the above. And you may have to cobble bits of code from different examples to meet your particular requirement.

[ArviLaanemets]

So what I would do is have the export write over the top of the old export every day ...

Of course you can use some export procedure to read data from Excel table too, but my advice was to link the Excel table into Access DB.

In design view, you activate External Data menu, and:
a) Select <ODBC Database>, activate "Link the data source by creating a linked table.", and click OK. Select Excel workbook, and sheet/table/named range in it, etc.
b) (I myself have never used it yet.) Select <Excel>, activate "Link the data source by creating a linked table.", select workbook, and click OK. Proceed.

With Excel table linked, Access automatically refreshes the data, whenever the application is opened, and I think (have never looked deeply into way this works) also whenever the linked table is accessed (data are requested) - i.e. for users it is always on-time. So you avoid the hassle with querying data at all.