You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by su2 <sh...@pexsupply.com> on 2009/11/11 17:50:56 UTC
Re: how to import data from excel sheet
Hi Nalin,
We have similar requirement where we want to upload/import all the product
information from excel sheet.
I would really appreciate if you could tell me, how we can achieve that.
Thank you.
Shuchi-
Nalin Chandra wrote:
>
> Hi All
>
> I want to import the product related data from excel sheet. I did it but i
> have one problem in this.
> I excel sheet i have absolute path of image now i want to upload the image
> from that absolute path to our application as well store the relative path
> in data base as we did in product content section.
> i am able to store the relative path in database but image in not
> uploading in the required folder.
>
> Main problem is that here i am not using any html form.
>
> i paste the code below for it
>
> public static String uploadCategory(HttpServletRequest request,
> HttpServletResponse response) {
>
> try
> {
> String filename = "D:/category.xls";
> WorkbookSettings ws = new WorkbookSettings();
> ws.setLocale(new Locale("en", "EN"));
> Workbook workbook = Workbook.getWorkbook(new File(filename),ws);
> Sheet s = workbook.getSheet(0);
> readCategoryDataSheet(s, request);
> workbook.close();
> }
> catch (IOException e)
> {
> e.printStackTrace();
> }
> catch (BiffException e)
> {
> e.printStackTrace();
> }
>
> return "success";
> }
>
> private static void readCategoryDataSheet(Sheet s, HttpServletRequest
> request)
> {
> Cell rowData[] = null;
> int successCount = 0;
> int failCount = 0;
> int rows = s.getRows();
> int column = s.getColumns();
> String productCategoryId = "";
> String productCategoryTypeId = "";
> String primaryParentCategoryId = "";
> String categoryName = "";
> String description = "";
> String longDescription = "";
> String categoryImageUrl = "";
> String linkOneImageUrl = "";
> String prodCatalogId = "";
> String prodCatalogCategoryTypeId = "";
> String sequenceNum = "";
>
> for (int i = 1; i < rows; i++) {
> rowData = s.getRow(i);
> if (rowData[0].getContents().length() != 0) {
> for (int j = 0; j < column; j++) {
> switch (j) {
> case 0:
> productCategoryId =
> rowData[j].getContents();
> case 1:
> productCategoryTypeId =
> rowData[j].getContents();
> case 2:
> primaryParentCategoryId =
> rowData[j].getContents();
> case 3:
> categoryName =
> rowData[j].getContents();
> case 4:
> description =
> rowData[j].getContents();
> case 5:
> longDescription =
> rowData[j].getContents();
> case 6:
> categoryImageUrl =
> rowData[j].getContents();
> case 7:
> linkOneImageUrl =
> rowData[j].getContents();
> default:
> break;
> }
> }
> }
> String imageFilenameFormat =
> UtilProperties.getPropertyValue("catalog", "image.filename.format");
> String imageServerPath =
> UtilProperties.getPropertyValue("catalog", "image.server.path");
> String imageUrlPrefix =
> UtilProperties.getPropertyValue("catalog", "image.url.prefix");
>
>
> // upload image
> FlexibleStringExpander filenameExpander = new
> FlexibleStringExpander(imageFilenameFormat);
> String catImageUrl = "";
> String linkImageUrl = "";
> if(categoryImageUrl != null &&
> categoryImageUrl.length() > 0){
> Object forLock = new Object();
> String contentType = null;
> String categoryImageLocation =
> filenameExpander.expandString(UtilMisc.toMap("location", "categories",
> "type", "category", "id", productCategoryId));
> String filePathPrefix = "";
> String filenameToUse = categoryImageLocation;
> if (categoryImageLocation.lastIndexOf("/") !=
> -1) {
> filePathPrefix =
> categoryImageLocation.substring(0, categoryImageLocation.lastIndexOf("/")
> + 1); // adding 1 to include the trailing slash
> filenameToUse =
> categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/") +
> 1);
> }
>
> int i1;
> if (contentType != null && (i1 =
> contentType.indexOf("boundary=")) != -1) {
> contentType = contentType.substring(i1 +
> 9);
> contentType = "--" + contentType;
> }
>
> String defaultFileName = filenameToUse +
> "_temp";
> HttpRequestFileUpload uploadObject = new
> HttpRequestFileUpload();
>
> uploadObject.setOverrideFilename(defaultFileName);
> uploadObject.setSavePath(imageServerPath + "/"
> + filePathPrefix);
> try{
> uploadObject.doUpload(request);
>
> }catch(IOException e){
> Debug.logInfo("Image uploading failure",
> module);
> }
> String categoryImageFileName =
> uploadObject.getFilename();
>
> if (categoryImageFileName != null &&
> categoryImageFileName.length() > 0) {
> if (categoryImageFileName.lastIndexOf(".")
> > 0 && categoryImageFileName.lastIndexOf(".") <
> categoryImageFileName.length()) {
> filenameToUse +=
> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
> } else {
> filenameToUse += ".jpg";
> }
> try{
> String characterEncoding =
> request.getCharacterEncoding();
> catImageUrl = imageUrlPrefix + "/" +
> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
> characterEncoding);
> }catch(Exception e){
> System.out.println("Incoding Problem");
> }
>
> try {
> File file = new File(imageServerPath +
> "/" + filePathPrefix, defaultFileName);
> File file1 = new File(imageServerPath
> + "/" + filePathPrefix, filenameToUse);
> try {
> file1.delete();
> } catch(Exception e) {
> System.out.println("error deleting
> existing file (not neccessarily a problem)");
> }
> file.renameTo(file1);
> } catch(Exception e) {
> e.printStackTrace();
> }
> }
> }
>
> // end of upload image
>
> Timestamp fromDate = UtilDateTime.nowTimestamp();
> GenericDelegator delegator = (GenericDelegator)
> request.getAttribute("delegator");
> try {
> GenericValue DataImportCategoryList =
> delegator.findByPrimaryKey("DataImportCategory",
> UtilMisc.toMap("productCategoryId", productCategoryId));
>
> if(DataImportCategoryList != null){
> String categoryId =
> DataImportCategoryList.getString("productCategoryId");
> if(categoryId.equals(productCategoryId)){
> failCount++;
> }
> }else {
> GenericValue newImportCategory =
> delegator.makeValue("DataImportCategory", null);
>
> newImportCategory.set("productCategoryId",
> productCategoryId.trim());
> if(productCategoryTypeId != null && productCategoryTypeId.length()
> > 0){
> newImportCategory.set("productCategoryTypeId",
> productCategoryTypeId.trim());
> } else {
>
> newImportCategory.set("productCategoryTypeId", "CATALOG_CATEGORY");
> }
> if(primaryParentCategoryId != null &&
> primaryParentCategoryId.length() > 0)
> newImportCategory.set("primaryParentCategoryId",
> primaryParentCategoryId);
> newImportCategory.set("categoryName", categoryName.trim());
> newImportCategory.set("description", description);
> newImportCategory.set("longDescription", longDescription);
> newImportCategory.set("categoryImageUrl", catImageUrl);
> newImportCategory.set("linkOneImageUrl", linkImageUrl);
> newImportCategory.set("fromDate",
> fromDate);
> try {
>
> delegator.create(newImportCategory);
> Debug.logInfo("Successfully
> imported category ["+productCategoryId+" from row no "+ i+1 +"].",
> module);
> successCount++;
> } catch (GenericEntityException e) {
> Debug.logWarning(e.getMessage(),
> module);
> }
>
> }
> } catch(GenericEntityException e) {
> Debug.logError("Exception occured :"+e.getMessage(), module);
> }
> }
>
> }
>
>
> it create the temp file in corresponding directory but there is no actual
> image at that path.
>
> So any one have any idea regarding this please suggest me.
>
>
> Thanks.
>
> Nalin Chandra
>
--
View this message in context: http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: how to import data from excel sheet
Posted by su2 <sh...@pexsupply.com>.
Hi RIshi,
I answered myself, I got the patch . Built and restarted ofbiz. I used the
same exploratory xls file with the patch.
As per the documentation, by Clicking on Import Text I got the result as
“Got 3 entities to write to the datasource.”
After restarting ofbiz, I do not see the xls data entry into my database
Entities - 'Product' and 'InventoryItem'
Am I missing something?
Thank you for the help.
Shuchi-
This will schedule “productImport” service
Rishi Solanki wrote:
>
> Hi,
> Look at the ImportProductServices.java, here you will get the
> productImportFromSpreadsheet() service to import the product data from
> Excel
> sheet.
> You just need to put your Excel sheet in your ${OFBIZ-HOME}/spreadsheet
> directory (need to create). Now go to webtools run service
> productImportFromSpreadsheet without passing any parameter. If your Excell
> Sheet has some other Product entity field then those needs to be handle by
> modifying the service.
>
> Rishi Solanki
> Enterprise Software Developer
> HotWax Media Pvt. Ltd.
>
>
> On Fri, Nov 13, 2009 at 10:57 AM, S K Pradeep kumar <
> s.k.pradeepkumar@gmail.com> wrote:
>
>> Study about webtools there is one data import facility study that and use
>> it
>> for your requirements.
>>
>>
>> With regards,
>> S K Pradeep kumar,
>>
>>
>>
>> On Thu, Nov 12, 2009 at 8:05 PM, su2 <sh...@pexsupply.com> wrote:
>>
>> >
>> > Hi Pradeep,
>> >
>> > Sorry still little confuse. How am I going to use DemoProduct.xml file?
>> I
>> > understood the file I can have all my required data there, but after
>> that
>> > what should I be doing in order to insert the data in entities?
>> >
>> > Thank you.
>> > Shuchi -
>> >
>> > S K Pradeep kumar wrote:
>> > >
>> > > you can find the xml filse in the data folder in all the
>> application/*
>> > > module , to create the new products use DemoProduct.xml file.
>> > >
>> > > With regards,
>> > > S K Pradeep kumar,
>> > >
>> > >
>> > >
>> > > On Thu, Nov 12, 2009 at 7:12 PM, su2 <sh...@pexsupply.com> wrote:
>> > >
>> > >>
>> > >> Hi Pradeep,
>> > >>
>> > >> Thanks for your response.
>> > >>
>> > >> Sorry I am new to ofbiz. But I am not sure where to define the
>> entity
>> > >> names
>> > >> and the entity-field names where I want to insert the data(as all
>> the
>> > >> information like price and others for the product is not stored in
>> only
>> > >> 'Product' entity, I need to insert the data in multiple entities)?
>> > >>
>> > >> Do I have to specify in XML? Do you have any sample xml file? I want
>> to
>> > >> insert data for new products
>> > >>
>> > >> Also, I see entity "DataImportProduct". What is that for?
>> > >>
>> > >> Once again thank you for the help.
>> > >>
>> > >> Shuchi-
>> > >>
>> > >>
>> > >> S K Pradeep kumar wrote:
>> > >> >
>> > >> > Hi shuchi,
>> > >> >
>> > >> > Convert your .cvs to xml and use webtools entity import.
>> > >> >
>> > >> > With regards,
>> > >> > S K Pradeep kumar,
>> > >> >
>> > >> >
>> > >> >
>> > >> > On Thu, Nov 12, 2009 at 12:52 AM, su2 <sh...@pexsupply.com>
>> wrote:
>> > >> >
>> > >> >>
>> > >> >> Hi Jacques,
>> > >> >>
>> > >> >> I want to insert all the excel data into the database. I looked
>> at
>> > >> >> viewdatafile screen, and looked like, using that I can not insert
>> all
>> > >> my
>> > >> >> excel data into database tables.
>> > >> >>
>> > >> >>
>> > >> >> Jacques Le Roux wrote:
>> > >> >> >
>> > >> >> > I'd use CSV with
>> > >> https://localhost:8443/webtools/control/viewdatafile
>> > >> .
>> > >> >> I
>> > >> >> > let you find more on this ML archives...
>> > >> >> >
>> > >> >> > Jacques
>> > >> >> >
>> > >> >> > From: "su2" <sh...@pexsupply.com>
>> > >> >> >>
>> > >> >> >> Hi Nalin,
>> > >> >> >>
>> > >> >> >> We have similar requirement where we want to upload/import all
>> the
>> > >> >> >> product
>> > >> >> >> information from excel sheet.
>> > >> >> >>
>> > >> >> >> I would really appreciate if you could tell me, how we can
>> achieve
>> > >> >> that.
>> > >> >> >>
>> > >> >> >> Thank you.
>> > >> >> >> Shuchi-
>> > >> >> >>
>> > >> >> >> Nalin Chandra wrote:
>> > >> >> >>>
>> > >> >> >>> Hi All
>> > >> >> >>>
>> > >> >> >>> I want to import the product related data from excel sheet. I
>> did
>> > >> it
>> > >> >> but
>> > >> >> >>> i
>> > >> >> >>> have one problem in this.
>> > >> >> >>> I excel sheet i have absolute path of image now i want to
>> upload
>> > >> the
>> > >> >> >>> image
>> > >> >> >>> from that absolute path to our application as well store the
>> > >> relative
>> > >> >> >>> path
>> > >> >> >>> in data base as we did in product content section.
>> > >> >> >>> i am able to store the relative path in database but image in
>> not
>> > >> >> >>> uploading in the required folder.
>> > >> >> >>>
>> > >> >> >>> Main problem is that here i am not using any html form.
>> > >> >> >>>
>> > >> >> >>> i paste the code below for it
>> > >> >> >>>
>> > >> >> >>> public static String uploadCategory(HttpServletRequest
>> request,
>> > >> >> >>> HttpServletResponse response) {
>> > >> >> >>>
>> > >> >> >>> try
>> > >> >> >>> {
>> > >> >> >>> String filename = "D:/category.xls";
>> > >> >> >>> WorkbookSettings ws = new WorkbookSettings();
>> > >> >> >>> ws.setLocale(new Locale("en", "EN"));
>> > >> >> >>> Workbook workbook = Workbook.getWorkbook(new
>> > >> >> >>> File(filename),ws);
>> > >> >> >>> Sheet s = workbook.getSheet(0);
>> > >> >> >>> readCategoryDataSheet(s, request);
>> > >> >> >>> workbook.close();
>> > >> >> >>> }
>> > >> >> >>> catch (IOException e)
>> > >> >> >>> {
>> > >> >> >>> e.printStackTrace();
>> > >> >> >>> }
>> > >> >> >>> catch (BiffException e)
>> > >> >> >>> {
>> > >> >> >>> e.printStackTrace();
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> return "success";
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> private static void readCategoryDataSheet(Sheet s,
>> > >> >> >>> HttpServletRequest
>> > >> >> >>> request)
>> > >> >> >>> {
>> > >> >> >>> Cell rowData[] = null;
>> > >> >> >>> int successCount = 0;
>> > >> >> >>> int failCount = 0;
>> > >> >> >>> int rows = s.getRows();
>> > >> >> >>> int column = s.getColumns();
>> > >> >> >>> String productCategoryId = "";
>> > >> >> >>> String productCategoryTypeId = "";
>> > >> >> >>> String primaryParentCategoryId = "";
>> > >> >> >>> String categoryName = "";
>> > >> >> >>> String description = "";
>> > >> >> >>> String longDescription = "";
>> > >> >> >>> String categoryImageUrl = "";
>> > >> >> >>> String linkOneImageUrl = "";
>> > >> >> >>> String prodCatalogId = "";
>> > >> >> >>> String prodCatalogCategoryTypeId = "";
>> > >> >> >>> String sequenceNum = "";
>> > >> >> >>>
>> > >> >> >>> for (int i = 1; i < rows; i++) {
>> > >> >> >>> rowData = s.getRow(i);
>> > >> >> >>> if (rowData[0].getContents().length()
>> !=
>> > >> 0)
>> > >> {
>> > >> >> >>> for (int j = 0; j < column;
>> j++)
>> > {
>> > >> >> >>> switch (j) {
>> > >> >> >>> case 0:
>> > >> >> >>>
>> productCategoryId
>> > >> =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 1:
>> > >> >> >>>
>> > >> productCategoryTypeId
>> > >> >> =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 2:
>> > >> >> >>>
>> > >> >> primaryParentCategoryId
>> > >> >> >>> =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 3:
>> > >> >> >>> categoryName
>> =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 4:
>> > >> >> >>> description =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 5:
>> > >> >> >>>
>> longDescription
>> =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 6:
>> > >> >> >>>
>> categoryImageUrl
>> > =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 7:
>> > >> >> >>>
>> linkOneImageUrl
>> =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> default:
>> > >> >> >>> break;
>> > >> >> >>> }
>> > >> >> >>> }
>> > >> >> >>> }
>> > >> >> >>> String imageFilenameFormat =
>> > >> >> >>> UtilProperties.getPropertyValue("catalog",
>> > >> "image.filename.format");
>> > >> >> >>> String imageServerPath =
>> > >> >> >>> UtilProperties.getPropertyValue("catalog",
>> "image.server.path");
>> > >> >> >>> String imageUrlPrefix =
>> > >> >> >>> UtilProperties.getPropertyValue("catalog",
>> "image.url.prefix");
>> > >> >> >>>
>> > >> >> >>>
>> > >> >> >>> // upload image
>> > >> >> >>> FlexibleStringExpander
>> filenameExpander
>> =
>> > >> new
>> > >> >> >>> FlexibleStringExpander(imageFilenameFormat);
>> > >> >> >>> String catImageUrl = "";
>> > >> >> >>> String linkImageUrl = "";
>> > >> >> >>> if(categoryImageUrl != null &&
>> > >> >> >>> categoryImageUrl.length() > 0){
>> > >> >> >>> Object forLock = new
>> Object();
>> > >> >> >>> String contentType = null;
>> > >> >> >>> String categoryImageLocation
>> =
>> > >> >> >>> filenameExpander.expandString(UtilMisc.toMap("location",
>> > >> >> "categories",
>> > >> >> >>> "type", "category", "id", productCategoryId));
>> > >> >> >>> String filePathPrefix = "";
>> > >> >> >>> String filenameToUse =
>> > >> >> >>> categoryImageLocation;
>> > >> >> >>> if
>> > >> >> (categoryImageLocation.lastIndexOf("/")
>> > >> >> >>> !=
>> > >> >> >>> -1) {
>> > >> >> >>> filePathPrefix =
>> > >> >> >>> categoryImageLocation.substring(0,
>> > >> >> >>> categoryImageLocation.lastIndexOf("/")
>> > >> >> >>> + 1); // adding 1 to include the trailing slash
>> > >> >> >>> filenameToUse =
>> > >> >> >>>
>> > >> >>
>> > categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/")
>> > >> >> +
>> > >> >> >>> 1);
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> int i1;
>> > >> >> >>> if (contentType != null && (i1 =
>> > >> >> >>> contentType.indexOf("boundary=")) != -1) {
>> > >> >> >>> contentType =
>> > >> >> contentType.substring(i1
>> > >> >> +
>> > >> >> >>> 9);
>> > >> >> >>> contentType = "--" +
>> contentType;
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> String defaultFileName =
>> > filenameToUse
>> > >> +
>> > >> >> >>> "_temp";
>> > >> >> >>> HttpRequestFileUpload
>> uploadObject
>> =
>> > >> new
>> > >> >> >>> HttpRequestFileUpload();
>> > >> >> >>>
>> > >> >> >>> uploadObject.setOverrideFilename(defaultFileName);
>> > >> >> >>>
>> > >> uploadObject.setSavePath(imageServerPath
>> > >> >> +
>> > >> >> >>> "/"
>> > >> >> >>> + filePathPrefix);
>> > >> >> >>> try{
>> > >> >> >>> uploadObject.doUpload(request);
>> > >> >> >>>
>> > >> >> >>> }catch(IOException e){
>> > >> >> >>> Debug.logInfo("Image uploading
>> > >> failure",
>> > >> >> >>> module);
>> > >> >> >>> }
>> > >> >> >>> String categoryImageFileName =
>> > >> >> >>> uploadObject.getFilename();
>> > >> >> >>>
>> > >> >> >>> if (categoryImageFileName != null
>> &&
>> > >> >> >>> categoryImageFileName.length() > 0) {
>> > >> >> >>> if
>> > >> >> >>> (categoryImageFileName.lastIndexOf(".")
>> > >> >> >>> > 0 && categoryImageFileName.lastIndexOf(".") <
>> > >> >> >>> categoryImageFileName.length()) {
>> > >> >> >>> filenameToUse +=
>> > >> >> >>>
>> > >> >>
>> > >>
>> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
>> > >> >> >>> } else {
>> > >> >> >>> filenameToUse += ".jpg";
>> > >> >> >>> }
>> > >> >> >>> try{
>> > >> >> >>> String characterEncoding =
>> > >> >> >>> request.getCharacterEncoding();
>> > >> >> >>> catImageUrl = imageUrlPrefix
>> +
>> > "/"
>> > >> +
>> > >> >> >>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
>> > >> >> >>> characterEncoding);
>> > >> >> >>> }catch(Exception e){
>> > >> >> >>> System.out.println("Incoding
>> > >> >> Problem");
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> try {
>> > >> >> >>> File file = new
>> > >> >> File(imageServerPath
>> > >> >> >>> +
>> > >> >> >>> "/" + filePathPrefix, defaultFileName);
>> > >> >> >>> File file1 = new
>> > >> >> >>> File(imageServerPath
>> > >> >> >>> + "/" + filePathPrefix, filenameToUse);
>> > >> >> >>> try {
>> > >> >> >>> file1.delete();
>> > >> >> >>> } catch(Exception e) {
>> > >> >> >>>
>> System.out.println("error
>> > >> >> >>> deleting
>> > >> >> >>> existing file (not neccessarily a problem)");
>> > >> >> >>> }
>> > >> >> >>> file.renameTo(file1);
>> > >> >> >>> } catch(Exception e) {
>> > >> >> >>> e.printStackTrace();
>> > >> >> >>> }
>> > >> >> >>> }
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> // end of upload image
>> > >> >> >>>
>> > >> >> >>> Timestamp fromDate =
>> UtilDateTime.nowTimestamp();
>> > >> >> >>> GenericDelegator delegator = (GenericDelegator)
>> > >> >> >>> request.getAttribute("delegator");
>> > >> >> >>> try {
>> > >> >> >>> GenericValue DataImportCategoryList =
>> > >> >> >>> delegator.findByPrimaryKey("DataImportCategory",
>> > >> >> >>> UtilMisc.toMap("productCategoryId", productCategoryId));
>> > >> >> >>>
>> > >> >> >>> if(DataImportCategoryList != null){
>> > >> >> >>> String categoryId =
>> > >> >> >>> DataImportCategoryList.getString("productCategoryId");
>> > >> >> >>>
>> if(categoryId.equals(productCategoryId)){
>> > >> >> >>> failCount++;
>> > >> >> >>> }
>> > >> >> >>> }else {
>> > >> >> >>> GenericValue newImportCategory =
>> > >> >> >>> delegator.makeValue("DataImportCategory", null);
>> > >> >> >>>
>> > >> >> >>> newImportCategory.set("productCategoryId",
>> > >> >> >>> productCategoryId.trim());
>> > >> >> >>> if(productCategoryTypeId != null &&
>> > >> >> >>> productCategoryTypeId.length()
>> > >> >> >>> > 0){
>> > >> >> >>> newImportCategory.set("productCategoryTypeId",
>> > >> >> >>> productCategoryTypeId.trim());
>> > >> >> >>> } else {
>> > >> >> >>>
>> > >> >> >>> newImportCategory.set("productCategoryTypeId",
>> > >> "CATALOG_CATEGORY");
>> > >> >> >>> }
>> > >> >> >>> if(primaryParentCategoryId != null &&
>> > >> >> >>> primaryParentCategoryId.length() > 0)
>> > >> >> >>> newImportCategory.set("primaryParentCategoryId",
>> > >> >> >>> primaryParentCategoryId);
>> > >> >> >>> newImportCategory.set("categoryName",
>> > >> categoryName.trim());
>> > >> >> >>> newImportCategory.set("description", description);
>> > >> >> >>> newImportCategory.set("longDescription",
>> > longDescription);
>> > >> >> >>> newImportCategory.set("categoryImageUrl",
>> catImageUrl);
>> > >> >> >>> newImportCategory.set("linkOneImageUrl",
>> linkImageUrl);
>> > >> >> >>>
>> > >> newImportCategory.set("fromDate",
>> > >> >> >>> fromDate);
>> > >> >> >>> try {
>> > >> >> >>>
>> > >> >> >>> delegator.create(newImportCategory);
>> > >> >> >>>
>> > >> Debug.logInfo("Successfully
>> > >> >> >>> imported category ["+productCategoryId+" from row no "+ i+1
>> > +"].",
>> > >> >> >>> module);
>> > >> >> >>> successCount++;
>> > >> >> >>> } catch
>> > >> (GenericEntityException
>> > >> >> e)
>> > >> >> >>> {
>> > >> >> >>>
>> > >> >> >>> Debug.logWarning(e.getMessage(),
>> > >> >> >>> module);
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> }
>> > >> >> >>> } catch(GenericEntityException e) {
>> > >> >> >>> Debug.logError("Exception occured :"+e.getMessage(),
>> > >> module);
>> > >> >> >>> }
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>>
>> > >> >> >>> it create the temp file in corresponding directory but there
>> is
>> > no
>> > >> >> >>> actual
>> > >> >> >>> image at that path.
>> > >> >> >>>
>> > >> >> >>> So any one have any idea regarding this please suggest me.
>> > >> >> >>>
>> > >> >> >>>
>> > >> >> >>> Thanks.
>> > >> >> >>>
>> > >> >> >>> Nalin Chandra
>> > >> >> >>>
>> > >> >> >>
>> > >> >> >> --
>> > >> >> >> View this message in context:
>> > >> >> >>
>> > >> >>
>> > >>
>> >
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
>> > >> >> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
>> > >> >> >>
>> > >> >> >
>> > >> >> >
>> > >> >> >
>> > >> >>
>> > >> >> --
>> > >> >> View this message in context:
>> > >> >>
>> > >>
>> >
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p586989.html
>> > >> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
>> > >> >>
>> > >> >
>> > >> >
>> > >>
>> > >> --
>> > >> View this message in context:
>> > >>
>> >
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620283.html
>> > >> Sent from the OFBiz - User mailing list archive at Nabble.com.
>> > >>
>> > >
>> > >
>> >
>> > --
>> > View this message in context:
>> >
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620305.html
>> > Sent from the OFBiz - User mailing list archive at Nabble.com.
>> >
>>
>
>
--
View this message in context: http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p621195.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: how to import data from excel sheet
Posted by su2 <sh...@pexsupply.com>.
Hi Rishi,
I was not able to find the ImportProductServices.java file. Where can I
locate it?
Thanks.
Shuchi-
Rishi Solanki wrote:
>
> Hi,
> Look at the ImportProductServices.java, here you will get the
> productImportFromSpreadsheet() service to import the product data from
> Excel
> sheet.
> You just need to put your Excel sheet in your ${OFBIZ-HOME}/spreadsheet
> directory (need to create). Now go to webtools run service
> productImportFromSpreadsheet without passing any parameter. If your Excell
> Sheet has some other Product entity field then those needs to be handle by
> modifying the service.
>
> Rishi Solanki
> Enterprise Software Developer
> HotWax Media Pvt. Ltd.
>
>
> On Fri, Nov 13, 2009 at 10:57 AM, S K Pradeep kumar <
> s.k.pradeepkumar@gmail.com> wrote:
>
>> Study about webtools there is one data import facility study that and use
>> it
>> for your requirements.
>>
>>
>> With regards,
>> S K Pradeep kumar,
>>
>>
>>
>> On Thu, Nov 12, 2009 at 8:05 PM, su2 <sh...@pexsupply.com> wrote:
>>
>> >
>> > Hi Pradeep,
>> >
>> > Sorry still little confuse. How am I going to use DemoProduct.xml file?
>> I
>> > understood the file I can have all my required data there, but after
>> that
>> > what should I be doing in order to insert the data in entities?
>> >
>> > Thank you.
>> > Shuchi -
>> >
>> > S K Pradeep kumar wrote:
>> > >
>> > > you can find the xml filse in the data folder in all the
>> application/*
>> > > module , to create the new products use DemoProduct.xml file.
>> > >
>> > > With regards,
>> > > S K Pradeep kumar,
>> > >
>> > >
>> > >
>> > > On Thu, Nov 12, 2009 at 7:12 PM, su2 <sh...@pexsupply.com> wrote:
>> > >
>> > >>
>> > >> Hi Pradeep,
>> > >>
>> > >> Thanks for your response.
>> > >>
>> > >> Sorry I am new to ofbiz. But I am not sure where to define the
>> entity
>> > >> names
>> > >> and the entity-field names where I want to insert the data(as all
>> the
>> > >> information like price and others for the product is not stored in
>> only
>> > >> 'Product' entity, I need to insert the data in multiple entities)?
>> > >>
>> > >> Do I have to specify in XML? Do you have any sample xml file? I want
>> to
>> > >> insert data for new products
>> > >>
>> > >> Also, I see entity "DataImportProduct". What is that for?
>> > >>
>> > >> Once again thank you for the help.
>> > >>
>> > >> Shuchi-
>> > >>
>> > >>
>> > >> S K Pradeep kumar wrote:
>> > >> >
>> > >> > Hi shuchi,
>> > >> >
>> > >> > Convert your .cvs to xml and use webtools entity import.
>> > >> >
>> > >> > With regards,
>> > >> > S K Pradeep kumar,
>> > >> >
>> > >> >
>> > >> >
>> > >> > On Thu, Nov 12, 2009 at 12:52 AM, su2 <sh...@pexsupply.com>
>> wrote:
>> > >> >
>> > >> >>
>> > >> >> Hi Jacques,
>> > >> >>
>> > >> >> I want to insert all the excel data into the database. I looked
>> at
>> > >> >> viewdatafile screen, and looked like, using that I can not insert
>> all
>> > >> my
>> > >> >> excel data into database tables.
>> > >> >>
>> > >> >>
>> > >> >> Jacques Le Roux wrote:
>> > >> >> >
>> > >> >> > I'd use CSV with
>> > >> https://localhost:8443/webtools/control/viewdatafile
>> > >> .
>> > >> >> I
>> > >> >> > let you find more on this ML archives...
>> > >> >> >
>> > >> >> > Jacques
>> > >> >> >
>> > >> >> > From: "su2" <sh...@pexsupply.com>
>> > >> >> >>
>> > >> >> >> Hi Nalin,
>> > >> >> >>
>> > >> >> >> We have similar requirement where we want to upload/import all
>> the
>> > >> >> >> product
>> > >> >> >> information from excel sheet.
>> > >> >> >>
>> > >> >> >> I would really appreciate if you could tell me, how we can
>> achieve
>> > >> >> that.
>> > >> >> >>
>> > >> >> >> Thank you.
>> > >> >> >> Shuchi-
>> > >> >> >>
>> > >> >> >> Nalin Chandra wrote:
>> > >> >> >>>
>> > >> >> >>> Hi All
>> > >> >> >>>
>> > >> >> >>> I want to import the product related data from excel sheet. I
>> did
>> > >> it
>> > >> >> but
>> > >> >> >>> i
>> > >> >> >>> have one problem in this.
>> > >> >> >>> I excel sheet i have absolute path of image now i want to
>> upload
>> > >> the
>> > >> >> >>> image
>> > >> >> >>> from that absolute path to our application as well store the
>> > >> relative
>> > >> >> >>> path
>> > >> >> >>> in data base as we did in product content section.
>> > >> >> >>> i am able to store the relative path in database but image in
>> not
>> > >> >> >>> uploading in the required folder.
>> > >> >> >>>
>> > >> >> >>> Main problem is that here i am not using any html form.
>> > >> >> >>>
>> > >> >> >>> i paste the code below for it
>> > >> >> >>>
>> > >> >> >>> public static String uploadCategory(HttpServletRequest
>> request,
>> > >> >> >>> HttpServletResponse response) {
>> > >> >> >>>
>> > >> >> >>> try
>> > >> >> >>> {
>> > >> >> >>> String filename = "D:/category.xls";
>> > >> >> >>> WorkbookSettings ws = new WorkbookSettings();
>> > >> >> >>> ws.setLocale(new Locale("en", "EN"));
>> > >> >> >>> Workbook workbook = Workbook.getWorkbook(new
>> > >> >> >>> File(filename),ws);
>> > >> >> >>> Sheet s = workbook.getSheet(0);
>> > >> >> >>> readCategoryDataSheet(s, request);
>> > >> >> >>> workbook.close();
>> > >> >> >>> }
>> > >> >> >>> catch (IOException e)
>> > >> >> >>> {
>> > >> >> >>> e.printStackTrace();
>> > >> >> >>> }
>> > >> >> >>> catch (BiffException e)
>> > >> >> >>> {
>> > >> >> >>> e.printStackTrace();
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> return "success";
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> private static void readCategoryDataSheet(Sheet s,
>> > >> >> >>> HttpServletRequest
>> > >> >> >>> request)
>> > >> >> >>> {
>> > >> >> >>> Cell rowData[] = null;
>> > >> >> >>> int successCount = 0;
>> > >> >> >>> int failCount = 0;
>> > >> >> >>> int rows = s.getRows();
>> > >> >> >>> int column = s.getColumns();
>> > >> >> >>> String productCategoryId = "";
>> > >> >> >>> String productCategoryTypeId = "";
>> > >> >> >>> String primaryParentCategoryId = "";
>> > >> >> >>> String categoryName = "";
>> > >> >> >>> String description = "";
>> > >> >> >>> String longDescription = "";
>> > >> >> >>> String categoryImageUrl = "";
>> > >> >> >>> String linkOneImageUrl = "";
>> > >> >> >>> String prodCatalogId = "";
>> > >> >> >>> String prodCatalogCategoryTypeId = "";
>> > >> >> >>> String sequenceNum = "";
>> > >> >> >>>
>> > >> >> >>> for (int i = 1; i < rows; i++) {
>> > >> >> >>> rowData = s.getRow(i);
>> > >> >> >>> if (rowData[0].getContents().length()
>> !=
>> > >> 0)
>> > >> {
>> > >> >> >>> for (int j = 0; j < column;
>> j++)
>> > {
>> > >> >> >>> switch (j) {
>> > >> >> >>> case 0:
>> > >> >> >>>
>> productCategoryId
>> > >> =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 1:
>> > >> >> >>>
>> > >> productCategoryTypeId
>> > >> >> =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 2:
>> > >> >> >>>
>> > >> >> primaryParentCategoryId
>> > >> >> >>> =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 3:
>> > >> >> >>> categoryName
>> =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 4:
>> > >> >> >>> description =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 5:
>> > >> >> >>>
>> longDescription
>> =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 6:
>> > >> >> >>>
>> categoryImageUrl
>> > =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> case 7:
>> > >> >> >>>
>> linkOneImageUrl
>> =
>> > >> >> >>> rowData[j].getContents();
>> > >> >> >>> default:
>> > >> >> >>> break;
>> > >> >> >>> }
>> > >> >> >>> }
>> > >> >> >>> }
>> > >> >> >>> String imageFilenameFormat =
>> > >> >> >>> UtilProperties.getPropertyValue("catalog",
>> > >> "image.filename.format");
>> > >> >> >>> String imageServerPath =
>> > >> >> >>> UtilProperties.getPropertyValue("catalog",
>> "image.server.path");
>> > >> >> >>> String imageUrlPrefix =
>> > >> >> >>> UtilProperties.getPropertyValue("catalog",
>> "image.url.prefix");
>> > >> >> >>>
>> > >> >> >>>
>> > >> >> >>> // upload image
>> > >> >> >>> FlexibleStringExpander
>> filenameExpander
>> =
>> > >> new
>> > >> >> >>> FlexibleStringExpander(imageFilenameFormat);
>> > >> >> >>> String catImageUrl = "";
>> > >> >> >>> String linkImageUrl = "";
>> > >> >> >>> if(categoryImageUrl != null &&
>> > >> >> >>> categoryImageUrl.length() > 0){
>> > >> >> >>> Object forLock = new
>> Object();
>> > >> >> >>> String contentType = null;
>> > >> >> >>> String categoryImageLocation
>> =
>> > >> >> >>> filenameExpander.expandString(UtilMisc.toMap("location",
>> > >> >> "categories",
>> > >> >> >>> "type", "category", "id", productCategoryId));
>> > >> >> >>> String filePathPrefix = "";
>> > >> >> >>> String filenameToUse =
>> > >> >> >>> categoryImageLocation;
>> > >> >> >>> if
>> > >> >> (categoryImageLocation.lastIndexOf("/")
>> > >> >> >>> !=
>> > >> >> >>> -1) {
>> > >> >> >>> filePathPrefix =
>> > >> >> >>> categoryImageLocation.substring(0,
>> > >> >> >>> categoryImageLocation.lastIndexOf("/")
>> > >> >> >>> + 1); // adding 1 to include the trailing slash
>> > >> >> >>> filenameToUse =
>> > >> >> >>>
>> > >> >>
>> > categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/")
>> > >> >> +
>> > >> >> >>> 1);
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> int i1;
>> > >> >> >>> if (contentType != null && (i1 =
>> > >> >> >>> contentType.indexOf("boundary=")) != -1) {
>> > >> >> >>> contentType =
>> > >> >> contentType.substring(i1
>> > >> >> +
>> > >> >> >>> 9);
>> > >> >> >>> contentType = "--" +
>> contentType;
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> String defaultFileName =
>> > filenameToUse
>> > >> +
>> > >> >> >>> "_temp";
>> > >> >> >>> HttpRequestFileUpload
>> uploadObject
>> =
>> > >> new
>> > >> >> >>> HttpRequestFileUpload();
>> > >> >> >>>
>> > >> >> >>> uploadObject.setOverrideFilename(defaultFileName);
>> > >> >> >>>
>> > >> uploadObject.setSavePath(imageServerPath
>> > >> >> +
>> > >> >> >>> "/"
>> > >> >> >>> + filePathPrefix);
>> > >> >> >>> try{
>> > >> >> >>> uploadObject.doUpload(request);
>> > >> >> >>>
>> > >> >> >>> }catch(IOException e){
>> > >> >> >>> Debug.logInfo("Image uploading
>> > >> failure",
>> > >> >> >>> module);
>> > >> >> >>> }
>> > >> >> >>> String categoryImageFileName =
>> > >> >> >>> uploadObject.getFilename();
>> > >> >> >>>
>> > >> >> >>> if (categoryImageFileName != null
>> &&
>> > >> >> >>> categoryImageFileName.length() > 0) {
>> > >> >> >>> if
>> > >> >> >>> (categoryImageFileName.lastIndexOf(".")
>> > >> >> >>> > 0 && categoryImageFileName.lastIndexOf(".") <
>> > >> >> >>> categoryImageFileName.length()) {
>> > >> >> >>> filenameToUse +=
>> > >> >> >>>
>> > >> >>
>> > >>
>> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
>> > >> >> >>> } else {
>> > >> >> >>> filenameToUse += ".jpg";
>> > >> >> >>> }
>> > >> >> >>> try{
>> > >> >> >>> String characterEncoding =
>> > >> >> >>> request.getCharacterEncoding();
>> > >> >> >>> catImageUrl = imageUrlPrefix
>> +
>> > "/"
>> > >> +
>> > >> >> >>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
>> > >> >> >>> characterEncoding);
>> > >> >> >>> }catch(Exception e){
>> > >> >> >>> System.out.println("Incoding
>> > >> >> Problem");
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> try {
>> > >> >> >>> File file = new
>> > >> >> File(imageServerPath
>> > >> >> >>> +
>> > >> >> >>> "/" + filePathPrefix, defaultFileName);
>> > >> >> >>> File file1 = new
>> > >> >> >>> File(imageServerPath
>> > >> >> >>> + "/" + filePathPrefix, filenameToUse);
>> > >> >> >>> try {
>> > >> >> >>> file1.delete();
>> > >> >> >>> } catch(Exception e) {
>> > >> >> >>>
>> System.out.println("error
>> > >> >> >>> deleting
>> > >> >> >>> existing file (not neccessarily a problem)");
>> > >> >> >>> }
>> > >> >> >>> file.renameTo(file1);
>> > >> >> >>> } catch(Exception e) {
>> > >> >> >>> e.printStackTrace();
>> > >> >> >>> }
>> > >> >> >>> }
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> // end of upload image
>> > >> >> >>>
>> > >> >> >>> Timestamp fromDate =
>> UtilDateTime.nowTimestamp();
>> > >> >> >>> GenericDelegator delegator = (GenericDelegator)
>> > >> >> >>> request.getAttribute("delegator");
>> > >> >> >>> try {
>> > >> >> >>> GenericValue DataImportCategoryList =
>> > >> >> >>> delegator.findByPrimaryKey("DataImportCategory",
>> > >> >> >>> UtilMisc.toMap("productCategoryId", productCategoryId));
>> > >> >> >>>
>> > >> >> >>> if(DataImportCategoryList != null){
>> > >> >> >>> String categoryId =
>> > >> >> >>> DataImportCategoryList.getString("productCategoryId");
>> > >> >> >>>
>> if(categoryId.equals(productCategoryId)){
>> > >> >> >>> failCount++;
>> > >> >> >>> }
>> > >> >> >>> }else {
>> > >> >> >>> GenericValue newImportCategory =
>> > >> >> >>> delegator.makeValue("DataImportCategory", null);
>> > >> >> >>>
>> > >> >> >>> newImportCategory.set("productCategoryId",
>> > >> >> >>> productCategoryId.trim());
>> > >> >> >>> if(productCategoryTypeId != null &&
>> > >> >> >>> productCategoryTypeId.length()
>> > >> >> >>> > 0){
>> > >> >> >>> newImportCategory.set("productCategoryTypeId",
>> > >> >> >>> productCategoryTypeId.trim());
>> > >> >> >>> } else {
>> > >> >> >>>
>> > >> >> >>> newImportCategory.set("productCategoryTypeId",
>> > >> "CATALOG_CATEGORY");
>> > >> >> >>> }
>> > >> >> >>> if(primaryParentCategoryId != null &&
>> > >> >> >>> primaryParentCategoryId.length() > 0)
>> > >> >> >>> newImportCategory.set("primaryParentCategoryId",
>> > >> >> >>> primaryParentCategoryId);
>> > >> >> >>> newImportCategory.set("categoryName",
>> > >> categoryName.trim());
>> > >> >> >>> newImportCategory.set("description", description);
>> > >> >> >>> newImportCategory.set("longDescription",
>> > longDescription);
>> > >> >> >>> newImportCategory.set("categoryImageUrl",
>> catImageUrl);
>> > >> >> >>> newImportCategory.set("linkOneImageUrl",
>> linkImageUrl);
>> > >> >> >>>
>> > >> newImportCategory.set("fromDate",
>> > >> >> >>> fromDate);
>> > >> >> >>> try {
>> > >> >> >>>
>> > >> >> >>> delegator.create(newImportCategory);
>> > >> >> >>>
>> > >> Debug.logInfo("Successfully
>> > >> >> >>> imported category ["+productCategoryId+" from row no "+ i+1
>> > +"].",
>> > >> >> >>> module);
>> > >> >> >>> successCount++;
>> > >> >> >>> } catch
>> > >> (GenericEntityException
>> > >> >> e)
>> > >> >> >>> {
>> > >> >> >>>
>> > >> >> >>> Debug.logWarning(e.getMessage(),
>> > >> >> >>> module);
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> }
>> > >> >> >>> } catch(GenericEntityException e) {
>> > >> >> >>> Debug.logError("Exception occured :"+e.getMessage(),
>> > >> module);
>> > >> >> >>> }
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>> }
>> > >> >> >>>
>> > >> >> >>>
>> > >> >> >>> it create the temp file in corresponding directory but there
>> is
>> > no
>> > >> >> >>> actual
>> > >> >> >>> image at that path.
>> > >> >> >>>
>> > >> >> >>> So any one have any idea regarding this please suggest me.
>> > >> >> >>>
>> > >> >> >>>
>> > >> >> >>> Thanks.
>> > >> >> >>>
>> > >> >> >>> Nalin Chandra
>> > >> >> >>>
>> > >> >> >>
>> > >> >> >> --
>> > >> >> >> View this message in context:
>> > >> >> >>
>> > >> >>
>> > >>
>> >
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
>> > >> >> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
>> > >> >> >>
>> > >> >> >
>> > >> >> >
>> > >> >> >
>> > >> >>
>> > >> >> --
>> > >> >> View this message in context:
>> > >> >>
>> > >>
>> >
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p586989.html
>> > >> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
>> > >> >>
>> > >> >
>> > >> >
>> > >>
>> > >> --
>> > >> View this message in context:
>> > >>
>> >
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620283.html
>> > >> Sent from the OFBiz - User mailing list archive at Nabble.com.
>> > >>
>> > >
>> > >
>> >
>> > --
>> > View this message in context:
>> >
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620305.html
>> > Sent from the OFBiz - User mailing list archive at Nabble.com.
>> >
>>
>
>
--
View this message in context: http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p621134.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: how to import data from excel sheet
Posted by Rishi Solanki <ri...@gmail.com>.
Hi,
Look at the ImportProductServices.java, here you will get the
productImportFromSpreadsheet() service to import the product data from Excel
sheet.
You just need to put your Excel sheet in your ${OFBIZ-HOME}/spreadsheet
directory (need to create). Now go to webtools run service
productImportFromSpreadsheet without passing any parameter. If your Excell
Sheet has some other Product entity field then those needs to be handle by
modifying the service.
Rishi Solanki
Enterprise Software Developer
HotWax Media Pvt. Ltd.
On Fri, Nov 13, 2009 at 10:57 AM, S K Pradeep kumar <
s.k.pradeepkumar@gmail.com> wrote:
> Study about webtools there is one data import facility study that and use
> it
> for your requirements.
>
>
> With regards,
> S K Pradeep kumar,
>
>
>
> On Thu, Nov 12, 2009 at 8:05 PM, su2 <sh...@pexsupply.com> wrote:
>
> >
> > Hi Pradeep,
> >
> > Sorry still little confuse. How am I going to use DemoProduct.xml file? I
> > understood the file I can have all my required data there, but after that
> > what should I be doing in order to insert the data in entities?
> >
> > Thank you.
> > Shuchi -
> >
> > S K Pradeep kumar wrote:
> > >
> > > you can find the xml filse in the data folder in all the application/*
> > > module , to create the new products use DemoProduct.xml file.
> > >
> > > With regards,
> > > S K Pradeep kumar,
> > >
> > >
> > >
> > > On Thu, Nov 12, 2009 at 7:12 PM, su2 <sh...@pexsupply.com> wrote:
> > >
> > >>
> > >> Hi Pradeep,
> > >>
> > >> Thanks for your response.
> > >>
> > >> Sorry I am new to ofbiz. But I am not sure where to define the entity
> > >> names
> > >> and the entity-field names where I want to insert the data(as all the
> > >> information like price and others for the product is not stored in
> only
> > >> 'Product' entity, I need to insert the data in multiple entities)?
> > >>
> > >> Do I have to specify in XML? Do you have any sample xml file? I want
> to
> > >> insert data for new products
> > >>
> > >> Also, I see entity "DataImportProduct". What is that for?
> > >>
> > >> Once again thank you for the help.
> > >>
> > >> Shuchi-
> > >>
> > >>
> > >> S K Pradeep kumar wrote:
> > >> >
> > >> > Hi shuchi,
> > >> >
> > >> > Convert your .cvs to xml and use webtools entity import.
> > >> >
> > >> > With regards,
> > >> > S K Pradeep kumar,
> > >> >
> > >> >
> > >> >
> > >> > On Thu, Nov 12, 2009 at 12:52 AM, su2 <sh...@pexsupply.com> wrote:
> > >> >
> > >> >>
> > >> >> Hi Jacques,
> > >> >>
> > >> >> I want to insert all the excel data into the database. I looked at
> > >> >> viewdatafile screen, and looked like, using that I can not insert
> all
> > >> my
> > >> >> excel data into database tables.
> > >> >>
> > >> >>
> > >> >> Jacques Le Roux wrote:
> > >> >> >
> > >> >> > I'd use CSV with
> > >> https://localhost:8443/webtools/control/viewdatafile
> > >> .
> > >> >> I
> > >> >> > let you find more on this ML archives...
> > >> >> >
> > >> >> > Jacques
> > >> >> >
> > >> >> > From: "su2" <sh...@pexsupply.com>
> > >> >> >>
> > >> >> >> Hi Nalin,
> > >> >> >>
> > >> >> >> We have similar requirement where we want to upload/import all
> the
> > >> >> >> product
> > >> >> >> information from excel sheet.
> > >> >> >>
> > >> >> >> I would really appreciate if you could tell me, how we can
> achieve
> > >> >> that.
> > >> >> >>
> > >> >> >> Thank you.
> > >> >> >> Shuchi-
> > >> >> >>
> > >> >> >> Nalin Chandra wrote:
> > >> >> >>>
> > >> >> >>> Hi All
> > >> >> >>>
> > >> >> >>> I want to import the product related data from excel sheet. I
> did
> > >> it
> > >> >> but
> > >> >> >>> i
> > >> >> >>> have one problem in this.
> > >> >> >>> I excel sheet i have absolute path of image now i want to
> upload
> > >> the
> > >> >> >>> image
> > >> >> >>> from that absolute path to our application as well store the
> > >> relative
> > >> >> >>> path
> > >> >> >>> in data base as we did in product content section.
> > >> >> >>> i am able to store the relative path in database but image in
> not
> > >> >> >>> uploading in the required folder.
> > >> >> >>>
> > >> >> >>> Main problem is that here i am not using any html form.
> > >> >> >>>
> > >> >> >>> i paste the code below for it
> > >> >> >>>
> > >> >> >>> public static String uploadCategory(HttpServletRequest request,
> > >> >> >>> HttpServletResponse response) {
> > >> >> >>>
> > >> >> >>> try
> > >> >> >>> {
> > >> >> >>> String filename = "D:/category.xls";
> > >> >> >>> WorkbookSettings ws = new WorkbookSettings();
> > >> >> >>> ws.setLocale(new Locale("en", "EN"));
> > >> >> >>> Workbook workbook = Workbook.getWorkbook(new
> > >> >> >>> File(filename),ws);
> > >> >> >>> Sheet s = workbook.getSheet(0);
> > >> >> >>> readCategoryDataSheet(s, request);
> > >> >> >>> workbook.close();
> > >> >> >>> }
> > >> >> >>> catch (IOException e)
> > >> >> >>> {
> > >> >> >>> e.printStackTrace();
> > >> >> >>> }
> > >> >> >>> catch (BiffException e)
> > >> >> >>> {
> > >> >> >>> e.printStackTrace();
> > >> >> >>> }
> > >> >> >>>
> > >> >> >>> return "success";
> > >> >> >>> }
> > >> >> >>>
> > >> >> >>> private static void readCategoryDataSheet(Sheet s,
> > >> >> >>> HttpServletRequest
> > >> >> >>> request)
> > >> >> >>> {
> > >> >> >>> Cell rowData[] = null;
> > >> >> >>> int successCount = 0;
> > >> >> >>> int failCount = 0;
> > >> >> >>> int rows = s.getRows();
> > >> >> >>> int column = s.getColumns();
> > >> >> >>> String productCategoryId = "";
> > >> >> >>> String productCategoryTypeId = "";
> > >> >> >>> String primaryParentCategoryId = "";
> > >> >> >>> String categoryName = "";
> > >> >> >>> String description = "";
> > >> >> >>> String longDescription = "";
> > >> >> >>> String categoryImageUrl = "";
> > >> >> >>> String linkOneImageUrl = "";
> > >> >> >>> String prodCatalogId = "";
> > >> >> >>> String prodCatalogCategoryTypeId = "";
> > >> >> >>> String sequenceNum = "";
> > >> >> >>>
> > >> >> >>> for (int i = 1; i < rows; i++) {
> > >> >> >>> rowData = s.getRow(i);
> > >> >> >>> if (rowData[0].getContents().length()
> !=
> > >> 0)
> > >> {
> > >> >> >>> for (int j = 0; j < column;
> j++)
> > {
> > >> >> >>> switch (j) {
> > >> >> >>> case 0:
> > >> >> >>>
> productCategoryId
> > >> =
> > >> >> >>> rowData[j].getContents();
> > >> >> >>> case 1:
> > >> >> >>>
> > >> productCategoryTypeId
> > >> >> =
> > >> >> >>> rowData[j].getContents();
> > >> >> >>> case 2:
> > >> >> >>>
> > >> >> primaryParentCategoryId
> > >> >> >>> =
> > >> >> >>> rowData[j].getContents();
> > >> >> >>> case 3:
> > >> >> >>> categoryName =
> > >> >> >>> rowData[j].getContents();
> > >> >> >>> case 4:
> > >> >> >>> description =
> > >> >> >>> rowData[j].getContents();
> > >> >> >>> case 5:
> > >> >> >>> longDescription
> =
> > >> >> >>> rowData[j].getContents();
> > >> >> >>> case 6:
> > >> >> >>>
> categoryImageUrl
> > =
> > >> >> >>> rowData[j].getContents();
> > >> >> >>> case 7:
> > >> >> >>> linkOneImageUrl
> =
> > >> >> >>> rowData[j].getContents();
> > >> >> >>> default:
> > >> >> >>> break;
> > >> >> >>> }
> > >> >> >>> }
> > >> >> >>> }
> > >> >> >>> String imageFilenameFormat =
> > >> >> >>> UtilProperties.getPropertyValue("catalog",
> > >> "image.filename.format");
> > >> >> >>> String imageServerPath =
> > >> >> >>> UtilProperties.getPropertyValue("catalog",
> "image.server.path");
> > >> >> >>> String imageUrlPrefix =
> > >> >> >>> UtilProperties.getPropertyValue("catalog", "image.url.prefix");
> > >> >> >>>
> > >> >> >>>
> > >> >> >>> // upload image
> > >> >> >>> FlexibleStringExpander filenameExpander
> =
> > >> new
> > >> >> >>> FlexibleStringExpander(imageFilenameFormat);
> > >> >> >>> String catImageUrl = "";
> > >> >> >>> String linkImageUrl = "";
> > >> >> >>> if(categoryImageUrl != null &&
> > >> >> >>> categoryImageUrl.length() > 0){
> > >> >> >>> Object forLock = new Object();
> > >> >> >>> String contentType = null;
> > >> >> >>> String categoryImageLocation =
> > >> >> >>> filenameExpander.expandString(UtilMisc.toMap("location",
> > >> >> "categories",
> > >> >> >>> "type", "category", "id", productCategoryId));
> > >> >> >>> String filePathPrefix = "";
> > >> >> >>> String filenameToUse =
> > >> >> >>> categoryImageLocation;
> > >> >> >>> if
> > >> >> (categoryImageLocation.lastIndexOf("/")
> > >> >> >>> !=
> > >> >> >>> -1) {
> > >> >> >>> filePathPrefix =
> > >> >> >>> categoryImageLocation.substring(0,
> > >> >> >>> categoryImageLocation.lastIndexOf("/")
> > >> >> >>> + 1); // adding 1 to include the trailing slash
> > >> >> >>> filenameToUse =
> > >> >> >>>
> > >> >>
> > categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/")
> > >> >> +
> > >> >> >>> 1);
> > >> >> >>> }
> > >> >> >>>
> > >> >> >>> int i1;
> > >> >> >>> if (contentType != null && (i1 =
> > >> >> >>> contentType.indexOf("boundary=")) != -1) {
> > >> >> >>> contentType =
> > >> >> contentType.substring(i1
> > >> >> +
> > >> >> >>> 9);
> > >> >> >>> contentType = "--" +
> contentType;
> > >> >> >>> }
> > >> >> >>>
> > >> >> >>> String defaultFileName =
> > filenameToUse
> > >> +
> > >> >> >>> "_temp";
> > >> >> >>> HttpRequestFileUpload uploadObject
> =
> > >> new
> > >> >> >>> HttpRequestFileUpload();
> > >> >> >>>
> > >> >> >>> uploadObject.setOverrideFilename(defaultFileName);
> > >> >> >>>
> > >> uploadObject.setSavePath(imageServerPath
> > >> >> +
> > >> >> >>> "/"
> > >> >> >>> + filePathPrefix);
> > >> >> >>> try{
> > >> >> >>> uploadObject.doUpload(request);
> > >> >> >>>
> > >> >> >>> }catch(IOException e){
> > >> >> >>> Debug.logInfo("Image uploading
> > >> failure",
> > >> >> >>> module);
> > >> >> >>> }
> > >> >> >>> String categoryImageFileName =
> > >> >> >>> uploadObject.getFilename();
> > >> >> >>>
> > >> >> >>> if (categoryImageFileName != null
> &&
> > >> >> >>> categoryImageFileName.length() > 0) {
> > >> >> >>> if
> > >> >> >>> (categoryImageFileName.lastIndexOf(".")
> > >> >> >>> > 0 && categoryImageFileName.lastIndexOf(".") <
> > >> >> >>> categoryImageFileName.length()) {
> > >> >> >>> filenameToUse +=
> > >> >> >>>
> > >> >>
> > >>
> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
> > >> >> >>> } else {
> > >> >> >>> filenameToUse += ".jpg";
> > >> >> >>> }
> > >> >> >>> try{
> > >> >> >>> String characterEncoding =
> > >> >> >>> request.getCharacterEncoding();
> > >> >> >>> catImageUrl = imageUrlPrefix +
> > "/"
> > >> +
> > >> >> >>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
> > >> >> >>> characterEncoding);
> > >> >> >>> }catch(Exception e){
> > >> >> >>> System.out.println("Incoding
> > >> >> Problem");
> > >> >> >>> }
> > >> >> >>>
> > >> >> >>> try {
> > >> >> >>> File file = new
> > >> >> File(imageServerPath
> > >> >> >>> +
> > >> >> >>> "/" + filePathPrefix, defaultFileName);
> > >> >> >>> File file1 = new
> > >> >> >>> File(imageServerPath
> > >> >> >>> + "/" + filePathPrefix, filenameToUse);
> > >> >> >>> try {
> > >> >> >>> file1.delete();
> > >> >> >>> } catch(Exception e) {
> > >> >> >>>
> System.out.println("error
> > >> >> >>> deleting
> > >> >> >>> existing file (not neccessarily a problem)");
> > >> >> >>> }
> > >> >> >>> file.renameTo(file1);
> > >> >> >>> } catch(Exception e) {
> > >> >> >>> e.printStackTrace();
> > >> >> >>> }
> > >> >> >>> }
> > >> >> >>> }
> > >> >> >>>
> > >> >> >>> // end of upload image
> > >> >> >>>
> > >> >> >>> Timestamp fromDate = UtilDateTime.nowTimestamp();
> > >> >> >>> GenericDelegator delegator = (GenericDelegator)
> > >> >> >>> request.getAttribute("delegator");
> > >> >> >>> try {
> > >> >> >>> GenericValue DataImportCategoryList =
> > >> >> >>> delegator.findByPrimaryKey("DataImportCategory",
> > >> >> >>> UtilMisc.toMap("productCategoryId", productCategoryId));
> > >> >> >>>
> > >> >> >>> if(DataImportCategoryList != null){
> > >> >> >>> String categoryId =
> > >> >> >>> DataImportCategoryList.getString("productCategoryId");
> > >> >> >>> if(categoryId.equals(productCategoryId)){
> > >> >> >>> failCount++;
> > >> >> >>> }
> > >> >> >>> }else {
> > >> >> >>> GenericValue newImportCategory =
> > >> >> >>> delegator.makeValue("DataImportCategory", null);
> > >> >> >>>
> > >> >> >>> newImportCategory.set("productCategoryId",
> > >> >> >>> productCategoryId.trim());
> > >> >> >>> if(productCategoryTypeId != null &&
> > >> >> >>> productCategoryTypeId.length()
> > >> >> >>> > 0){
> > >> >> >>> newImportCategory.set("productCategoryTypeId",
> > >> >> >>> productCategoryTypeId.trim());
> > >> >> >>> } else {
> > >> >> >>>
> > >> >> >>> newImportCategory.set("productCategoryTypeId",
> > >> "CATALOG_CATEGORY");
> > >> >> >>> }
> > >> >> >>> if(primaryParentCategoryId != null &&
> > >> >> >>> primaryParentCategoryId.length() > 0)
> > >> >> >>> newImportCategory.set("primaryParentCategoryId",
> > >> >> >>> primaryParentCategoryId);
> > >> >> >>> newImportCategory.set("categoryName",
> > >> categoryName.trim());
> > >> >> >>> newImportCategory.set("description", description);
> > >> >> >>> newImportCategory.set("longDescription",
> > longDescription);
> > >> >> >>> newImportCategory.set("categoryImageUrl", catImageUrl);
> > >> >> >>> newImportCategory.set("linkOneImageUrl", linkImageUrl);
> > >> >> >>>
> > >> newImportCategory.set("fromDate",
> > >> >> >>> fromDate);
> > >> >> >>> try {
> > >> >> >>>
> > >> >> >>> delegator.create(newImportCategory);
> > >> >> >>>
> > >> Debug.logInfo("Successfully
> > >> >> >>> imported category ["+productCategoryId+" from row no "+ i+1
> > +"].",
> > >> >> >>> module);
> > >> >> >>> successCount++;
> > >> >> >>> } catch
> > >> (GenericEntityException
> > >> >> e)
> > >> >> >>> {
> > >> >> >>>
> > >> >> >>> Debug.logWarning(e.getMessage(),
> > >> >> >>> module);
> > >> >> >>> }
> > >> >> >>>
> > >> >> >>> }
> > >> >> >>> } catch(GenericEntityException e) {
> > >> >> >>> Debug.logError("Exception occured :"+e.getMessage(),
> > >> module);
> > >> >> >>> }
> > >> >> >>> }
> > >> >> >>>
> > >> >> >>> }
> > >> >> >>>
> > >> >> >>>
> > >> >> >>> it create the temp file in corresponding directory but there is
> > no
> > >> >> >>> actual
> > >> >> >>> image at that path.
> > >> >> >>>
> > >> >> >>> So any one have any idea regarding this please suggest me.
> > >> >> >>>
> > >> >> >>>
> > >> >> >>> Thanks.
> > >> >> >>>
> > >> >> >>> Nalin Chandra
> > >> >> >>>
> > >> >> >>
> > >> >> >> --
> > >> >> >> View this message in context:
> > >> >> >>
> > >> >>
> > >>
> >
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
> > >> >> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
> > >> >> >>
> > >> >> >
> > >> >> >
> > >> >> >
> > >> >>
> > >> >> --
> > >> >> View this message in context:
> > >> >>
> > >>
> >
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p586989.html
> > >> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
> > >> >>
> > >> >
> > >> >
> > >>
> > >> --
> > >> View this message in context:
> > >>
> >
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620283.html
> > >> Sent from the OFBiz - User mailing list archive at Nabble.com.
> > >>
> > >
> > >
> >
> > --
> > View this message in context:
> >
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620305.html
> > Sent from the OFBiz - User mailing list archive at Nabble.com.
> >
>
Re: how to import data from excel sheet
Posted by S K Pradeep kumar <s....@gmail.com>.
Study about webtools there is one data import facility study that and use it
for your requirements.
With regards,
S K Pradeep kumar,
On Thu, Nov 12, 2009 at 8:05 PM, su2 <sh...@pexsupply.com> wrote:
>
> Hi Pradeep,
>
> Sorry still little confuse. How am I going to use DemoProduct.xml file? I
> understood the file I can have all my required data there, but after that
> what should I be doing in order to insert the data in entities?
>
> Thank you.
> Shuchi -
>
> S K Pradeep kumar wrote:
> >
> > you can find the xml filse in the data folder in all the application/*
> > module , to create the new products use DemoProduct.xml file.
> >
> > With regards,
> > S K Pradeep kumar,
> >
> >
> >
> > On Thu, Nov 12, 2009 at 7:12 PM, su2 <sh...@pexsupply.com> wrote:
> >
> >>
> >> Hi Pradeep,
> >>
> >> Thanks for your response.
> >>
> >> Sorry I am new to ofbiz. But I am not sure where to define the entity
> >> names
> >> and the entity-field names where I want to insert the data(as all the
> >> information like price and others for the product is not stored in only
> >> 'Product' entity, I need to insert the data in multiple entities)?
> >>
> >> Do I have to specify in XML? Do you have any sample xml file? I want to
> >> insert data for new products
> >>
> >> Also, I see entity "DataImportProduct". What is that for?
> >>
> >> Once again thank you for the help.
> >>
> >> Shuchi-
> >>
> >>
> >> S K Pradeep kumar wrote:
> >> >
> >> > Hi shuchi,
> >> >
> >> > Convert your .cvs to xml and use webtools entity import.
> >> >
> >> > With regards,
> >> > S K Pradeep kumar,
> >> >
> >> >
> >> >
> >> > On Thu, Nov 12, 2009 at 12:52 AM, su2 <sh...@pexsupply.com> wrote:
> >> >
> >> >>
> >> >> Hi Jacques,
> >> >>
> >> >> I want to insert all the excel data into the database. I looked at
> >> >> viewdatafile screen, and looked like, using that I can not insert all
> >> my
> >> >> excel data into database tables.
> >> >>
> >> >>
> >> >> Jacques Le Roux wrote:
> >> >> >
> >> >> > I'd use CSV with
> >> https://localhost:8443/webtools/control/viewdatafile
> >> .
> >> >> I
> >> >> > let you find more on this ML archives...
> >> >> >
> >> >> > Jacques
> >> >> >
> >> >> > From: "su2" <sh...@pexsupply.com>
> >> >> >>
> >> >> >> Hi Nalin,
> >> >> >>
> >> >> >> We have similar requirement where we want to upload/import all the
> >> >> >> product
> >> >> >> information from excel sheet.
> >> >> >>
> >> >> >> I would really appreciate if you could tell me, how we can achieve
> >> >> that.
> >> >> >>
> >> >> >> Thank you.
> >> >> >> Shuchi-
> >> >> >>
> >> >> >> Nalin Chandra wrote:
> >> >> >>>
> >> >> >>> Hi All
> >> >> >>>
> >> >> >>> I want to import the product related data from excel sheet. I did
> >> it
> >> >> but
> >> >> >>> i
> >> >> >>> have one problem in this.
> >> >> >>> I excel sheet i have absolute path of image now i want to upload
> >> the
> >> >> >>> image
> >> >> >>> from that absolute path to our application as well store the
> >> relative
> >> >> >>> path
> >> >> >>> in data base as we did in product content section.
> >> >> >>> i am able to store the relative path in database but image in not
> >> >> >>> uploading in the required folder.
> >> >> >>>
> >> >> >>> Main problem is that here i am not using any html form.
> >> >> >>>
> >> >> >>> i paste the code below for it
> >> >> >>>
> >> >> >>> public static String uploadCategory(HttpServletRequest request,
> >> >> >>> HttpServletResponse response) {
> >> >> >>>
> >> >> >>> try
> >> >> >>> {
> >> >> >>> String filename = "D:/category.xls";
> >> >> >>> WorkbookSettings ws = new WorkbookSettings();
> >> >> >>> ws.setLocale(new Locale("en", "EN"));
> >> >> >>> Workbook workbook = Workbook.getWorkbook(new
> >> >> >>> File(filename),ws);
> >> >> >>> Sheet s = workbook.getSheet(0);
> >> >> >>> readCategoryDataSheet(s, request);
> >> >> >>> workbook.close();
> >> >> >>> }
> >> >> >>> catch (IOException e)
> >> >> >>> {
> >> >> >>> e.printStackTrace();
> >> >> >>> }
> >> >> >>> catch (BiffException e)
> >> >> >>> {
> >> >> >>> e.printStackTrace();
> >> >> >>> }
> >> >> >>>
> >> >> >>> return "success";
> >> >> >>> }
> >> >> >>>
> >> >> >>> private static void readCategoryDataSheet(Sheet s,
> >> >> >>> HttpServletRequest
> >> >> >>> request)
> >> >> >>> {
> >> >> >>> Cell rowData[] = null;
> >> >> >>> int successCount = 0;
> >> >> >>> int failCount = 0;
> >> >> >>> int rows = s.getRows();
> >> >> >>> int column = s.getColumns();
> >> >> >>> String productCategoryId = "";
> >> >> >>> String productCategoryTypeId = "";
> >> >> >>> String primaryParentCategoryId = "";
> >> >> >>> String categoryName = "";
> >> >> >>> String description = "";
> >> >> >>> String longDescription = "";
> >> >> >>> String categoryImageUrl = "";
> >> >> >>> String linkOneImageUrl = "";
> >> >> >>> String prodCatalogId = "";
> >> >> >>> String prodCatalogCategoryTypeId = "";
> >> >> >>> String sequenceNum = "";
> >> >> >>>
> >> >> >>> for (int i = 1; i < rows; i++) {
> >> >> >>> rowData = s.getRow(i);
> >> >> >>> if (rowData[0].getContents().length() !=
> >> 0)
> >> {
> >> >> >>> for (int j = 0; j < column; j++)
> {
> >> >> >>> switch (j) {
> >> >> >>> case 0:
> >> >> >>> productCategoryId
> >> =
> >> >> >>> rowData[j].getContents();
> >> >> >>> case 1:
> >> >> >>>
> >> productCategoryTypeId
> >> >> =
> >> >> >>> rowData[j].getContents();
> >> >> >>> case 2:
> >> >> >>>
> >> >> primaryParentCategoryId
> >> >> >>> =
> >> >> >>> rowData[j].getContents();
> >> >> >>> case 3:
> >> >> >>> categoryName =
> >> >> >>> rowData[j].getContents();
> >> >> >>> case 4:
> >> >> >>> description =
> >> >> >>> rowData[j].getContents();
> >> >> >>> case 5:
> >> >> >>> longDescription =
> >> >> >>> rowData[j].getContents();
> >> >> >>> case 6:
> >> >> >>> categoryImageUrl
> =
> >> >> >>> rowData[j].getContents();
> >> >> >>> case 7:
> >> >> >>> linkOneImageUrl =
> >> >> >>> rowData[j].getContents();
> >> >> >>> default:
> >> >> >>> break;
> >> >> >>> }
> >> >> >>> }
> >> >> >>> }
> >> >> >>> String imageFilenameFormat =
> >> >> >>> UtilProperties.getPropertyValue("catalog",
> >> "image.filename.format");
> >> >> >>> String imageServerPath =
> >> >> >>> UtilProperties.getPropertyValue("catalog", "image.server.path");
> >> >> >>> String imageUrlPrefix =
> >> >> >>> UtilProperties.getPropertyValue("catalog", "image.url.prefix");
> >> >> >>>
> >> >> >>>
> >> >> >>> // upload image
> >> >> >>> FlexibleStringExpander filenameExpander =
> >> new
> >> >> >>> FlexibleStringExpander(imageFilenameFormat);
> >> >> >>> String catImageUrl = "";
> >> >> >>> String linkImageUrl = "";
> >> >> >>> if(categoryImageUrl != null &&
> >> >> >>> categoryImageUrl.length() > 0){
> >> >> >>> Object forLock = new Object();
> >> >> >>> String contentType = null;
> >> >> >>> String categoryImageLocation =
> >> >> >>> filenameExpander.expandString(UtilMisc.toMap("location",
> >> >> "categories",
> >> >> >>> "type", "category", "id", productCategoryId));
> >> >> >>> String filePathPrefix = "";
> >> >> >>> String filenameToUse =
> >> >> >>> categoryImageLocation;
> >> >> >>> if
> >> >> (categoryImageLocation.lastIndexOf("/")
> >> >> >>> !=
> >> >> >>> -1) {
> >> >> >>> filePathPrefix =
> >> >> >>> categoryImageLocation.substring(0,
> >> >> >>> categoryImageLocation.lastIndexOf("/")
> >> >> >>> + 1); // adding 1 to include the trailing slash
> >> >> >>> filenameToUse =
> >> >> >>>
> >> >>
> categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/")
> >> >> +
> >> >> >>> 1);
> >> >> >>> }
> >> >> >>>
> >> >> >>> int i1;
> >> >> >>> if (contentType != null && (i1 =
> >> >> >>> contentType.indexOf("boundary=")) != -1) {
> >> >> >>> contentType =
> >> >> contentType.substring(i1
> >> >> +
> >> >> >>> 9);
> >> >> >>> contentType = "--" + contentType;
> >> >> >>> }
> >> >> >>>
> >> >> >>> String defaultFileName =
> filenameToUse
> >> +
> >> >> >>> "_temp";
> >> >> >>> HttpRequestFileUpload uploadObject =
> >> new
> >> >> >>> HttpRequestFileUpload();
> >> >> >>>
> >> >> >>> uploadObject.setOverrideFilename(defaultFileName);
> >> >> >>>
> >> uploadObject.setSavePath(imageServerPath
> >> >> +
> >> >> >>> "/"
> >> >> >>> + filePathPrefix);
> >> >> >>> try{
> >> >> >>> uploadObject.doUpload(request);
> >> >> >>>
> >> >> >>> }catch(IOException e){
> >> >> >>> Debug.logInfo("Image uploading
> >> failure",
> >> >> >>> module);
> >> >> >>> }
> >> >> >>> String categoryImageFileName =
> >> >> >>> uploadObject.getFilename();
> >> >> >>>
> >> >> >>> if (categoryImageFileName != null &&
> >> >> >>> categoryImageFileName.length() > 0) {
> >> >> >>> if
> >> >> >>> (categoryImageFileName.lastIndexOf(".")
> >> >> >>> > 0 && categoryImageFileName.lastIndexOf(".") <
> >> >> >>> categoryImageFileName.length()) {
> >> >> >>> filenameToUse +=
> >> >> >>>
> >> >>
> >> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
> >> >> >>> } else {
> >> >> >>> filenameToUse += ".jpg";
> >> >> >>> }
> >> >> >>> try{
> >> >> >>> String characterEncoding =
> >> >> >>> request.getCharacterEncoding();
> >> >> >>> catImageUrl = imageUrlPrefix +
> "/"
> >> +
> >> >> >>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
> >> >> >>> characterEncoding);
> >> >> >>> }catch(Exception e){
> >> >> >>> System.out.println("Incoding
> >> >> Problem");
> >> >> >>> }
> >> >> >>>
> >> >> >>> try {
> >> >> >>> File file = new
> >> >> File(imageServerPath
> >> >> >>> +
> >> >> >>> "/" + filePathPrefix, defaultFileName);
> >> >> >>> File file1 = new
> >> >> >>> File(imageServerPath
> >> >> >>> + "/" + filePathPrefix, filenameToUse);
> >> >> >>> try {
> >> >> >>> file1.delete();
> >> >> >>> } catch(Exception e) {
> >> >> >>> System.out.println("error
> >> >> >>> deleting
> >> >> >>> existing file (not neccessarily a problem)");
> >> >> >>> }
> >> >> >>> file.renameTo(file1);
> >> >> >>> } catch(Exception e) {
> >> >> >>> e.printStackTrace();
> >> >> >>> }
> >> >> >>> }
> >> >> >>> }
> >> >> >>>
> >> >> >>> // end of upload image
> >> >> >>>
> >> >> >>> Timestamp fromDate = UtilDateTime.nowTimestamp();
> >> >> >>> GenericDelegator delegator = (GenericDelegator)
> >> >> >>> request.getAttribute("delegator");
> >> >> >>> try {
> >> >> >>> GenericValue DataImportCategoryList =
> >> >> >>> delegator.findByPrimaryKey("DataImportCategory",
> >> >> >>> UtilMisc.toMap("productCategoryId", productCategoryId));
> >> >> >>>
> >> >> >>> if(DataImportCategoryList != null){
> >> >> >>> String categoryId =
> >> >> >>> DataImportCategoryList.getString("productCategoryId");
> >> >> >>> if(categoryId.equals(productCategoryId)){
> >> >> >>> failCount++;
> >> >> >>> }
> >> >> >>> }else {
> >> >> >>> GenericValue newImportCategory =
> >> >> >>> delegator.makeValue("DataImportCategory", null);
> >> >> >>>
> >> >> >>> newImportCategory.set("productCategoryId",
> >> >> >>> productCategoryId.trim());
> >> >> >>> if(productCategoryTypeId != null &&
> >> >> >>> productCategoryTypeId.length()
> >> >> >>> > 0){
> >> >> >>> newImportCategory.set("productCategoryTypeId",
> >> >> >>> productCategoryTypeId.trim());
> >> >> >>> } else {
> >> >> >>>
> >> >> >>> newImportCategory.set("productCategoryTypeId",
> >> "CATALOG_CATEGORY");
> >> >> >>> }
> >> >> >>> if(primaryParentCategoryId != null &&
> >> >> >>> primaryParentCategoryId.length() > 0)
> >> >> >>> newImportCategory.set("primaryParentCategoryId",
> >> >> >>> primaryParentCategoryId);
> >> >> >>> newImportCategory.set("categoryName",
> >> categoryName.trim());
> >> >> >>> newImportCategory.set("description", description);
> >> >> >>> newImportCategory.set("longDescription",
> longDescription);
> >> >> >>> newImportCategory.set("categoryImageUrl", catImageUrl);
> >> >> >>> newImportCategory.set("linkOneImageUrl", linkImageUrl);
> >> >> >>>
> >> newImportCategory.set("fromDate",
> >> >> >>> fromDate);
> >> >> >>> try {
> >> >> >>>
> >> >> >>> delegator.create(newImportCategory);
> >> >> >>>
> >> Debug.logInfo("Successfully
> >> >> >>> imported category ["+productCategoryId+" from row no "+ i+1
> +"].",
> >> >> >>> module);
> >> >> >>> successCount++;
> >> >> >>> } catch
> >> (GenericEntityException
> >> >> e)
> >> >> >>> {
> >> >> >>>
> >> >> >>> Debug.logWarning(e.getMessage(),
> >> >> >>> module);
> >> >> >>> }
> >> >> >>>
> >> >> >>> }
> >> >> >>> } catch(GenericEntityException e) {
> >> >> >>> Debug.logError("Exception occured :"+e.getMessage(),
> >> module);
> >> >> >>> }
> >> >> >>> }
> >> >> >>>
> >> >> >>> }
> >> >> >>>
> >> >> >>>
> >> >> >>> it create the temp file in corresponding directory but there is
> no
> >> >> >>> actual
> >> >> >>> image at that path.
> >> >> >>>
> >> >> >>> So any one have any idea regarding this please suggest me.
> >> >> >>>
> >> >> >>>
> >> >> >>> Thanks.
> >> >> >>>
> >> >> >>> Nalin Chandra
> >> >> >>>
> >> >> >>
> >> >> >> --
> >> >> >> View this message in context:
> >> >> >>
> >> >>
> >>
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
> >> >> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
> >> >> >>
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >> --
> >> >> View this message in context:
> >> >>
> >>
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p586989.html
> >> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
> >> >>
> >> >
> >> >
> >>
> >> --
> >> View this message in context:
> >>
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620283.html
> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
> >>
> >
> >
>
> --
> View this message in context:
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620305.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
Re: how to import data from excel sheet
Posted by su2 <sh...@pexsupply.com>.
Hi Pradeep,
Sorry still little confuse. How am I going to use DemoProduct.xml file? I
understood the file I can have all my required data there, but after that
what should I be doing in order to insert the data in entities?
Thank you.
Shuchi -
S K Pradeep kumar wrote:
>
> you can find the xml filse in the data folder in all the application/*
> module , to create the new products use DemoProduct.xml file.
>
> With regards,
> S K Pradeep kumar,
>
>
>
> On Thu, Nov 12, 2009 at 7:12 PM, su2 <sh...@pexsupply.com> wrote:
>
>>
>> Hi Pradeep,
>>
>> Thanks for your response.
>>
>> Sorry I am new to ofbiz. But I am not sure where to define the entity
>> names
>> and the entity-field names where I want to insert the data(as all the
>> information like price and others for the product is not stored in only
>> 'Product' entity, I need to insert the data in multiple entities)?
>>
>> Do I have to specify in XML? Do you have any sample xml file? I want to
>> insert data for new products
>>
>> Also, I see entity "DataImportProduct". What is that for?
>>
>> Once again thank you for the help.
>>
>> Shuchi-
>>
>>
>> S K Pradeep kumar wrote:
>> >
>> > Hi shuchi,
>> >
>> > Convert your .cvs to xml and use webtools entity import.
>> >
>> > With regards,
>> > S K Pradeep kumar,
>> >
>> >
>> >
>> > On Thu, Nov 12, 2009 at 12:52 AM, su2 <sh...@pexsupply.com> wrote:
>> >
>> >>
>> >> Hi Jacques,
>> >>
>> >> I want to insert all the excel data into the database. I looked at
>> >> viewdatafile screen, and looked like, using that I can not insert all
>> my
>> >> excel data into database tables.
>> >>
>> >>
>> >> Jacques Le Roux wrote:
>> >> >
>> >> > I'd use CSV with
>> https://localhost:8443/webtools/control/viewdatafile
>> .
>> >> I
>> >> > let you find more on this ML archives...
>> >> >
>> >> > Jacques
>> >> >
>> >> > From: "su2" <sh...@pexsupply.com>
>> >> >>
>> >> >> Hi Nalin,
>> >> >>
>> >> >> We have similar requirement where we want to upload/import all the
>> >> >> product
>> >> >> information from excel sheet.
>> >> >>
>> >> >> I would really appreciate if you could tell me, how we can achieve
>> >> that.
>> >> >>
>> >> >> Thank you.
>> >> >> Shuchi-
>> >> >>
>> >> >> Nalin Chandra wrote:
>> >> >>>
>> >> >>> Hi All
>> >> >>>
>> >> >>> I want to import the product related data from excel sheet. I did
>> it
>> >> but
>> >> >>> i
>> >> >>> have one problem in this.
>> >> >>> I excel sheet i have absolute path of image now i want to upload
>> the
>> >> >>> image
>> >> >>> from that absolute path to our application as well store the
>> relative
>> >> >>> path
>> >> >>> in data base as we did in product content section.
>> >> >>> i am able to store the relative path in database but image in not
>> >> >>> uploading in the required folder.
>> >> >>>
>> >> >>> Main problem is that here i am not using any html form.
>> >> >>>
>> >> >>> i paste the code below for it
>> >> >>>
>> >> >>> public static String uploadCategory(HttpServletRequest request,
>> >> >>> HttpServletResponse response) {
>> >> >>>
>> >> >>> try
>> >> >>> {
>> >> >>> String filename = "D:/category.xls";
>> >> >>> WorkbookSettings ws = new WorkbookSettings();
>> >> >>> ws.setLocale(new Locale("en", "EN"));
>> >> >>> Workbook workbook = Workbook.getWorkbook(new
>> >> >>> File(filename),ws);
>> >> >>> Sheet s = workbook.getSheet(0);
>> >> >>> readCategoryDataSheet(s, request);
>> >> >>> workbook.close();
>> >> >>> }
>> >> >>> catch (IOException e)
>> >> >>> {
>> >> >>> e.printStackTrace();
>> >> >>> }
>> >> >>> catch (BiffException e)
>> >> >>> {
>> >> >>> e.printStackTrace();
>> >> >>> }
>> >> >>>
>> >> >>> return "success";
>> >> >>> }
>> >> >>>
>> >> >>> private static void readCategoryDataSheet(Sheet s,
>> >> >>> HttpServletRequest
>> >> >>> request)
>> >> >>> {
>> >> >>> Cell rowData[] = null;
>> >> >>> int successCount = 0;
>> >> >>> int failCount = 0;
>> >> >>> int rows = s.getRows();
>> >> >>> int column = s.getColumns();
>> >> >>> String productCategoryId = "";
>> >> >>> String productCategoryTypeId = "";
>> >> >>> String primaryParentCategoryId = "";
>> >> >>> String categoryName = "";
>> >> >>> String description = "";
>> >> >>> String longDescription = "";
>> >> >>> String categoryImageUrl = "";
>> >> >>> String linkOneImageUrl = "";
>> >> >>> String prodCatalogId = "";
>> >> >>> String prodCatalogCategoryTypeId = "";
>> >> >>> String sequenceNum = "";
>> >> >>>
>> >> >>> for (int i = 1; i < rows; i++) {
>> >> >>> rowData = s.getRow(i);
>> >> >>> if (rowData[0].getContents().length() !=
>> 0)
>> {
>> >> >>> for (int j = 0; j < column; j++) {
>> >> >>> switch (j) {
>> >> >>> case 0:
>> >> >>> productCategoryId
>> =
>> >> >>> rowData[j].getContents();
>> >> >>> case 1:
>> >> >>>
>> productCategoryTypeId
>> >> =
>> >> >>> rowData[j].getContents();
>> >> >>> case 2:
>> >> >>>
>> >> primaryParentCategoryId
>> >> >>> =
>> >> >>> rowData[j].getContents();
>> >> >>> case 3:
>> >> >>> categoryName =
>> >> >>> rowData[j].getContents();
>> >> >>> case 4:
>> >> >>> description =
>> >> >>> rowData[j].getContents();
>> >> >>> case 5:
>> >> >>> longDescription =
>> >> >>> rowData[j].getContents();
>> >> >>> case 6:
>> >> >>> categoryImageUrl =
>> >> >>> rowData[j].getContents();
>> >> >>> case 7:
>> >> >>> linkOneImageUrl =
>> >> >>> rowData[j].getContents();
>> >> >>> default:
>> >> >>> break;
>> >> >>> }
>> >> >>> }
>> >> >>> }
>> >> >>> String imageFilenameFormat =
>> >> >>> UtilProperties.getPropertyValue("catalog",
>> "image.filename.format");
>> >> >>> String imageServerPath =
>> >> >>> UtilProperties.getPropertyValue("catalog", "image.server.path");
>> >> >>> String imageUrlPrefix =
>> >> >>> UtilProperties.getPropertyValue("catalog", "image.url.prefix");
>> >> >>>
>> >> >>>
>> >> >>> // upload image
>> >> >>> FlexibleStringExpander filenameExpander =
>> new
>> >> >>> FlexibleStringExpander(imageFilenameFormat);
>> >> >>> String catImageUrl = "";
>> >> >>> String linkImageUrl = "";
>> >> >>> if(categoryImageUrl != null &&
>> >> >>> categoryImageUrl.length() > 0){
>> >> >>> Object forLock = new Object();
>> >> >>> String contentType = null;
>> >> >>> String categoryImageLocation =
>> >> >>> filenameExpander.expandString(UtilMisc.toMap("location",
>> >> "categories",
>> >> >>> "type", "category", "id", productCategoryId));
>> >> >>> String filePathPrefix = "";
>> >> >>> String filenameToUse =
>> >> >>> categoryImageLocation;
>> >> >>> if
>> >> (categoryImageLocation.lastIndexOf("/")
>> >> >>> !=
>> >> >>> -1) {
>> >> >>> filePathPrefix =
>> >> >>> categoryImageLocation.substring(0,
>> >> >>> categoryImageLocation.lastIndexOf("/")
>> >> >>> + 1); // adding 1 to include the trailing slash
>> >> >>> filenameToUse =
>> >> >>>
>> >> categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/")
>> >> +
>> >> >>> 1);
>> >> >>> }
>> >> >>>
>> >> >>> int i1;
>> >> >>> if (contentType != null && (i1 =
>> >> >>> contentType.indexOf("boundary=")) != -1) {
>> >> >>> contentType =
>> >> contentType.substring(i1
>> >> +
>> >> >>> 9);
>> >> >>> contentType = "--" + contentType;
>> >> >>> }
>> >> >>>
>> >> >>> String defaultFileName = filenameToUse
>> +
>> >> >>> "_temp";
>> >> >>> HttpRequestFileUpload uploadObject =
>> new
>> >> >>> HttpRequestFileUpload();
>> >> >>>
>> >> >>> uploadObject.setOverrideFilename(defaultFileName);
>> >> >>>
>> uploadObject.setSavePath(imageServerPath
>> >> +
>> >> >>> "/"
>> >> >>> + filePathPrefix);
>> >> >>> try{
>> >> >>> uploadObject.doUpload(request);
>> >> >>>
>> >> >>> }catch(IOException e){
>> >> >>> Debug.logInfo("Image uploading
>> failure",
>> >> >>> module);
>> >> >>> }
>> >> >>> String categoryImageFileName =
>> >> >>> uploadObject.getFilename();
>> >> >>>
>> >> >>> if (categoryImageFileName != null &&
>> >> >>> categoryImageFileName.length() > 0) {
>> >> >>> if
>> >> >>> (categoryImageFileName.lastIndexOf(".")
>> >> >>> > 0 && categoryImageFileName.lastIndexOf(".") <
>> >> >>> categoryImageFileName.length()) {
>> >> >>> filenameToUse +=
>> >> >>>
>> >>
>> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
>> >> >>> } else {
>> >> >>> filenameToUse += ".jpg";
>> >> >>> }
>> >> >>> try{
>> >> >>> String characterEncoding =
>> >> >>> request.getCharacterEncoding();
>> >> >>> catImageUrl = imageUrlPrefix + "/"
>> +
>> >> >>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
>> >> >>> characterEncoding);
>> >> >>> }catch(Exception e){
>> >> >>> System.out.println("Incoding
>> >> Problem");
>> >> >>> }
>> >> >>>
>> >> >>> try {
>> >> >>> File file = new
>> >> File(imageServerPath
>> >> >>> +
>> >> >>> "/" + filePathPrefix, defaultFileName);
>> >> >>> File file1 = new
>> >> >>> File(imageServerPath
>> >> >>> + "/" + filePathPrefix, filenameToUse);
>> >> >>> try {
>> >> >>> file1.delete();
>> >> >>> } catch(Exception e) {
>> >> >>> System.out.println("error
>> >> >>> deleting
>> >> >>> existing file (not neccessarily a problem)");
>> >> >>> }
>> >> >>> file.renameTo(file1);
>> >> >>> } catch(Exception e) {
>> >> >>> e.printStackTrace();
>> >> >>> }
>> >> >>> }
>> >> >>> }
>> >> >>>
>> >> >>> // end of upload image
>> >> >>>
>> >> >>> Timestamp fromDate = UtilDateTime.nowTimestamp();
>> >> >>> GenericDelegator delegator = (GenericDelegator)
>> >> >>> request.getAttribute("delegator");
>> >> >>> try {
>> >> >>> GenericValue DataImportCategoryList =
>> >> >>> delegator.findByPrimaryKey("DataImportCategory",
>> >> >>> UtilMisc.toMap("productCategoryId", productCategoryId));
>> >> >>>
>> >> >>> if(DataImportCategoryList != null){
>> >> >>> String categoryId =
>> >> >>> DataImportCategoryList.getString("productCategoryId");
>> >> >>> if(categoryId.equals(productCategoryId)){
>> >> >>> failCount++;
>> >> >>> }
>> >> >>> }else {
>> >> >>> GenericValue newImportCategory =
>> >> >>> delegator.makeValue("DataImportCategory", null);
>> >> >>>
>> >> >>> newImportCategory.set("productCategoryId",
>> >> >>> productCategoryId.trim());
>> >> >>> if(productCategoryTypeId != null &&
>> >> >>> productCategoryTypeId.length()
>> >> >>> > 0){
>> >> >>> newImportCategory.set("productCategoryTypeId",
>> >> >>> productCategoryTypeId.trim());
>> >> >>> } else {
>> >> >>>
>> >> >>> newImportCategory.set("productCategoryTypeId",
>> "CATALOG_CATEGORY");
>> >> >>> }
>> >> >>> if(primaryParentCategoryId != null &&
>> >> >>> primaryParentCategoryId.length() > 0)
>> >> >>> newImportCategory.set("primaryParentCategoryId",
>> >> >>> primaryParentCategoryId);
>> >> >>> newImportCategory.set("categoryName",
>> categoryName.trim());
>> >> >>> newImportCategory.set("description", description);
>> >> >>> newImportCategory.set("longDescription", longDescription);
>> >> >>> newImportCategory.set("categoryImageUrl", catImageUrl);
>> >> >>> newImportCategory.set("linkOneImageUrl", linkImageUrl);
>> >> >>>
>> newImportCategory.set("fromDate",
>> >> >>> fromDate);
>> >> >>> try {
>> >> >>>
>> >> >>> delegator.create(newImportCategory);
>> >> >>>
>> Debug.logInfo("Successfully
>> >> >>> imported category ["+productCategoryId+" from row no "+ i+1 +"].",
>> >> >>> module);
>> >> >>> successCount++;
>> >> >>> } catch
>> (GenericEntityException
>> >> e)
>> >> >>> {
>> >> >>>
>> >> >>> Debug.logWarning(e.getMessage(),
>> >> >>> module);
>> >> >>> }
>> >> >>>
>> >> >>> }
>> >> >>> } catch(GenericEntityException e) {
>> >> >>> Debug.logError("Exception occured :"+e.getMessage(),
>> module);
>> >> >>> }
>> >> >>> }
>> >> >>>
>> >> >>> }
>> >> >>>
>> >> >>>
>> >> >>> it create the temp file in corresponding directory but there is no
>> >> >>> actual
>> >> >>> image at that path.
>> >> >>>
>> >> >>> So any one have any idea regarding this please suggest me.
>> >> >>>
>> >> >>>
>> >> >>> Thanks.
>> >> >>>
>> >> >>> Nalin Chandra
>> >> >>>
>> >> >>
>> >> >> --
>> >> >> View this message in context:
>> >> >>
>> >>
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
>> >> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
>> >> >>
>> >> >
>> >> >
>> >> >
>> >>
>> >> --
>> >> View this message in context:
>> >>
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p586989.html
>> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
>> >>
>> >
>> >
>>
>> --
>> View this message in context:
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620283.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>
>
>
--
View this message in context: http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620305.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: how to import data from excel sheet
Posted by S K Pradeep kumar <s....@gmail.com>.
you can find the xml filse in the data folder in all the application/*
module , to create the new products use DemoProduct.xml file.
With regards,
S K Pradeep kumar,
On Thu, Nov 12, 2009 at 7:12 PM, su2 <sh...@pexsupply.com> wrote:
>
> Hi Pradeep,
>
> Thanks for your response.
>
> Sorry I am new to ofbiz. But I am not sure where to define the entity names
> and the entity-field names where I want to insert the data(as all the
> information like price and others for the product is not stored in only
> 'Product' entity, I need to insert the data in multiple entities)?
>
> Do I have to specify in XML? Do you have any sample xml file? I want to
> insert data for new products
>
> Also, I see entity "DataImportProduct". What is that for?
>
> Once again thank you for the help.
>
> Shuchi-
>
>
> S K Pradeep kumar wrote:
> >
> > Hi shuchi,
> >
> > Convert your .cvs to xml and use webtools entity import.
> >
> > With regards,
> > S K Pradeep kumar,
> >
> >
> >
> > On Thu, Nov 12, 2009 at 12:52 AM, su2 <sh...@pexsupply.com> wrote:
> >
> >>
> >> Hi Jacques,
> >>
> >> I want to insert all the excel data into the database. I looked at
> >> viewdatafile screen, and looked like, using that I can not insert all my
> >> excel data into database tables.
> >>
> >>
> >> Jacques Le Roux wrote:
> >> >
> >> > I'd use CSV with https://localhost:8443/webtools/control/viewdatafile
> .
> >> I
> >> > let you find more on this ML archives...
> >> >
> >> > Jacques
> >> >
> >> > From: "su2" <sh...@pexsupply.com>
> >> >>
> >> >> Hi Nalin,
> >> >>
> >> >> We have similar requirement where we want to upload/import all the
> >> >> product
> >> >> information from excel sheet.
> >> >>
> >> >> I would really appreciate if you could tell me, how we can achieve
> >> that.
> >> >>
> >> >> Thank you.
> >> >> Shuchi-
> >> >>
> >> >> Nalin Chandra wrote:
> >> >>>
> >> >>> Hi All
> >> >>>
> >> >>> I want to import the product related data from excel sheet. I did it
> >> but
> >> >>> i
> >> >>> have one problem in this.
> >> >>> I excel sheet i have absolute path of image now i want to upload the
> >> >>> image
> >> >>> from that absolute path to our application as well store the
> relative
> >> >>> path
> >> >>> in data base as we did in product content section.
> >> >>> i am able to store the relative path in database but image in not
> >> >>> uploading in the required folder.
> >> >>>
> >> >>> Main problem is that here i am not using any html form.
> >> >>>
> >> >>> i paste the code below for it
> >> >>>
> >> >>> public static String uploadCategory(HttpServletRequest request,
> >> >>> HttpServletResponse response) {
> >> >>>
> >> >>> try
> >> >>> {
> >> >>> String filename = "D:/category.xls";
> >> >>> WorkbookSettings ws = new WorkbookSettings();
> >> >>> ws.setLocale(new Locale("en", "EN"));
> >> >>> Workbook workbook = Workbook.getWorkbook(new
> >> >>> File(filename),ws);
> >> >>> Sheet s = workbook.getSheet(0);
> >> >>> readCategoryDataSheet(s, request);
> >> >>> workbook.close();
> >> >>> }
> >> >>> catch (IOException e)
> >> >>> {
> >> >>> e.printStackTrace();
> >> >>> }
> >> >>> catch (BiffException e)
> >> >>> {
> >> >>> e.printStackTrace();
> >> >>> }
> >> >>>
> >> >>> return "success";
> >> >>> }
> >> >>>
> >> >>> private static void readCategoryDataSheet(Sheet s,
> >> >>> HttpServletRequest
> >> >>> request)
> >> >>> {
> >> >>> Cell rowData[] = null;
> >> >>> int successCount = 0;
> >> >>> int failCount = 0;
> >> >>> int rows = s.getRows();
> >> >>> int column = s.getColumns();
> >> >>> String productCategoryId = "";
> >> >>> String productCategoryTypeId = "";
> >> >>> String primaryParentCategoryId = "";
> >> >>> String categoryName = "";
> >> >>> String description = "";
> >> >>> String longDescription = "";
> >> >>> String categoryImageUrl = "";
> >> >>> String linkOneImageUrl = "";
> >> >>> String prodCatalogId = "";
> >> >>> String prodCatalogCategoryTypeId = "";
> >> >>> String sequenceNum = "";
> >> >>>
> >> >>> for (int i = 1; i < rows; i++) {
> >> >>> rowData = s.getRow(i);
> >> >>> if (rowData[0].getContents().length() != 0)
> {
> >> >>> for (int j = 0; j < column; j++) {
> >> >>> switch (j) {
> >> >>> case 0:
> >> >>> productCategoryId =
> >> >>> rowData[j].getContents();
> >> >>> case 1:
> >> >>>
> productCategoryTypeId
> >> =
> >> >>> rowData[j].getContents();
> >> >>> case 2:
> >> >>>
> >> primaryParentCategoryId
> >> >>> =
> >> >>> rowData[j].getContents();
> >> >>> case 3:
> >> >>> categoryName =
> >> >>> rowData[j].getContents();
> >> >>> case 4:
> >> >>> description =
> >> >>> rowData[j].getContents();
> >> >>> case 5:
> >> >>> longDescription =
> >> >>> rowData[j].getContents();
> >> >>> case 6:
> >> >>> categoryImageUrl =
> >> >>> rowData[j].getContents();
> >> >>> case 7:
> >> >>> linkOneImageUrl =
> >> >>> rowData[j].getContents();
> >> >>> default:
> >> >>> break;
> >> >>> }
> >> >>> }
> >> >>> }
> >> >>> String imageFilenameFormat =
> >> >>> UtilProperties.getPropertyValue("catalog", "image.filename.format");
> >> >>> String imageServerPath =
> >> >>> UtilProperties.getPropertyValue("catalog", "image.server.path");
> >> >>> String imageUrlPrefix =
> >> >>> UtilProperties.getPropertyValue("catalog", "image.url.prefix");
> >> >>>
> >> >>>
> >> >>> // upload image
> >> >>> FlexibleStringExpander filenameExpander =
> new
> >> >>> FlexibleStringExpander(imageFilenameFormat);
> >> >>> String catImageUrl = "";
> >> >>> String linkImageUrl = "";
> >> >>> if(categoryImageUrl != null &&
> >> >>> categoryImageUrl.length() > 0){
> >> >>> Object forLock = new Object();
> >> >>> String contentType = null;
> >> >>> String categoryImageLocation =
> >> >>> filenameExpander.expandString(UtilMisc.toMap("location",
> >> "categories",
> >> >>> "type", "category", "id", productCategoryId));
> >> >>> String filePathPrefix = "";
> >> >>> String filenameToUse =
> >> >>> categoryImageLocation;
> >> >>> if
> >> (categoryImageLocation.lastIndexOf("/")
> >> >>> !=
> >> >>> -1) {
> >> >>> filePathPrefix =
> >> >>> categoryImageLocation.substring(0,
> >> >>> categoryImageLocation.lastIndexOf("/")
> >> >>> + 1); // adding 1 to include the trailing slash
> >> >>> filenameToUse =
> >> >>>
> >> categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/")
> >> +
> >> >>> 1);
> >> >>> }
> >> >>>
> >> >>> int i1;
> >> >>> if (contentType != null && (i1 =
> >> >>> contentType.indexOf("boundary=")) != -1) {
> >> >>> contentType =
> >> contentType.substring(i1
> >> +
> >> >>> 9);
> >> >>> contentType = "--" + contentType;
> >> >>> }
> >> >>>
> >> >>> String defaultFileName = filenameToUse +
> >> >>> "_temp";
> >> >>> HttpRequestFileUpload uploadObject = new
> >> >>> HttpRequestFileUpload();
> >> >>>
> >> >>> uploadObject.setOverrideFilename(defaultFileName);
> >> >>> uploadObject.setSavePath(imageServerPath
> >> +
> >> >>> "/"
> >> >>> + filePathPrefix);
> >> >>> try{
> >> >>> uploadObject.doUpload(request);
> >> >>>
> >> >>> }catch(IOException e){
> >> >>> Debug.logInfo("Image uploading failure",
> >> >>> module);
> >> >>> }
> >> >>> String categoryImageFileName =
> >> >>> uploadObject.getFilename();
> >> >>>
> >> >>> if (categoryImageFileName != null &&
> >> >>> categoryImageFileName.length() > 0) {
> >> >>> if
> >> >>> (categoryImageFileName.lastIndexOf(".")
> >> >>> > 0 && categoryImageFileName.lastIndexOf(".") <
> >> >>> categoryImageFileName.length()) {
> >> >>> filenameToUse +=
> >> >>>
> >> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
> >> >>> } else {
> >> >>> filenameToUse += ".jpg";
> >> >>> }
> >> >>> try{
> >> >>> String characterEncoding =
> >> >>> request.getCharacterEncoding();
> >> >>> catImageUrl = imageUrlPrefix + "/" +
> >> >>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
> >> >>> characterEncoding);
> >> >>> }catch(Exception e){
> >> >>> System.out.println("Incoding
> >> Problem");
> >> >>> }
> >> >>>
> >> >>> try {
> >> >>> File file = new
> >> File(imageServerPath
> >> >>> +
> >> >>> "/" + filePathPrefix, defaultFileName);
> >> >>> File file1 = new
> >> >>> File(imageServerPath
> >> >>> + "/" + filePathPrefix, filenameToUse);
> >> >>> try {
> >> >>> file1.delete();
> >> >>> } catch(Exception e) {
> >> >>> System.out.println("error
> >> >>> deleting
> >> >>> existing file (not neccessarily a problem)");
> >> >>> }
> >> >>> file.renameTo(file1);
> >> >>> } catch(Exception e) {
> >> >>> e.printStackTrace();
> >> >>> }
> >> >>> }
> >> >>> }
> >> >>>
> >> >>> // end of upload image
> >> >>>
> >> >>> Timestamp fromDate = UtilDateTime.nowTimestamp();
> >> >>> GenericDelegator delegator = (GenericDelegator)
> >> >>> request.getAttribute("delegator");
> >> >>> try {
> >> >>> GenericValue DataImportCategoryList =
> >> >>> delegator.findByPrimaryKey("DataImportCategory",
> >> >>> UtilMisc.toMap("productCategoryId", productCategoryId));
> >> >>>
> >> >>> if(DataImportCategoryList != null){
> >> >>> String categoryId =
> >> >>> DataImportCategoryList.getString("productCategoryId");
> >> >>> if(categoryId.equals(productCategoryId)){
> >> >>> failCount++;
> >> >>> }
> >> >>> }else {
> >> >>> GenericValue newImportCategory =
> >> >>> delegator.makeValue("DataImportCategory", null);
> >> >>>
> >> >>> newImportCategory.set("productCategoryId",
> >> >>> productCategoryId.trim());
> >> >>> if(productCategoryTypeId != null &&
> >> >>> productCategoryTypeId.length()
> >> >>> > 0){
> >> >>> newImportCategory.set("productCategoryTypeId",
> >> >>> productCategoryTypeId.trim());
> >> >>> } else {
> >> >>>
> >> >>> newImportCategory.set("productCategoryTypeId", "CATALOG_CATEGORY");
> >> >>> }
> >> >>> if(primaryParentCategoryId != null &&
> >> >>> primaryParentCategoryId.length() > 0)
> >> >>> newImportCategory.set("primaryParentCategoryId",
> >> >>> primaryParentCategoryId);
> >> >>> newImportCategory.set("categoryName", categoryName.trim());
> >> >>> newImportCategory.set("description", description);
> >> >>> newImportCategory.set("longDescription", longDescription);
> >> >>> newImportCategory.set("categoryImageUrl", catImageUrl);
> >> >>> newImportCategory.set("linkOneImageUrl", linkImageUrl);
> >> >>>
> newImportCategory.set("fromDate",
> >> >>> fromDate);
> >> >>> try {
> >> >>>
> >> >>> delegator.create(newImportCategory);
> >> >>>
> Debug.logInfo("Successfully
> >> >>> imported category ["+productCategoryId+" from row no "+ i+1 +"].",
> >> >>> module);
> >> >>> successCount++;
> >> >>> } catch
> (GenericEntityException
> >> e)
> >> >>> {
> >> >>>
> >> >>> Debug.logWarning(e.getMessage(),
> >> >>> module);
> >> >>> }
> >> >>>
> >> >>> }
> >> >>> } catch(GenericEntityException e) {
> >> >>> Debug.logError("Exception occured :"+e.getMessage(), module);
> >> >>> }
> >> >>> }
> >> >>>
> >> >>> }
> >> >>>
> >> >>>
> >> >>> it create the temp file in corresponding directory but there is no
> >> >>> actual
> >> >>> image at that path.
> >> >>>
> >> >>> So any one have any idea regarding this please suggest me.
> >> >>>
> >> >>>
> >> >>> Thanks.
> >> >>>
> >> >>> Nalin Chandra
> >> >>>
> >> >>
> >> >> --
> >> >> View this message in context:
> >> >>
> >>
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
> >> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
> >> >>
> >> >
> >> >
> >> >
> >>
> >> --
> >> View this message in context:
> >>
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p586989.html
> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
> >>
> >
> >
>
> --
> View this message in context:
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620283.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
Re: how to import data from excel sheet
Posted by su2 <sh...@pexsupply.com>.
Hi Pradeep,
Thanks for your response.
Sorry I am new to ofbiz. But I am not sure where to define the entity names
and the entity-field names where I want to insert the data(as all the
information like price and others for the product is not stored in only
'Product' entity, I need to insert the data in multiple entities)?
Do I have to specify in XML? Do you have any sample xml file? I want to
insert data for new products
Also, I see entity "DataImportProduct". What is that for?
Once again thank you for the help.
Shuchi-
S K Pradeep kumar wrote:
>
> Hi shuchi,
>
> Convert your .cvs to xml and use webtools entity import.
>
> With regards,
> S K Pradeep kumar,
>
>
>
> On Thu, Nov 12, 2009 at 12:52 AM, su2 <sh...@pexsupply.com> wrote:
>
>>
>> Hi Jacques,
>>
>> I want to insert all the excel data into the database. I looked at
>> viewdatafile screen, and looked like, using that I can not insert all my
>> excel data into database tables.
>>
>>
>> Jacques Le Roux wrote:
>> >
>> > I'd use CSV with https://localhost:8443/webtools/control/viewdatafile.
>> I
>> > let you find more on this ML archives...
>> >
>> > Jacques
>> >
>> > From: "su2" <sh...@pexsupply.com>
>> >>
>> >> Hi Nalin,
>> >>
>> >> We have similar requirement where we want to upload/import all the
>> >> product
>> >> information from excel sheet.
>> >>
>> >> I would really appreciate if you could tell me, how we can achieve
>> that.
>> >>
>> >> Thank you.
>> >> Shuchi-
>> >>
>> >> Nalin Chandra wrote:
>> >>>
>> >>> Hi All
>> >>>
>> >>> I want to import the product related data from excel sheet. I did it
>> but
>> >>> i
>> >>> have one problem in this.
>> >>> I excel sheet i have absolute path of image now i want to upload the
>> >>> image
>> >>> from that absolute path to our application as well store the relative
>> >>> path
>> >>> in data base as we did in product content section.
>> >>> i am able to store the relative path in database but image in not
>> >>> uploading in the required folder.
>> >>>
>> >>> Main problem is that here i am not using any html form.
>> >>>
>> >>> i paste the code below for it
>> >>>
>> >>> public static String uploadCategory(HttpServletRequest request,
>> >>> HttpServletResponse response) {
>> >>>
>> >>> try
>> >>> {
>> >>> String filename = "D:/category.xls";
>> >>> WorkbookSettings ws = new WorkbookSettings();
>> >>> ws.setLocale(new Locale("en", "EN"));
>> >>> Workbook workbook = Workbook.getWorkbook(new
>> >>> File(filename),ws);
>> >>> Sheet s = workbook.getSheet(0);
>> >>> readCategoryDataSheet(s, request);
>> >>> workbook.close();
>> >>> }
>> >>> catch (IOException e)
>> >>> {
>> >>> e.printStackTrace();
>> >>> }
>> >>> catch (BiffException e)
>> >>> {
>> >>> e.printStackTrace();
>> >>> }
>> >>>
>> >>> return "success";
>> >>> }
>> >>>
>> >>> private static void readCategoryDataSheet(Sheet s,
>> >>> HttpServletRequest
>> >>> request)
>> >>> {
>> >>> Cell rowData[] = null;
>> >>> int successCount = 0;
>> >>> int failCount = 0;
>> >>> int rows = s.getRows();
>> >>> int column = s.getColumns();
>> >>> String productCategoryId = "";
>> >>> String productCategoryTypeId = "";
>> >>> String primaryParentCategoryId = "";
>> >>> String categoryName = "";
>> >>> String description = "";
>> >>> String longDescription = "";
>> >>> String categoryImageUrl = "";
>> >>> String linkOneImageUrl = "";
>> >>> String prodCatalogId = "";
>> >>> String prodCatalogCategoryTypeId = "";
>> >>> String sequenceNum = "";
>> >>>
>> >>> for (int i = 1; i < rows; i++) {
>> >>> rowData = s.getRow(i);
>> >>> if (rowData[0].getContents().length() != 0) {
>> >>> for (int j = 0; j < column; j++) {
>> >>> switch (j) {
>> >>> case 0:
>> >>> productCategoryId =
>> >>> rowData[j].getContents();
>> >>> case 1:
>> >>> productCategoryTypeId
>> =
>> >>> rowData[j].getContents();
>> >>> case 2:
>> >>>
>> primaryParentCategoryId
>> >>> =
>> >>> rowData[j].getContents();
>> >>> case 3:
>> >>> categoryName =
>> >>> rowData[j].getContents();
>> >>> case 4:
>> >>> description =
>> >>> rowData[j].getContents();
>> >>> case 5:
>> >>> longDescription =
>> >>> rowData[j].getContents();
>> >>> case 6:
>> >>> categoryImageUrl =
>> >>> rowData[j].getContents();
>> >>> case 7:
>> >>> linkOneImageUrl =
>> >>> rowData[j].getContents();
>> >>> default:
>> >>> break;
>> >>> }
>> >>> }
>> >>> }
>> >>> String imageFilenameFormat =
>> >>> UtilProperties.getPropertyValue("catalog", "image.filename.format");
>> >>> String imageServerPath =
>> >>> UtilProperties.getPropertyValue("catalog", "image.server.path");
>> >>> String imageUrlPrefix =
>> >>> UtilProperties.getPropertyValue("catalog", "image.url.prefix");
>> >>>
>> >>>
>> >>> // upload image
>> >>> FlexibleStringExpander filenameExpander = new
>> >>> FlexibleStringExpander(imageFilenameFormat);
>> >>> String catImageUrl = "";
>> >>> String linkImageUrl = "";
>> >>> if(categoryImageUrl != null &&
>> >>> categoryImageUrl.length() > 0){
>> >>> Object forLock = new Object();
>> >>> String contentType = null;
>> >>> String categoryImageLocation =
>> >>> filenameExpander.expandString(UtilMisc.toMap("location",
>> "categories",
>> >>> "type", "category", "id", productCategoryId));
>> >>> String filePathPrefix = "";
>> >>> String filenameToUse =
>> >>> categoryImageLocation;
>> >>> if
>> (categoryImageLocation.lastIndexOf("/")
>> >>> !=
>> >>> -1) {
>> >>> filePathPrefix =
>> >>> categoryImageLocation.substring(0,
>> >>> categoryImageLocation.lastIndexOf("/")
>> >>> + 1); // adding 1 to include the trailing slash
>> >>> filenameToUse =
>> >>>
>> categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/")
>> +
>> >>> 1);
>> >>> }
>> >>>
>> >>> int i1;
>> >>> if (contentType != null && (i1 =
>> >>> contentType.indexOf("boundary=")) != -1) {
>> >>> contentType =
>> contentType.substring(i1
>> +
>> >>> 9);
>> >>> contentType = "--" + contentType;
>> >>> }
>> >>>
>> >>> String defaultFileName = filenameToUse +
>> >>> "_temp";
>> >>> HttpRequestFileUpload uploadObject = new
>> >>> HttpRequestFileUpload();
>> >>>
>> >>> uploadObject.setOverrideFilename(defaultFileName);
>> >>> uploadObject.setSavePath(imageServerPath
>> +
>> >>> "/"
>> >>> + filePathPrefix);
>> >>> try{
>> >>> uploadObject.doUpload(request);
>> >>>
>> >>> }catch(IOException e){
>> >>> Debug.logInfo("Image uploading failure",
>> >>> module);
>> >>> }
>> >>> String categoryImageFileName =
>> >>> uploadObject.getFilename();
>> >>>
>> >>> if (categoryImageFileName != null &&
>> >>> categoryImageFileName.length() > 0) {
>> >>> if
>> >>> (categoryImageFileName.lastIndexOf(".")
>> >>> > 0 && categoryImageFileName.lastIndexOf(".") <
>> >>> categoryImageFileName.length()) {
>> >>> filenameToUse +=
>> >>>
>> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
>> >>> } else {
>> >>> filenameToUse += ".jpg";
>> >>> }
>> >>> try{
>> >>> String characterEncoding =
>> >>> request.getCharacterEncoding();
>> >>> catImageUrl = imageUrlPrefix + "/" +
>> >>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
>> >>> characterEncoding);
>> >>> }catch(Exception e){
>> >>> System.out.println("Incoding
>> Problem");
>> >>> }
>> >>>
>> >>> try {
>> >>> File file = new
>> File(imageServerPath
>> >>> +
>> >>> "/" + filePathPrefix, defaultFileName);
>> >>> File file1 = new
>> >>> File(imageServerPath
>> >>> + "/" + filePathPrefix, filenameToUse);
>> >>> try {
>> >>> file1.delete();
>> >>> } catch(Exception e) {
>> >>> System.out.println("error
>> >>> deleting
>> >>> existing file (not neccessarily a problem)");
>> >>> }
>> >>> file.renameTo(file1);
>> >>> } catch(Exception e) {
>> >>> e.printStackTrace();
>> >>> }
>> >>> }
>> >>> }
>> >>>
>> >>> // end of upload image
>> >>>
>> >>> Timestamp fromDate = UtilDateTime.nowTimestamp();
>> >>> GenericDelegator delegator = (GenericDelegator)
>> >>> request.getAttribute("delegator");
>> >>> try {
>> >>> GenericValue DataImportCategoryList =
>> >>> delegator.findByPrimaryKey("DataImportCategory",
>> >>> UtilMisc.toMap("productCategoryId", productCategoryId));
>> >>>
>> >>> if(DataImportCategoryList != null){
>> >>> String categoryId =
>> >>> DataImportCategoryList.getString("productCategoryId");
>> >>> if(categoryId.equals(productCategoryId)){
>> >>> failCount++;
>> >>> }
>> >>> }else {
>> >>> GenericValue newImportCategory =
>> >>> delegator.makeValue("DataImportCategory", null);
>> >>>
>> >>> newImportCategory.set("productCategoryId",
>> >>> productCategoryId.trim());
>> >>> if(productCategoryTypeId != null &&
>> >>> productCategoryTypeId.length()
>> >>> > 0){
>> >>> newImportCategory.set("productCategoryTypeId",
>> >>> productCategoryTypeId.trim());
>> >>> } else {
>> >>>
>> >>> newImportCategory.set("productCategoryTypeId", "CATALOG_CATEGORY");
>> >>> }
>> >>> if(primaryParentCategoryId != null &&
>> >>> primaryParentCategoryId.length() > 0)
>> >>> newImportCategory.set("primaryParentCategoryId",
>> >>> primaryParentCategoryId);
>> >>> newImportCategory.set("categoryName", categoryName.trim());
>> >>> newImportCategory.set("description", description);
>> >>> newImportCategory.set("longDescription", longDescription);
>> >>> newImportCategory.set("categoryImageUrl", catImageUrl);
>> >>> newImportCategory.set("linkOneImageUrl", linkImageUrl);
>> >>> newImportCategory.set("fromDate",
>> >>> fromDate);
>> >>> try {
>> >>>
>> >>> delegator.create(newImportCategory);
>> >>> Debug.logInfo("Successfully
>> >>> imported category ["+productCategoryId+" from row no "+ i+1 +"].",
>> >>> module);
>> >>> successCount++;
>> >>> } catch (GenericEntityException
>> e)
>> >>> {
>> >>>
>> >>> Debug.logWarning(e.getMessage(),
>> >>> module);
>> >>> }
>> >>>
>> >>> }
>> >>> } catch(GenericEntityException e) {
>> >>> Debug.logError("Exception occured :"+e.getMessage(), module);
>> >>> }
>> >>> }
>> >>>
>> >>> }
>> >>>
>> >>>
>> >>> it create the temp file in corresponding directory but there is no
>> >>> actual
>> >>> image at that path.
>> >>>
>> >>> So any one have any idea regarding this please suggest me.
>> >>>
>> >>>
>> >>> Thanks.
>> >>>
>> >>> Nalin Chandra
>> >>>
>> >>
>> >> --
>> >> View this message in context:
>> >>
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
>> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
>> >>
>> >
>> >
>> >
>>
>> --
>> View this message in context:
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p586989.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>
>
>
--
View this message in context: http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620283.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: how to import data from excel sheet
Posted by Jacques Le Roux <ja...@les7arts.com>.
And for that use viewdatafile.... There are messages in user ML explaining how to do it...
Jacques
From: "S K Pradeep kumar" <s....@gmail.com>
> Hi shuchi,
>
> Convert your .cvs to xml and use webtools entity import.
>
> With regards,
> S K Pradeep kumar,
>
>
>
> On Thu, Nov 12, 2009 at 12:52 AM, su2 <sh...@pexsupply.com> wrote:
>
>>
>> Hi Jacques,
>>
>> I want to insert all the excel data into the database. I looked at
>> viewdatafile screen, and looked like, using that I can not insert all my
>> excel data into database tables.
>>
>>
>> Jacques Le Roux wrote:
>> >
>> > I'd use CSV with https://localhost:8443/webtools/control/viewdatafile. I
>> > let you find more on this ML archives...
>> >
>> > Jacques
>> >
>> > From: "su2" <sh...@pexsupply.com>
>> >>
>> >> Hi Nalin,
>> >>
>> >> We have similar requirement where we want to upload/import all the
>> >> product
>> >> information from excel sheet.
>> >>
>> >> I would really appreciate if you could tell me, how we can achieve that.
>> >>
>> >> Thank you.
>> >> Shuchi-
>> >>
>> >> Nalin Chandra wrote:
>> >>>
>> >>> Hi All
>> >>>
>> >>> I want to import the product related data from excel sheet. I did it
>> but
>> >>> i
>> >>> have one problem in this.
>> >>> I excel sheet i have absolute path of image now i want to upload the
>> >>> image
>> >>> from that absolute path to our application as well store the relative
>> >>> path
>> >>> in data base as we did in product content section.
>> >>> i am able to store the relative path in database but image in not
>> >>> uploading in the required folder.
>> >>>
>> >>> Main problem is that here i am not using any html form.
>> >>>
>> >>> i paste the code below for it
>> >>>
>> >>> public static String uploadCategory(HttpServletRequest request,
>> >>> HttpServletResponse response) {
>> >>>
>> >>> try
>> >>> {
>> >>> String filename = "D:/category.xls";
>> >>> WorkbookSettings ws = new WorkbookSettings();
>> >>> ws.setLocale(new Locale("en", "EN"));
>> >>> Workbook workbook = Workbook.getWorkbook(new
>> >>> File(filename),ws);
>> >>> Sheet s = workbook.getSheet(0);
>> >>> readCategoryDataSheet(s, request);
>> >>> workbook.close();
>> >>> }
>> >>> catch (IOException e)
>> >>> {
>> >>> e.printStackTrace();
>> >>> }
>> >>> catch (BiffException e)
>> >>> {
>> >>> e.printStackTrace();
>> >>> }
>> >>>
>> >>> return "success";
>> >>> }
>> >>>
>> >>> private static void readCategoryDataSheet(Sheet s,
>> >>> HttpServletRequest
>> >>> request)
>> >>> {
>> >>> Cell rowData[] = null;
>> >>> int successCount = 0;
>> >>> int failCount = 0;
>> >>> int rows = s.getRows();
>> >>> int column = s.getColumns();
>> >>> String productCategoryId = "";
>> >>> String productCategoryTypeId = "";
>> >>> String primaryParentCategoryId = "";
>> >>> String categoryName = "";
>> >>> String description = "";
>> >>> String longDescription = "";
>> >>> String categoryImageUrl = "";
>> >>> String linkOneImageUrl = "";
>> >>> String prodCatalogId = "";
>> >>> String prodCatalogCategoryTypeId = "";
>> >>> String sequenceNum = "";
>> >>>
>> >>> for (int i = 1; i < rows; i++) {
>> >>> rowData = s.getRow(i);
>> >>> if (rowData[0].getContents().length() != 0) {
>> >>> for (int j = 0; j < column; j++) {
>> >>> switch (j) {
>> >>> case 0:
>> >>> productCategoryId =
>> >>> rowData[j].getContents();
>> >>> case 1:
>> >>> productCategoryTypeId =
>> >>> rowData[j].getContents();
>> >>> case 2:
>> >>> primaryParentCategoryId
>> >>> =
>> >>> rowData[j].getContents();
>> >>> case 3:
>> >>> categoryName =
>> >>> rowData[j].getContents();
>> >>> case 4:
>> >>> description =
>> >>> rowData[j].getContents();
>> >>> case 5:
>> >>> longDescription =
>> >>> rowData[j].getContents();
>> >>> case 6:
>> >>> categoryImageUrl =
>> >>> rowData[j].getContents();
>> >>> case 7:
>> >>> linkOneImageUrl =
>> >>> rowData[j].getContents();
>> >>> default:
>> >>> break;
>> >>> }
>> >>> }
>> >>> }
>> >>> String imageFilenameFormat =
>> >>> UtilProperties.getPropertyValue("catalog", "image.filename.format");
>> >>> String imageServerPath =
>> >>> UtilProperties.getPropertyValue("catalog", "image.server.path");
>> >>> String imageUrlPrefix =
>> >>> UtilProperties.getPropertyValue("catalog", "image.url.prefix");
>> >>>
>> >>>
>> >>> // upload image
>> >>> FlexibleStringExpander filenameExpander = new
>> >>> FlexibleStringExpander(imageFilenameFormat);
>> >>> String catImageUrl = "";
>> >>> String linkImageUrl = "";
>> >>> if(categoryImageUrl != null &&
>> >>> categoryImageUrl.length() > 0){
>> >>> Object forLock = new Object();
>> >>> String contentType = null;
>> >>> String categoryImageLocation =
>> >>> filenameExpander.expandString(UtilMisc.toMap("location", "categories",
>> >>> "type", "category", "id", productCategoryId));
>> >>> String filePathPrefix = "";
>> >>> String filenameToUse =
>> >>> categoryImageLocation;
>> >>> if (categoryImageLocation.lastIndexOf("/")
>> >>> !=
>> >>> -1) {
>> >>> filePathPrefix =
>> >>> categoryImageLocation.substring(0,
>> >>> categoryImageLocation.lastIndexOf("/")
>> >>> + 1); // adding 1 to include the trailing slash
>> >>> filenameToUse =
>> >>> categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/")
>> +
>> >>> 1);
>> >>> }
>> >>>
>> >>> int i1;
>> >>> if (contentType != null && (i1 =
>> >>> contentType.indexOf("boundary=")) != -1) {
>> >>> contentType = contentType.substring(i1
>> +
>> >>> 9);
>> >>> contentType = "--" + contentType;
>> >>> }
>> >>>
>> >>> String defaultFileName = filenameToUse +
>> >>> "_temp";
>> >>> HttpRequestFileUpload uploadObject = new
>> >>> HttpRequestFileUpload();
>> >>>
>> >>> uploadObject.setOverrideFilename(defaultFileName);
>> >>> uploadObject.setSavePath(imageServerPath +
>> >>> "/"
>> >>> + filePathPrefix);
>> >>> try{
>> >>> uploadObject.doUpload(request);
>> >>>
>> >>> }catch(IOException e){
>> >>> Debug.logInfo("Image uploading failure",
>> >>> module);
>> >>> }
>> >>> String categoryImageFileName =
>> >>> uploadObject.getFilename();
>> >>>
>> >>> if (categoryImageFileName != null &&
>> >>> categoryImageFileName.length() > 0) {
>> >>> if
>> >>> (categoryImageFileName.lastIndexOf(".")
>> >>> > 0 && categoryImageFileName.lastIndexOf(".") <
>> >>> categoryImageFileName.length()) {
>> >>> filenameToUse +=
>> >>>
>> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
>> >>> } else {
>> >>> filenameToUse += ".jpg";
>> >>> }
>> >>> try{
>> >>> String characterEncoding =
>> >>> request.getCharacterEncoding();
>> >>> catImageUrl = imageUrlPrefix + "/" +
>> >>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
>> >>> characterEncoding);
>> >>> }catch(Exception e){
>> >>> System.out.println("Incoding Problem");
>> >>> }
>> >>>
>> >>> try {
>> >>> File file = new
>> File(imageServerPath
>> >>> +
>> >>> "/" + filePathPrefix, defaultFileName);
>> >>> File file1 = new
>> >>> File(imageServerPath
>> >>> + "/" + filePathPrefix, filenameToUse);
>> >>> try {
>> >>> file1.delete();
>> >>> } catch(Exception e) {
>> >>> System.out.println("error
>> >>> deleting
>> >>> existing file (not neccessarily a problem)");
>> >>> }
>> >>> file.renameTo(file1);
>> >>> } catch(Exception e) {
>> >>> e.printStackTrace();
>> >>> }
>> >>> }
>> >>> }
>> >>>
>> >>> // end of upload image
>> >>>
>> >>> Timestamp fromDate = UtilDateTime.nowTimestamp();
>> >>> GenericDelegator delegator = (GenericDelegator)
>> >>> request.getAttribute("delegator");
>> >>> try {
>> >>> GenericValue DataImportCategoryList =
>> >>> delegator.findByPrimaryKey("DataImportCategory",
>> >>> UtilMisc.toMap("productCategoryId", productCategoryId));
>> >>>
>> >>> if(DataImportCategoryList != null){
>> >>> String categoryId =
>> >>> DataImportCategoryList.getString("productCategoryId");
>> >>> if(categoryId.equals(productCategoryId)){
>> >>> failCount++;
>> >>> }
>> >>> }else {
>> >>> GenericValue newImportCategory =
>> >>> delegator.makeValue("DataImportCategory", null);
>> >>>
>> >>> newImportCategory.set("productCategoryId",
>> >>> productCategoryId.trim());
>> >>> if(productCategoryTypeId != null &&
>> >>> productCategoryTypeId.length()
>> >>> > 0){
>> >>> newImportCategory.set("productCategoryTypeId",
>> >>> productCategoryTypeId.trim());
>> >>> } else {
>> >>>
>> >>> newImportCategory.set("productCategoryTypeId", "CATALOG_CATEGORY");
>> >>> }
>> >>> if(primaryParentCategoryId != null &&
>> >>> primaryParentCategoryId.length() > 0)
>> >>> newImportCategory.set("primaryParentCategoryId",
>> >>> primaryParentCategoryId);
>> >>> newImportCategory.set("categoryName", categoryName.trim());
>> >>> newImportCategory.set("description", description);
>> >>> newImportCategory.set("longDescription", longDescription);
>> >>> newImportCategory.set("categoryImageUrl", catImageUrl);
>> >>> newImportCategory.set("linkOneImageUrl", linkImageUrl);
>> >>> newImportCategory.set("fromDate",
>> >>> fromDate);
>> >>> try {
>> >>>
>> >>> delegator.create(newImportCategory);
>> >>> Debug.logInfo("Successfully
>> >>> imported category ["+productCategoryId+" from row no "+ i+1 +"].",
>> >>> module);
>> >>> successCount++;
>> >>> } catch (GenericEntityException
>> e)
>> >>> {
>> >>>
>> >>> Debug.logWarning(e.getMessage(),
>> >>> module);
>> >>> }
>> >>>
>> >>> }
>> >>> } catch(GenericEntityException e) {
>> >>> Debug.logError("Exception occured :"+e.getMessage(), module);
>> >>> }
>> >>> }
>> >>>
>> >>> }
>> >>>
>> >>>
>> >>> it create the temp file in corresponding directory but there is no
>> >>> actual
>> >>> image at that path.
>> >>>
>> >>> So any one have any idea regarding this please suggest me.
>> >>>
>> >>>
>> >>> Thanks.
>> >>>
>> >>> Nalin Chandra
>> >>>
>> >>
>> >> --
>> >> View this message in context:
>> >>
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
>> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
>> >>
>> >
>> >
>> >
>>
>> --
>> View this message in context:
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p586989.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>
>
Re: how to import data from excel sheet
Posted by S K Pradeep kumar <s....@gmail.com>.
Hi shuchi,
Convert your .cvs to xml and use webtools entity import.
With regards,
S K Pradeep kumar,
On Thu, Nov 12, 2009 at 12:52 AM, su2 <sh...@pexsupply.com> wrote:
>
> Hi Jacques,
>
> I want to insert all the excel data into the database. I looked at
> viewdatafile screen, and looked like, using that I can not insert all my
> excel data into database tables.
>
>
> Jacques Le Roux wrote:
> >
> > I'd use CSV with https://localhost:8443/webtools/control/viewdatafile. I
> > let you find more on this ML archives...
> >
> > Jacques
> >
> > From: "su2" <sh...@pexsupply.com>
> >>
> >> Hi Nalin,
> >>
> >> We have similar requirement where we want to upload/import all the
> >> product
> >> information from excel sheet.
> >>
> >> I would really appreciate if you could tell me, how we can achieve that.
> >>
> >> Thank you.
> >> Shuchi-
> >>
> >> Nalin Chandra wrote:
> >>>
> >>> Hi All
> >>>
> >>> I want to import the product related data from excel sheet. I did it
> but
> >>> i
> >>> have one problem in this.
> >>> I excel sheet i have absolute path of image now i want to upload the
> >>> image
> >>> from that absolute path to our application as well store the relative
> >>> path
> >>> in data base as we did in product content section.
> >>> i am able to store the relative path in database but image in not
> >>> uploading in the required folder.
> >>>
> >>> Main problem is that here i am not using any html form.
> >>>
> >>> i paste the code below for it
> >>>
> >>> public static String uploadCategory(HttpServletRequest request,
> >>> HttpServletResponse response) {
> >>>
> >>> try
> >>> {
> >>> String filename = "D:/category.xls";
> >>> WorkbookSettings ws = new WorkbookSettings();
> >>> ws.setLocale(new Locale("en", "EN"));
> >>> Workbook workbook = Workbook.getWorkbook(new
> >>> File(filename),ws);
> >>> Sheet s = workbook.getSheet(0);
> >>> readCategoryDataSheet(s, request);
> >>> workbook.close();
> >>> }
> >>> catch (IOException e)
> >>> {
> >>> e.printStackTrace();
> >>> }
> >>> catch (BiffException e)
> >>> {
> >>> e.printStackTrace();
> >>> }
> >>>
> >>> return "success";
> >>> }
> >>>
> >>> private static void readCategoryDataSheet(Sheet s,
> >>> HttpServletRequest
> >>> request)
> >>> {
> >>> Cell rowData[] = null;
> >>> int successCount = 0;
> >>> int failCount = 0;
> >>> int rows = s.getRows();
> >>> int column = s.getColumns();
> >>> String productCategoryId = "";
> >>> String productCategoryTypeId = "";
> >>> String primaryParentCategoryId = "";
> >>> String categoryName = "";
> >>> String description = "";
> >>> String longDescription = "";
> >>> String categoryImageUrl = "";
> >>> String linkOneImageUrl = "";
> >>> String prodCatalogId = "";
> >>> String prodCatalogCategoryTypeId = "";
> >>> String sequenceNum = "";
> >>>
> >>> for (int i = 1; i < rows; i++) {
> >>> rowData = s.getRow(i);
> >>> if (rowData[0].getContents().length() != 0) {
> >>> for (int j = 0; j < column; j++) {
> >>> switch (j) {
> >>> case 0:
> >>> productCategoryId =
> >>> rowData[j].getContents();
> >>> case 1:
> >>> productCategoryTypeId =
> >>> rowData[j].getContents();
> >>> case 2:
> >>> primaryParentCategoryId
> >>> =
> >>> rowData[j].getContents();
> >>> case 3:
> >>> categoryName =
> >>> rowData[j].getContents();
> >>> case 4:
> >>> description =
> >>> rowData[j].getContents();
> >>> case 5:
> >>> longDescription =
> >>> rowData[j].getContents();
> >>> case 6:
> >>> categoryImageUrl =
> >>> rowData[j].getContents();
> >>> case 7:
> >>> linkOneImageUrl =
> >>> rowData[j].getContents();
> >>> default:
> >>> break;
> >>> }
> >>> }
> >>> }
> >>> String imageFilenameFormat =
> >>> UtilProperties.getPropertyValue("catalog", "image.filename.format");
> >>> String imageServerPath =
> >>> UtilProperties.getPropertyValue("catalog", "image.server.path");
> >>> String imageUrlPrefix =
> >>> UtilProperties.getPropertyValue("catalog", "image.url.prefix");
> >>>
> >>>
> >>> // upload image
> >>> FlexibleStringExpander filenameExpander = new
> >>> FlexibleStringExpander(imageFilenameFormat);
> >>> String catImageUrl = "";
> >>> String linkImageUrl = "";
> >>> if(categoryImageUrl != null &&
> >>> categoryImageUrl.length() > 0){
> >>> Object forLock = new Object();
> >>> String contentType = null;
> >>> String categoryImageLocation =
> >>> filenameExpander.expandString(UtilMisc.toMap("location", "categories",
> >>> "type", "category", "id", productCategoryId));
> >>> String filePathPrefix = "";
> >>> String filenameToUse =
> >>> categoryImageLocation;
> >>> if (categoryImageLocation.lastIndexOf("/")
> >>> !=
> >>> -1) {
> >>> filePathPrefix =
> >>> categoryImageLocation.substring(0,
> >>> categoryImageLocation.lastIndexOf("/")
> >>> + 1); // adding 1 to include the trailing slash
> >>> filenameToUse =
> >>> categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/")
> +
> >>> 1);
> >>> }
> >>>
> >>> int i1;
> >>> if (contentType != null && (i1 =
> >>> contentType.indexOf("boundary=")) != -1) {
> >>> contentType = contentType.substring(i1
> +
> >>> 9);
> >>> contentType = "--" + contentType;
> >>> }
> >>>
> >>> String defaultFileName = filenameToUse +
> >>> "_temp";
> >>> HttpRequestFileUpload uploadObject = new
> >>> HttpRequestFileUpload();
> >>>
> >>> uploadObject.setOverrideFilename(defaultFileName);
> >>> uploadObject.setSavePath(imageServerPath +
> >>> "/"
> >>> + filePathPrefix);
> >>> try{
> >>> uploadObject.doUpload(request);
> >>>
> >>> }catch(IOException e){
> >>> Debug.logInfo("Image uploading failure",
> >>> module);
> >>> }
> >>> String categoryImageFileName =
> >>> uploadObject.getFilename();
> >>>
> >>> if (categoryImageFileName != null &&
> >>> categoryImageFileName.length() > 0) {
> >>> if
> >>> (categoryImageFileName.lastIndexOf(".")
> >>> > 0 && categoryImageFileName.lastIndexOf(".") <
> >>> categoryImageFileName.length()) {
> >>> filenameToUse +=
> >>>
> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
> >>> } else {
> >>> filenameToUse += ".jpg";
> >>> }
> >>> try{
> >>> String characterEncoding =
> >>> request.getCharacterEncoding();
> >>> catImageUrl = imageUrlPrefix + "/" +
> >>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
> >>> characterEncoding);
> >>> }catch(Exception e){
> >>> System.out.println("Incoding Problem");
> >>> }
> >>>
> >>> try {
> >>> File file = new
> File(imageServerPath
> >>> +
> >>> "/" + filePathPrefix, defaultFileName);
> >>> File file1 = new
> >>> File(imageServerPath
> >>> + "/" + filePathPrefix, filenameToUse);
> >>> try {
> >>> file1.delete();
> >>> } catch(Exception e) {
> >>> System.out.println("error
> >>> deleting
> >>> existing file (not neccessarily a problem)");
> >>> }
> >>> file.renameTo(file1);
> >>> } catch(Exception e) {
> >>> e.printStackTrace();
> >>> }
> >>> }
> >>> }
> >>>
> >>> // end of upload image
> >>>
> >>> Timestamp fromDate = UtilDateTime.nowTimestamp();
> >>> GenericDelegator delegator = (GenericDelegator)
> >>> request.getAttribute("delegator");
> >>> try {
> >>> GenericValue DataImportCategoryList =
> >>> delegator.findByPrimaryKey("DataImportCategory",
> >>> UtilMisc.toMap("productCategoryId", productCategoryId));
> >>>
> >>> if(DataImportCategoryList != null){
> >>> String categoryId =
> >>> DataImportCategoryList.getString("productCategoryId");
> >>> if(categoryId.equals(productCategoryId)){
> >>> failCount++;
> >>> }
> >>> }else {
> >>> GenericValue newImportCategory =
> >>> delegator.makeValue("DataImportCategory", null);
> >>>
> >>> newImportCategory.set("productCategoryId",
> >>> productCategoryId.trim());
> >>> if(productCategoryTypeId != null &&
> >>> productCategoryTypeId.length()
> >>> > 0){
> >>> newImportCategory.set("productCategoryTypeId",
> >>> productCategoryTypeId.trim());
> >>> } else {
> >>>
> >>> newImportCategory.set("productCategoryTypeId", "CATALOG_CATEGORY");
> >>> }
> >>> if(primaryParentCategoryId != null &&
> >>> primaryParentCategoryId.length() > 0)
> >>> newImportCategory.set("primaryParentCategoryId",
> >>> primaryParentCategoryId);
> >>> newImportCategory.set("categoryName", categoryName.trim());
> >>> newImportCategory.set("description", description);
> >>> newImportCategory.set("longDescription", longDescription);
> >>> newImportCategory.set("categoryImageUrl", catImageUrl);
> >>> newImportCategory.set("linkOneImageUrl", linkImageUrl);
> >>> newImportCategory.set("fromDate",
> >>> fromDate);
> >>> try {
> >>>
> >>> delegator.create(newImportCategory);
> >>> Debug.logInfo("Successfully
> >>> imported category ["+productCategoryId+" from row no "+ i+1 +"].",
> >>> module);
> >>> successCount++;
> >>> } catch (GenericEntityException
> e)
> >>> {
> >>>
> >>> Debug.logWarning(e.getMessage(),
> >>> module);
> >>> }
> >>>
> >>> }
> >>> } catch(GenericEntityException e) {
> >>> Debug.logError("Exception occured :"+e.getMessage(), module);
> >>> }
> >>> }
> >>>
> >>> }
> >>>
> >>>
> >>> it create the temp file in corresponding directory but there is no
> >>> actual
> >>> image at that path.
> >>>
> >>> So any one have any idea regarding this please suggest me.
> >>>
> >>>
> >>> Thanks.
> >>>
> >>> Nalin Chandra
> >>>
> >>
> >> --
> >> View this message in context:
> >>
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
> >>
> >
> >
> >
>
> --
> View this message in context:
> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p586989.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
Re: how to import data from excel sheet
Posted by su2 <sh...@pexsupply.com>.
Hi Jacques,
I want to insert all the excel data into the database. I looked at
viewdatafile screen, and looked like, using that I can not insert all my
excel data into database tables.
Jacques Le Roux wrote:
>
> I'd use CSV with https://localhost:8443/webtools/control/viewdatafile. I
> let you find more on this ML archives...
>
> Jacques
>
> From: "su2" <sh...@pexsupply.com>
>>
>> Hi Nalin,
>>
>> We have similar requirement where we want to upload/import all the
>> product
>> information from excel sheet.
>>
>> I would really appreciate if you could tell me, how we can achieve that.
>>
>> Thank you.
>> Shuchi-
>>
>> Nalin Chandra wrote:
>>>
>>> Hi All
>>>
>>> I want to import the product related data from excel sheet. I did it but
>>> i
>>> have one problem in this.
>>> I excel sheet i have absolute path of image now i want to upload the
>>> image
>>> from that absolute path to our application as well store the relative
>>> path
>>> in data base as we did in product content section.
>>> i am able to store the relative path in database but image in not
>>> uploading in the required folder.
>>>
>>> Main problem is that here i am not using any html form.
>>>
>>> i paste the code below for it
>>>
>>> public static String uploadCategory(HttpServletRequest request,
>>> HttpServletResponse response) {
>>>
>>> try
>>> {
>>> String filename = "D:/category.xls";
>>> WorkbookSettings ws = new WorkbookSettings();
>>> ws.setLocale(new Locale("en", "EN"));
>>> Workbook workbook = Workbook.getWorkbook(new
>>> File(filename),ws);
>>> Sheet s = workbook.getSheet(0);
>>> readCategoryDataSheet(s, request);
>>> workbook.close();
>>> }
>>> catch (IOException e)
>>> {
>>> e.printStackTrace();
>>> }
>>> catch (BiffException e)
>>> {
>>> e.printStackTrace();
>>> }
>>>
>>> return "success";
>>> }
>>>
>>> private static void readCategoryDataSheet(Sheet s,
>>> HttpServletRequest
>>> request)
>>> {
>>> Cell rowData[] = null;
>>> int successCount = 0;
>>> int failCount = 0;
>>> int rows = s.getRows();
>>> int column = s.getColumns();
>>> String productCategoryId = "";
>>> String productCategoryTypeId = "";
>>> String primaryParentCategoryId = "";
>>> String categoryName = "";
>>> String description = "";
>>> String longDescription = "";
>>> String categoryImageUrl = "";
>>> String linkOneImageUrl = "";
>>> String prodCatalogId = "";
>>> String prodCatalogCategoryTypeId = "";
>>> String sequenceNum = "";
>>>
>>> for (int i = 1; i < rows; i++) {
>>> rowData = s.getRow(i);
>>> if (rowData[0].getContents().length() != 0) {
>>> for (int j = 0; j < column; j++) {
>>> switch (j) {
>>> case 0:
>>> productCategoryId =
>>> rowData[j].getContents();
>>> case 1:
>>> productCategoryTypeId =
>>> rowData[j].getContents();
>>> case 2:
>>> primaryParentCategoryId
>>> =
>>> rowData[j].getContents();
>>> case 3:
>>> categoryName =
>>> rowData[j].getContents();
>>> case 4:
>>> description =
>>> rowData[j].getContents();
>>> case 5:
>>> longDescription =
>>> rowData[j].getContents();
>>> case 6:
>>> categoryImageUrl =
>>> rowData[j].getContents();
>>> case 7:
>>> linkOneImageUrl =
>>> rowData[j].getContents();
>>> default:
>>> break;
>>> }
>>> }
>>> }
>>> String imageFilenameFormat =
>>> UtilProperties.getPropertyValue("catalog", "image.filename.format");
>>> String imageServerPath =
>>> UtilProperties.getPropertyValue("catalog", "image.server.path");
>>> String imageUrlPrefix =
>>> UtilProperties.getPropertyValue("catalog", "image.url.prefix");
>>>
>>>
>>> // upload image
>>> FlexibleStringExpander filenameExpander = new
>>> FlexibleStringExpander(imageFilenameFormat);
>>> String catImageUrl = "";
>>> String linkImageUrl = "";
>>> if(categoryImageUrl != null &&
>>> categoryImageUrl.length() > 0){
>>> Object forLock = new Object();
>>> String contentType = null;
>>> String categoryImageLocation =
>>> filenameExpander.expandString(UtilMisc.toMap("location", "categories",
>>> "type", "category", "id", productCategoryId));
>>> String filePathPrefix = "";
>>> String filenameToUse =
>>> categoryImageLocation;
>>> if (categoryImageLocation.lastIndexOf("/")
>>> !=
>>> -1) {
>>> filePathPrefix =
>>> categoryImageLocation.substring(0,
>>> categoryImageLocation.lastIndexOf("/")
>>> + 1); // adding 1 to include the trailing slash
>>> filenameToUse =
>>> categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/") +
>>> 1);
>>> }
>>>
>>> int i1;
>>> if (contentType != null && (i1 =
>>> contentType.indexOf("boundary=")) != -1) {
>>> contentType = contentType.substring(i1 +
>>> 9);
>>> contentType = "--" + contentType;
>>> }
>>>
>>> String defaultFileName = filenameToUse +
>>> "_temp";
>>> HttpRequestFileUpload uploadObject = new
>>> HttpRequestFileUpload();
>>>
>>> uploadObject.setOverrideFilename(defaultFileName);
>>> uploadObject.setSavePath(imageServerPath +
>>> "/"
>>> + filePathPrefix);
>>> try{
>>> uploadObject.doUpload(request);
>>>
>>> }catch(IOException e){
>>> Debug.logInfo("Image uploading failure",
>>> module);
>>> }
>>> String categoryImageFileName =
>>> uploadObject.getFilename();
>>>
>>> if (categoryImageFileName != null &&
>>> categoryImageFileName.length() > 0) {
>>> if
>>> (categoryImageFileName.lastIndexOf(".")
>>> > 0 && categoryImageFileName.lastIndexOf(".") <
>>> categoryImageFileName.length()) {
>>> filenameToUse +=
>>> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
>>> } else {
>>> filenameToUse += ".jpg";
>>> }
>>> try{
>>> String characterEncoding =
>>> request.getCharacterEncoding();
>>> catImageUrl = imageUrlPrefix + "/" +
>>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
>>> characterEncoding);
>>> }catch(Exception e){
>>> System.out.println("Incoding Problem");
>>> }
>>>
>>> try {
>>> File file = new File(imageServerPath
>>> +
>>> "/" + filePathPrefix, defaultFileName);
>>> File file1 = new
>>> File(imageServerPath
>>> + "/" + filePathPrefix, filenameToUse);
>>> try {
>>> file1.delete();
>>> } catch(Exception e) {
>>> System.out.println("error
>>> deleting
>>> existing file (not neccessarily a problem)");
>>> }
>>> file.renameTo(file1);
>>> } catch(Exception e) {
>>> e.printStackTrace();
>>> }
>>> }
>>> }
>>>
>>> // end of upload image
>>>
>>> Timestamp fromDate = UtilDateTime.nowTimestamp();
>>> GenericDelegator delegator = (GenericDelegator)
>>> request.getAttribute("delegator");
>>> try {
>>> GenericValue DataImportCategoryList =
>>> delegator.findByPrimaryKey("DataImportCategory",
>>> UtilMisc.toMap("productCategoryId", productCategoryId));
>>>
>>> if(DataImportCategoryList != null){
>>> String categoryId =
>>> DataImportCategoryList.getString("productCategoryId");
>>> if(categoryId.equals(productCategoryId)){
>>> failCount++;
>>> }
>>> }else {
>>> GenericValue newImportCategory =
>>> delegator.makeValue("DataImportCategory", null);
>>>
>>> newImportCategory.set("productCategoryId",
>>> productCategoryId.trim());
>>> if(productCategoryTypeId != null &&
>>> productCategoryTypeId.length()
>>> > 0){
>>> newImportCategory.set("productCategoryTypeId",
>>> productCategoryTypeId.trim());
>>> } else {
>>>
>>> newImportCategory.set("productCategoryTypeId", "CATALOG_CATEGORY");
>>> }
>>> if(primaryParentCategoryId != null &&
>>> primaryParentCategoryId.length() > 0)
>>> newImportCategory.set("primaryParentCategoryId",
>>> primaryParentCategoryId);
>>> newImportCategory.set("categoryName", categoryName.trim());
>>> newImportCategory.set("description", description);
>>> newImportCategory.set("longDescription", longDescription);
>>> newImportCategory.set("categoryImageUrl", catImageUrl);
>>> newImportCategory.set("linkOneImageUrl", linkImageUrl);
>>> newImportCategory.set("fromDate",
>>> fromDate);
>>> try {
>>>
>>> delegator.create(newImportCategory);
>>> Debug.logInfo("Successfully
>>> imported category ["+productCategoryId+" from row no "+ i+1 +"].",
>>> module);
>>> successCount++;
>>> } catch (GenericEntityException e)
>>> {
>>>
>>> Debug.logWarning(e.getMessage(),
>>> module);
>>> }
>>>
>>> }
>>> } catch(GenericEntityException e) {
>>> Debug.logError("Exception occured :"+e.getMessage(), module);
>>> }
>>> }
>>>
>>> }
>>>
>>>
>>> it create the temp file in corresponding directory but there is no
>>> actual
>>> image at that path.
>>>
>>> So any one have any idea regarding this please suggest me.
>>>
>>>
>>> Thanks.
>>>
>>> Nalin Chandra
>>>
>>
>> --
>> View this message in context:
>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>
>
>
>
--
View this message in context: http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p586989.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: how to import data from excel sheet
Posted by BJ Freeman <bj...@free-man.net>.
look in the /framework/datafile/dtd/datafiles.xsd
it has how to make your data definition file.
also
http://docs.ofbiz.org/display/OFBENDUSER/OFBiz's+Data+File+Tools
http://docs.ofbiz.org/display/OFBIZ/Handling+of+External+data
once you build the data definintion files then you can use
https://localhost:8443/webtools/control/viewdatafile
su2 sent the following on 11/11/2009 11:22 AM:
> Hi Jacques,
>
> I want to insert all the excel data into the database. I looked at
> viewdatafile screen, and looked like, using that I can not insert all my
> excel data into database tables.
>
>
> Jacques Le Roux wrote:
>> I'd use CSV with https://localhost:8443/webtools/control/viewdatafile. I
>> let you find more on this ML archives...
>>
>> Jacques
>>
>> From: "su2" <sh...@pexsupply.com>
>>> Hi Nalin,
>>>
>>> We have similar requirement where we want to upload/import all the
>>> product
>>> information from excel sheet.
>>>
>>> I would really appreciate if you could tell me, how we can achieve that.
>>>
>>> Thank you.
>>> Shuchi-
>>>
>>> Nalin Chandra wrote:
>>>> Hi All
>>>>
>>>> I want to import the product related data from excel sheet. I did it but
>>>> i
>>>> have one problem in this.
>>>> I excel sheet i have absolute path of image now i want to upload the
>>>> image
>>>> from that absolute path to our application as well store the relative
>>>> path
>>>> in data base as we did in product content section.
>>>> i am able to store the relative path in database but image in not
>>>> uploading in the required folder.
>>>>
>>>> Main problem is that here i am not using any html form.
>>>>
>>>> i paste the code below for it
>>>>
>>>> public static String uploadCategory(HttpServletRequest request,
>>>> HttpServletResponse response) {
>>>>
>>>> try
>>>> {
>>>> String filename = "D:/category.xls";
>>>> WorkbookSettings ws = new WorkbookSettings();
>>>> ws.setLocale(new Locale("en", "EN"));
>>>> Workbook workbook = Workbook.getWorkbook(new
>>>> File(filename),ws);
>>>> Sheet s = workbook.getSheet(0);
>>>> readCategoryDataSheet(s, request);
>>>> workbook.close();
>>>> }
>>>> catch (IOException e)
>>>> {
>>>> e.printStackTrace();
>>>> }
>>>> catch (BiffException e)
>>>> {
>>>> e.printStackTrace();
>>>> }
>>>>
>>>> return "success";
>>>> }
>>>>
>>>> private static void readCategoryDataSheet(Sheet s,
>>>> HttpServletRequest
>>>> request)
>>>> {
>>>> Cell rowData[] = null;
>>>> int successCount = 0;
>>>> int failCount = 0;
>>>> int rows = s.getRows();
>>>> int column = s.getColumns();
>>>> String productCategoryId = "";
>>>> String productCategoryTypeId = "";
>>>> String primaryParentCategoryId = "";
>>>> String categoryName = "";
>>>> String description = "";
>>>> String longDescription = "";
>>>> String categoryImageUrl = "";
>>>> String linkOneImageUrl = "";
>>>> String prodCatalogId = "";
>>>> String prodCatalogCategoryTypeId = "";
>>>> String sequenceNum = "";
>>>>
>>>> for (int i = 1; i < rows; i++) {
>>>> rowData = s.getRow(i);
>>>> if (rowData[0].getContents().length() != 0) {
>>>> for (int j = 0; j < column; j++) {
>>>> switch (j) {
>>>> case 0:
>>>> productCategoryId =
>>>> rowData[j].getContents();
>>>> case 1:
>>>> productCategoryTypeId =
>>>> rowData[j].getContents();
>>>> case 2:
>>>> primaryParentCategoryId
>>>> =
>>>> rowData[j].getContents();
>>>> case 3:
>>>> categoryName =
>>>> rowData[j].getContents();
>>>> case 4:
>>>> description =
>>>> rowData[j].getContents();
>>>> case 5:
>>>> longDescription =
>>>> rowData[j].getContents();
>>>> case 6:
>>>> categoryImageUrl =
>>>> rowData[j].getContents();
>>>> case 7:
>>>> linkOneImageUrl =
>>>> rowData[j].getContents();
>>>> default:
>>>> break;
>>>> }
>>>> }
>>>> }
>>>> String imageFilenameFormat =
>>>> UtilProperties.getPropertyValue("catalog", "image.filename.format");
>>>> String imageServerPath =
>>>> UtilProperties.getPropertyValue("catalog", "image.server.path");
>>>> String imageUrlPrefix =
>>>> UtilProperties.getPropertyValue("catalog", "image.url.prefix");
>>>>
>>>>
>>>> // upload image
>>>> FlexibleStringExpander filenameExpander = new
>>>> FlexibleStringExpander(imageFilenameFormat);
>>>> String catImageUrl = "";
>>>> String linkImageUrl = "";
>>>> if(categoryImageUrl != null &&
>>>> categoryImageUrl.length() > 0){
>>>> Object forLock = new Object();
>>>> String contentType = null;
>>>> String categoryImageLocation =
>>>> filenameExpander.expandString(UtilMisc.toMap("location", "categories",
>>>> "type", "category", "id", productCategoryId));
>>>> String filePathPrefix = "";
>>>> String filenameToUse =
>>>> categoryImageLocation;
>>>> if (categoryImageLocation.lastIndexOf("/")
>>>> !=
>>>> -1) {
>>>> filePathPrefix =
>>>> categoryImageLocation.substring(0,
>>>> categoryImageLocation.lastIndexOf("/")
>>>> + 1); // adding 1 to include the trailing slash
>>>> filenameToUse =
>>>> categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/") +
>>>> 1);
>>>> }
>>>>
>>>> int i1;
>>>> if (contentType != null && (i1 =
>>>> contentType.indexOf("boundary=")) != -1) {
>>>> contentType = contentType.substring(i1 +
>>>> 9);
>>>> contentType = "--" + contentType;
>>>> }
>>>>
>>>> String defaultFileName = filenameToUse +
>>>> "_temp";
>>>> HttpRequestFileUpload uploadObject = new
>>>> HttpRequestFileUpload();
>>>>
>>>> uploadObject.setOverrideFilename(defaultFileName);
>>>> uploadObject.setSavePath(imageServerPath +
>>>> "/"
>>>> + filePathPrefix);
>>>> try{
>>>> uploadObject.doUpload(request);
>>>>
>>>> }catch(IOException e){
>>>> Debug.logInfo("Image uploading failure",
>>>> module);
>>>> }
>>>> String categoryImageFileName =
>>>> uploadObject.getFilename();
>>>>
>>>> if (categoryImageFileName != null &&
>>>> categoryImageFileName.length() > 0) {
>>>> if
>>>> (categoryImageFileName.lastIndexOf(".")
>>>>> 0 && categoryImageFileName.lastIndexOf(".") <
>>>> categoryImageFileName.length()) {
>>>> filenameToUse +=
>>>> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
>>>> } else {
>>>> filenameToUse += ".jpg";
>>>> }
>>>> try{
>>>> String characterEncoding =
>>>> request.getCharacterEncoding();
>>>> catImageUrl = imageUrlPrefix + "/" +
>>>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
>>>> characterEncoding);
>>>> }catch(Exception e){
>>>> System.out.println("Incoding Problem");
>>>> }
>>>>
>>>> try {
>>>> File file = new File(imageServerPath
>>>> +
>>>> "/" + filePathPrefix, defaultFileName);
>>>> File file1 = new
>>>> File(imageServerPath
>>>> + "/" + filePathPrefix, filenameToUse);
>>>> try {
>>>> file1.delete();
>>>> } catch(Exception e) {
>>>> System.out.println("error
>>>> deleting
>>>> existing file (not neccessarily a problem)");
>>>> }
>>>> file.renameTo(file1);
>>>> } catch(Exception e) {
>>>> e.printStackTrace();
>>>> }
>>>> }
>>>> }
>>>>
>>>> // end of upload image
>>>>
>>>> Timestamp fromDate = UtilDateTime.nowTimestamp();
>>>> GenericDelegator delegator = (GenericDelegator)
>>>> request.getAttribute("delegator");
>>>> try {
>>>> GenericValue DataImportCategoryList =
>>>> delegator.findByPrimaryKey("DataImportCategory",
>>>> UtilMisc.toMap("productCategoryId", productCategoryId));
>>>>
>>>> if(DataImportCategoryList != null){
>>>> String categoryId =
>>>> DataImportCategoryList.getString("productCategoryId");
>>>> if(categoryId.equals(productCategoryId)){
>>>> failCount++;
>>>> }
>>>> }else {
>>>> GenericValue newImportCategory =
>>>> delegator.makeValue("DataImportCategory", null);
>>>>
>>>> newImportCategory.set("productCategoryId",
>>>> productCategoryId.trim());
>>>> if(productCategoryTypeId != null &&
>>>> productCategoryTypeId.length()
>>>>> 0){
>>>> newImportCategory.set("productCategoryTypeId",
>>>> productCategoryTypeId.trim());
>>>> } else {
>>>>
>>>> newImportCategory.set("productCategoryTypeId", "CATALOG_CATEGORY");
>>>> }
>>>> if(primaryParentCategoryId != null &&
>>>> primaryParentCategoryId.length() > 0)
>>>> newImportCategory.set("primaryParentCategoryId",
>>>> primaryParentCategoryId);
>>>> newImportCategory.set("categoryName", categoryName.trim());
>>>> newImportCategory.set("description", description);
>>>> newImportCategory.set("longDescription", longDescription);
>>>> newImportCategory.set("categoryImageUrl", catImageUrl);
>>>> newImportCategory.set("linkOneImageUrl", linkImageUrl);
>>>> newImportCategory.set("fromDate",
>>>> fromDate);
>>>> try {
>>>>
>>>> delegator.create(newImportCategory);
>>>> Debug.logInfo("Successfully
>>>> imported category ["+productCategoryId+" from row no "+ i+1 +"].",
>>>> module);
>>>> successCount++;
>>>> } catch (GenericEntityException e)
>>>> {
>>>>
>>>> Debug.logWarning(e.getMessage(),
>>>> module);
>>>> }
>>>>
>>>> }
>>>> } catch(GenericEntityException e) {
>>>> Debug.logError("Exception occured :"+e.getMessage(), module);
>>>> }
>>>> }
>>>>
>>>> }
>>>>
>>>>
>>>> it create the temp file in corresponding directory but there is no
>>>> actual
>>>> image at that path.
>>>>
>>>> So any one have any idea regarding this please suggest me.
>>>>
>>>>
>>>> Thanks.
>>>>
>>>> Nalin Chandra
>>>>
>>> --
>>> View this message in context:
>>> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
>>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>>
>>
>>
>
--
BJ Freeman
http://www.businessesnetwork.com/automation
http://bjfreeman.elance.com
http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
Systems Integrator.
Re: how to import data from excel sheet
Posted by Jacques Le Roux <ja...@les7arts.com>.
I'd use CSV with https://localhost:8443/webtools/control/viewdatafile. I let you find more on this ML archives...
Jacques
From: "su2" <sh...@pexsupply.com>
>
> Hi Nalin,
>
> We have similar requirement where we want to upload/import all the product
> information from excel sheet.
>
> I would really appreciate if you could tell me, how we can achieve that.
>
> Thank you.
> Shuchi-
>
> Nalin Chandra wrote:
>>
>> Hi All
>>
>> I want to import the product related data from excel sheet. I did it but i
>> have one problem in this.
>> I excel sheet i have absolute path of image now i want to upload the image
>> from that absolute path to our application as well store the relative path
>> in data base as we did in product content section.
>> i am able to store the relative path in database but image in not
>> uploading in the required folder.
>>
>> Main problem is that here i am not using any html form.
>>
>> i paste the code below for it
>>
>> public static String uploadCategory(HttpServletRequest request,
>> HttpServletResponse response) {
>>
>> try
>> {
>> String filename = "D:/category.xls";
>> WorkbookSettings ws = new WorkbookSettings();
>> ws.setLocale(new Locale("en", "EN"));
>> Workbook workbook = Workbook.getWorkbook(new File(filename),ws);
>> Sheet s = workbook.getSheet(0);
>> readCategoryDataSheet(s, request);
>> workbook.close();
>> }
>> catch (IOException e)
>> {
>> e.printStackTrace();
>> }
>> catch (BiffException e)
>> {
>> e.printStackTrace();
>> }
>>
>> return "success";
>> }
>>
>> private static void readCategoryDataSheet(Sheet s, HttpServletRequest
>> request)
>> {
>> Cell rowData[] = null;
>> int successCount = 0;
>> int failCount = 0;
>> int rows = s.getRows();
>> int column = s.getColumns();
>> String productCategoryId = "";
>> String productCategoryTypeId = "";
>> String primaryParentCategoryId = "";
>> String categoryName = "";
>> String description = "";
>> String longDescription = "";
>> String categoryImageUrl = "";
>> String linkOneImageUrl = "";
>> String prodCatalogId = "";
>> String prodCatalogCategoryTypeId = "";
>> String sequenceNum = "";
>>
>> for (int i = 1; i < rows; i++) {
>> rowData = s.getRow(i);
>> if (rowData[0].getContents().length() != 0) {
>> for (int j = 0; j < column; j++) {
>> switch (j) {
>> case 0:
>> productCategoryId =
>> rowData[j].getContents();
>> case 1:
>> productCategoryTypeId =
>> rowData[j].getContents();
>> case 2:
>> primaryParentCategoryId =
>> rowData[j].getContents();
>> case 3:
>> categoryName =
>> rowData[j].getContents();
>> case 4:
>> description =
>> rowData[j].getContents();
>> case 5:
>> longDescription =
>> rowData[j].getContents();
>> case 6:
>> categoryImageUrl =
>> rowData[j].getContents();
>> case 7:
>> linkOneImageUrl =
>> rowData[j].getContents();
>> default:
>> break;
>> }
>> }
>> }
>> String imageFilenameFormat =
>> UtilProperties.getPropertyValue("catalog", "image.filename.format");
>> String imageServerPath =
>> UtilProperties.getPropertyValue("catalog", "image.server.path");
>> String imageUrlPrefix =
>> UtilProperties.getPropertyValue("catalog", "image.url.prefix");
>>
>>
>> // upload image
>> FlexibleStringExpander filenameExpander = new
>> FlexibleStringExpander(imageFilenameFormat);
>> String catImageUrl = "";
>> String linkImageUrl = "";
>> if(categoryImageUrl != null &&
>> categoryImageUrl.length() > 0){
>> Object forLock = new Object();
>> String contentType = null;
>> String categoryImageLocation =
>> filenameExpander.expandString(UtilMisc.toMap("location", "categories",
>> "type", "category", "id", productCategoryId));
>> String filePathPrefix = "";
>> String filenameToUse = categoryImageLocation;
>> if (categoryImageLocation.lastIndexOf("/") !=
>> -1) {
>> filePathPrefix =
>> categoryImageLocation.substring(0, categoryImageLocation.lastIndexOf("/")
>> + 1); // adding 1 to include the trailing slash
>> filenameToUse =
>> categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/") +
>> 1);
>> }
>>
>> int i1;
>> if (contentType != null && (i1 =
>> contentType.indexOf("boundary=")) != -1) {
>> contentType = contentType.substring(i1 +
>> 9);
>> contentType = "--" + contentType;
>> }
>>
>> String defaultFileName = filenameToUse +
>> "_temp";
>> HttpRequestFileUpload uploadObject = new
>> HttpRequestFileUpload();
>>
>> uploadObject.setOverrideFilename(defaultFileName);
>> uploadObject.setSavePath(imageServerPath + "/"
>> + filePathPrefix);
>> try{
>> uploadObject.doUpload(request);
>>
>> }catch(IOException e){
>> Debug.logInfo("Image uploading failure",
>> module);
>> }
>> String categoryImageFileName =
>> uploadObject.getFilename();
>>
>> if (categoryImageFileName != null &&
>> categoryImageFileName.length() > 0) {
>> if (categoryImageFileName.lastIndexOf(".")
>> > 0 && categoryImageFileName.lastIndexOf(".") <
>> categoryImageFileName.length()) {
>> filenameToUse +=
>> categoryImageFileName.substring(categoryImageFileName.lastIndexOf("."));
>> } else {
>> filenameToUse += ".jpg";
>> }
>> try{
>> String characterEncoding =
>> request.getCharacterEncoding();
>> catImageUrl = imageUrlPrefix + "/" +
>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse,
>> characterEncoding);
>> }catch(Exception e){
>> System.out.println("Incoding Problem");
>> }
>>
>> try {
>> File file = new File(imageServerPath +
>> "/" + filePathPrefix, defaultFileName);
>> File file1 = new File(imageServerPath
>> + "/" + filePathPrefix, filenameToUse);
>> try {
>> file1.delete();
>> } catch(Exception e) {
>> System.out.println("error deleting
>> existing file (not neccessarily a problem)");
>> }
>> file.renameTo(file1);
>> } catch(Exception e) {
>> e.printStackTrace();
>> }
>> }
>> }
>>
>> // end of upload image
>>
>> Timestamp fromDate = UtilDateTime.nowTimestamp();
>> GenericDelegator delegator = (GenericDelegator)
>> request.getAttribute("delegator");
>> try {
>> GenericValue DataImportCategoryList =
>> delegator.findByPrimaryKey("DataImportCategory",
>> UtilMisc.toMap("productCategoryId", productCategoryId));
>>
>> if(DataImportCategoryList != null){
>> String categoryId =
>> DataImportCategoryList.getString("productCategoryId");
>> if(categoryId.equals(productCategoryId)){
>> failCount++;
>> }
>> }else {
>> GenericValue newImportCategory =
>> delegator.makeValue("DataImportCategory", null);
>>
>> newImportCategory.set("productCategoryId",
>> productCategoryId.trim());
>> if(productCategoryTypeId != null && productCategoryTypeId.length()
>> > 0){
>> newImportCategory.set("productCategoryTypeId",
>> productCategoryTypeId.trim());
>> } else {
>>
>> newImportCategory.set("productCategoryTypeId", "CATALOG_CATEGORY");
>> }
>> if(primaryParentCategoryId != null &&
>> primaryParentCategoryId.length() > 0)
>> newImportCategory.set("primaryParentCategoryId",
>> primaryParentCategoryId);
>> newImportCategory.set("categoryName", categoryName.trim());
>> newImportCategory.set("description", description);
>> newImportCategory.set("longDescription", longDescription);
>> newImportCategory.set("categoryImageUrl", catImageUrl);
>> newImportCategory.set("linkOneImageUrl", linkImageUrl);
>> newImportCategory.set("fromDate",
>> fromDate);
>> try {
>>
>> delegator.create(newImportCategory);
>> Debug.logInfo("Successfully
>> imported category ["+productCategoryId+" from row no "+ i+1 +"].",
>> module);
>> successCount++;
>> } catch (GenericEntityException e) {
>> Debug.logWarning(e.getMessage(),
>> module);
>> }
>>
>> }
>> } catch(GenericEntityException e) {
>> Debug.logError("Exception occured :"+e.getMessage(), module);
>> }
>> }
>>
>> }
>>
>>
>> it create the temp file in corresponding directory but there is no actual
>> image at that path.
>>
>> So any one have any idea regarding this please suggest me.
>>
>>
>> Thanks.
>>
>> Nalin Chandra
>>
>
> --
> View this message in context: http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>