Batch import of products from a tabular file
Hello,
We can receive from our wholesaler a list of all the available products inside a Excel File.
The Excel File contains for each product :
- Reference number
- Article
- Code
- Key_Code
- Producer
- Price
- TVA
Does anybody already write a kind of batch that I can reuse (in SQL or n JAVA for example) which read a products file (CSV for example) and import the products descriptions into OpenVpms.
If not, do you have any advice to develop this batch?
Thanks.
Daniel
Re: [OpenVPMS Developers] Batch import of products from a tabula
We receive very similar files from wholesalers and have used these to load a base set of products into practices who are not migrating from an existing software system.
The ETL product was developed just for this purpose and I use Pentaho Kettle and the OpenVPMS plugin to build "transforms" to load all kinds of data into OpenVPMS.
You need to download and install Pentaho Kettle version 2.4 (sorry not compatible with version 3 yet but hopefully soon) and then load the plugin zip file found in the OpenVPMS release into the Kettle plugins/steps folder so you can use our especially designed OpenVPMS loader Kettle Step. This loader is archetype aware and greatly simplifies loading the OpenVPMS database.
I have a number of transforms I am happy to send to you that do similar things to what you want. You may just need to modify them to suit your input csv format etc.
Cheers Tony
On 20/08/09 6:13 AM, "daniel.dricot@hotmail.fr" :
> Hello, > We can receive from our wholesaler a list of all the available products inside > a Excel File. > The Excel File contains for each product : > - Reference number > - Article > - Code > - Key_Code > - Producer > - Price > - TVA > Does anybody already write a kind of batch that I can reuse (in SQL or n JAVA > for example) which read a products file (CSV for example) and import the > products descriptions into OpenVpms. > If not, do you have any advice to develop this batch? > Thanks. > Daniel > _______________________________________________ > OpenVPMS Developers Mailing List > developers@lists.openvpms.org > To unsubscribe or change your subscription visit: > http://lists.openvpms.org/listinfo/developers > Posts from this mailing list can be viewed online and replied to in the > OpenVPMS Developer's forum- http://tinyurl.com/openvdf_______________________________________________ OpenVPMS Developers Mailing List developers@lists.openvpms.org To unsubscribe or change your subscription visit: http://lists.openvpms.org/listinfo/developers Posts from this mailing list can be viewed online and replied to in the OpenVPMS Developer's forum- http://tinyurl.com/openvdf
Kettle transform
Hy Tony,
Thanks for your help.
I 've download and installed Kettle. I've also load the OpenVpms plugin and made some small tests with kettle.;
Can you please send me a transform sample file (for products if possible) because I did not understand how to use the plugins
Thanks in advance.
Daniel Dricot
PS : Sorry for my late response but it was summer holiday here :-)
Re: [OpenVPMS Developers] Kettle transform
I have attached a sample transform that takes information from a csv file containing product information and populates the base product and price information in OpenVPMS.
You can examine the Text File input step to see the structure of the csv file (fields tab) and replicate some sample data to test. Note the data can can from a wide range of sources (db, excel, csv, xml etc).
The OpenVPMS plugin uses a id field to identify individual records being loaded and stores logs of records loaded in a table in the database (etl_log). This allows transforms to be restarted and only data not already loaded will be processed (there is an check box on the plugin to enable this feature).
The plugin can also automatically populate lookups while loading. In this example the dispensing and selling units lookups are create on the fly without duplicates. saves having to preprocess the data to generate the lookup and then run the main transform.
Cheers Tony
Re: [OpenVPMS Developers] Kettle transform
Hy Tony,
Thanks
Where can I download your sample file ? I did'nt see any attached file.
Daniel Dricot
Re: [OpenVPMS Developers] Kettle transform
I didn't attach one on the basis you could create your own csv file using the information in the Text Input step as a guide to the columns required.
Anyway here is a sample that may help :-)
Cheers Tony
On Sat, Sep 26, 2009 at 5:12 AM, wrote:
> Hy Tony, > Thanks > Where can I download your sample file ? I did'nt see any attached file. > Daniel Dricot > _______________________________________________ > OpenVPMS Developers Mailing List > developers@lists.openvpms.org > To unsubscribe or change your subscription visit: > http://lists.openvpms.org/listinfo/developers > Posts from this mailing list can be viewed online and replied to in the > OpenVPMS Developer's forum- http://tinyurl.com/openvdf >-- ______________________________________________________________________ Principal Consultant Vertical Connect Pty Ltd Phone : +61 (0) 3 97229824 Mobile: +61 (0) 4 21347105 Email : tony@verticalconnect.net Web : http://www.verticalconnect.net
Where are the attached files ?
Hi Tony,
My problem was simpler :-) ... I can't see any attached file on the Web Page. Even in the Email I received, there are no attached file.
And from the result of a search request with "kettle" return :
... was developed just for this purpose and I use Pentaho Kettle and the OpenVPMS plugin to build "transforms" to load all kinds of data ... You need to download and install Pentaho Kettle version 2.4 (sorry not compatible with version 3 yet but hopefully soon) ...
Forum topic - ddricot - 20/08/2009 - 06:13 - 5 commentaires - 0 fichier attaché
Where do I have to look at ?
Thanks for you help.
Daniel
Where are the attached files ?
Hi Daniel,
Tony's original file got stripped on the way in from the mailing list. I have attached it to this comment. You will need to visit the forum to grab it.
Cheers,
Matt Y.
Kettle 2.5.2
Hi,
I have obtained an .xls spreadsheet from our supplier with the fields CODE, PRODUCT NAME and COST and reading your recommendations to use pentaho kettle to import the data into OPENVPMS
I did a search for Kettle 2.4 but it seems all the sites that once offered it are now only offering the newer versions and it is nowhere to be found. I have managed to find PDI 2.5.2 but I am not sure if this version will work correctly, could you confirm this?
Anyway, I have downloaded and installed 2.5.2 and attempted to go through the process of "transforming" the data but seem to be having little success
In creating a new repository the program comes up with an error saying couldn't connect and that this version of the repository is 2.2 and that this kettle edition requires it to be at least 2.5, and of course I could not find any version earlier than 2.5.2 anyway so this is understandable.
Anyway skipping that part, I copied over the OpenVPMSloader to the /steps folder and used your template csv and played around with it for a bit and in attempting to verify the transformation - among many other errors - I get another error regarding the JsMod transform fields plugin:
[4 - Error] Transform Fields
General error executing script:
TypeError: setString is not a function. (script; line 5)
Which I can only assume is another problem caused by incompatability of versions?
Any help would be appreciated, hopefully this is all just a result of the wrong version. If so, any idea where I can find the one compatible with OpenVPMS?
Thanks
Batch import of products
Hi,
Not sure what is happening with repository but typically I never utilse and just keep transforms and jobs in files rather than a database.
In regards the setString error this is a comaptability issue but is easily rectified. From version 2.5 onwards they changed some of the input and output method names in the Modifed Java Script step which was painful and so you need to modify these to get the original transform to work.
The main changes are all the setString etc calls are now setValue instead. In the Java Script step editor you have a left hand tree view panel which gives you examples of all the input, output and general functions. This should help you fix the transform.
BTW version 1.4 supports the latest version of PDI.
Cheers
Tony
Re: Batch import of products from a tabular file
Hi Tony,
I need this to load some products and well would have given it a shot except that the oldest version of kettle that I can get is 2.5 which gives me errors when I select the OpenVPMS Transformation from tools. Any chance you can perhaps attach the kettle 2.4 here?
Re: Batch import of products from a tabular file
Hi Nilesh,
The issue is the later versions of Kettle changed the javasipt to use different methods to set values.
I suggest you gte the latest supported release of Kettle (3.2 I believe) , load the latest plugin, open the transform and modify the Transform fields step script to say
NAME.setValue(wordUtils.capitalizeFully(trim(NAME.getString())))I believe that shoudl get rid of the errors.
Cheers
Tony
Re: Batch import of products from a tabular file
Hi all,
Still attempting to use Kettle to load some data into OpenVPMS. My environment
Java 1.6 build 26 (latest Java6)
Kettle 3.2 - local
OpenVPMS 1.5 on remotehost
MySQL on remotehost
Importing a CSV -> If Null Value Step -> OpenVPMS Loader
I have previewed the results (error free) until I link the loader at which point it gives:
2011/08/04 14:59:43 - General - Initialising application context, using database URL: jdbc:mysql://10.1.3.21:3306/openvpms?defaultFetchSize=500&useCursorFetch=true
Re: [OpenVPMS Developers] Batch import of products from a t abul
Hi all, Still attempting to use Kettle to load some data into OpenVPMS. My environment Java 1.6 build 26 (latest Java6) Kettle 3.2 - local OpenVPMS 1.5 on remotehost MySQL on remotehost Importing a CSV -> If Null Value Step -> OpenVPMS Loader I have previewed the results (error free) until I link the loader at which point it gives: 2011/08/04 14:59:43 - General - Initialising application context, using database URL: jdbc:mysql://10.1.3.21:3306/openvpms?defaultFetchSize=500&useCursorFetch=true 2011/08/04 14:59:43 - org.openvpms.etl.kettle.LoaderPluginMeta - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : org.springframework.util.Assert.noNullElements([Ljava/lang/Object;Ljava/lang/String;) Hence I figured I had not defined the datasource correctly. yet it tests ok and intializes in Pentaho. A bit later it throws the final fail errors 2011/08/04 14:59:43 - OpenVPMS Loader.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Unexpected error: 2011/08/04 14:59:43 - OpenVPMS Loader.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Cannot perform transformation. No application context specified 2011/08/04 14:59:43 - OpenVPMS Loader.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : org.pentaho.di.core.exception.KettleException: 2011/08/04 14:59:43 - OpenVPMS Loader.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Cannot perform transformation. No application context specified 2011/08/04 14:59:43 - OpenVPMS Loader.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : 2011/08/04 14:59:43 - OpenVPMS Loader.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.openvpms.etl.kettle.LoaderPlugin.getLoader(LoaderPlugin.java:220) 2011/08/04 14:59:43 - OpenVPMS Loader.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.openvpms.etl.kettle.LoaderPlugin.run(LoaderPlugin.java:183) I am assuming these errors are just errors that in some sence are as a result of the first exception. Can anyone shed any light as to how to obtain a valid connection when actually running the transform.. keeping in mind I have definded a working JDBC MySQL connection in the Kettle Database connections dialog and assigned it too OpenVPMS loader. I can browse the MYSQL data using the in build data explorer as well. Ben