Would nuBuilder be suitable for my use case?

[ricking]

Hi,

I receive regular (daily) price and stock updates from my suppliers, as I trade in price volatile items. I’m looking for a way to be able to manipulate the data I receive from the suppliers in CSV format and import it to update ERPNext.

My issue seems to be that ERPNext assigns unique IDs to items and supplier items, and the suppliers don’t know my unique IDs to populate the CSV files with on the correct line. I need a tool that will allow me to either import directly to ERPNext using the existing data provided and match using my product nos or IDs when compared to the supplier part no, or an external tool that will format the CSV for ERPNext.

Does anyone have any suggestions for where to start? Would nuBuilder do the job? How easy would it be to setup?

Thanks

[kev1n]

Hi,

nuBuilder is a tool for developing web-based database applications (visual forms)
ETL tools are more suited to automate data flows and Extract/Transform/Load data.

https://datavirtuality.com/blog-etl-too ... processes/

[ricking]

Thanks for the reply and a poke in the right direction. I'm new to this, and trying to feel out solutions. I'll take a look at ETL solutions instead!

[Dalkeith]

MS Access makes a kick ass ETL tool

It can connect to all the main databases

MySQL
https://rounduptheusualsuspects.org/?p=1563

Postgres
https://rounduptheusualsuspects.org/?p=1774

SQl Server
https://rounduptheusualsuspects.org/?p=1446

Oracle
https://stackoverflow.com/questions/504 ... e-database

You can script queries and order them...
https://rounduptheusualsuspects.org/?p=1558

Its cheap compared with something like Informatica

There is loads of code out there and already waiting and its much better tested than most ETL tools

Plus you have full strength of VBA if you need to do really clever things.

In 2017 I worked with a company called IDOX their recommended tool for ETL was MS Access we converted all the building and planning records from 30 years of applications. It took us quite a long time to figure out what was a massive system - it had in excess of 100 tables - the difficult thing was that the relational databases source and target had differing structures - We sucked everything out of Oracle and put it back into a different Oracle database.

There is a limit of 2gb with access and often you don't really want to go above about 1.5gb but if you know that you can work round it and do things like suck individual tables from remote source database alter structure add keys etc then place in a remote database delete then table in MS Access.

So the first question you need to ask is exactly how much data needs to be converted...

Just looked up ERP Next and they were saying it connects to MariaDB - MariaDB will probably connect to ms access (almost defintiely)

[kev1n]

There are also many free ETL Tools available (like Talend, Pentaho etc.).

With Pentaho e.g., it's very easy to setup a "data synchronization" with a few steps in a few minutes using a visual designer (no coding required).

E.g. fetch the CSV from a folder or FTP etc (use wildcards/regular expressions), lookup existing data from a database and optionally transform data with inbuilt steps (or use Java/Javascript), load the new data into destination db or update existing data. Then schedule the job and you're done.
This can then be extended as desired, like sending email alerts in case of a failure, send an import notification as text message etc etc.