how to import data from excel sheet

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

how to import data from excel sheet

Nalin Chandra
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
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

BJ Freeman
images, that are displayed are stored physically at
/framework/images/webapp/images/catalog
/frameework/images/webapp/images/products/large
/framework/images/webapp/images/products/medium
/framework/images/webapp/images/products/small
/framework/images/webapp/images/products/additional

the last one is used with the service addAdditionalViewForProductFromURL
which take the image in that directory and resizes it for the other
product directories.




nalin chandra sent the following on 5/9/2009 11:17 PM:

> 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

--
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.

Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

Nalin Chandra
Thanks for reply.

But that is not my problem.
Once again i clear my problem.
I want to upload all the information about product category etc from the excel sheet where we write all the required information about product and category. In that excel sheet i also mentioned the local system path of image. Now i want to upload the image from local system to remote server using path of that. Now i read the local system file path from excel sheet as shown in bold.

I know we need a form to upload the file
<form method="post" action="uploadCategory" enctype="multipart/form-data">
  file: <input type="file" name="file-upload">
  <input type="submit">
</form> .

but i my case there is no form. i got the file path in my method now i want to upload the that file from local system to remote server.

I think now you under stand my problem.
with the use of this we have no need to upload the product detail one by one to the server.

waiting for your reply.

Thanks

Nalin Chandra




>
> 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

Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

BJ Freeman
In reply to this post by BJ Freeman
Ok the way to upload changes with out a form.
First understand that in the form way the image is uploaded into server
memory, from your desktop, then you read the data from the server memory
and put it into a file. This is a function of the webbrowser not ofbiz.
The problem is that a server can not read from you local drive. You can
only do this thru a web browser.

so you must either put the images on the server, thru FTP then reference
them that way in your excel.
or put the images on a machine that is exposed to the internet with a
web server and give a http:// type url to be uploaded, in your excel.


nalin chandra sent the following on 5/10/2009 7:24 AM:

> Thanks for reply.
>
> But that is not my problem.
> Once again i clear my problem.
> I want to upload all the information about product category etc from the
> excel sheet where we write all the required information about product and
> category. In that excel sheet i also mentioned the local system path of
> image. Now i want to upload the image from local system to remote server
> using path of that. Now i read the local system file path from excel sheet
> as shown in bold.
>
> I know we need a form to upload the file
> <form method="post" action="uploadCategory" enctype="multipart/form-data">
>   file: <input type="file" name="file-upload">
>   <input type="submit">
> </form> .
>
> but i my case there is no form. i got the file path in my method now i want
> to upload the that file from local system to remote server.
>
> I think now you under stand my problem.
> with the use of this we have no need to upload the product detail one by one
> to the server.
>
> waiting for your reply.
>
> Thanks
>
> Nalin Chandra
>
>
>
>
>> 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
>
>

--
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.

Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

Nalin Chandra
Hi

This is long time i back on this forum.

thanks for all your suggestions. I solve this problem with different method. Now we store the path of the image using Excel sheet and upload the corresponding images in the respective folder.


Nalin Chandra
Ofbiz developer


BJ Freeman wrote
Ok the way to upload changes with out a form.
First understand that in the form way the image is uploaded into server
memory, from your desktop, then you read the data from the server memory
and put it into a file. This is a function of the webbrowser not ofbiz.
The problem is that a server can not read from you local drive. You can
only do this thru a web browser.

so you must either put the images on the server, thru FTP then reference
them that way in your excel.
or put the images on a machine that is exposed to the internet with a
web server and give a http:// type url to be uploaded, in your excel.


nalin chandra sent the following on 5/10/2009 7:24 AM:
> Thanks for reply.
>
> But that is not my problem.
> Once again i clear my problem.
> I want to upload all the information about product category etc from the
> excel sheet where we write all the required information about product and
> category. In that excel sheet i also mentioned the local system path of
> image. Now i want to upload the image from local system to remote server
> using path of that. Now i read the local system file path from excel sheet
> as shown in bold.
>
> I know we need a form to upload the file
> <form method="post" action="uploadCategory" enctype="multipart/form-data">
>   file: <input type="file" name="file-upload">
>   <input type="submit">
> </form> .
>
> but i my case there is no form. i got the file path in my method now i want
> to upload the that file from local system to remote server.
>
> I think now you under stand my problem.
> with the use of this we have no need to upload the product detail one by one
> to the server.
>
> waiting for your reply.
>
> Thanks
>
> Nalin Chandra
>
>
>
>
>> 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
>
>

--
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.
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

BJ Freeman
In reply to this post by BJ Freeman
and Update to ofbiz trunk after release 4.0 lets you pass a URL to the
service and it will import, size the images and put one for each size in
the correct folder.

you can see this work but going to the product content page and paste a
URL in the upload box

glad you found a solution.

Nalin Chandra sent the following on 10/27/2009 6:08 PM:

> Hi
>
> This is long time i back on this forum.
>
> thanks for all your suggestions. I solve this problem with different method.
> Now we store the path of the image using Excel sheet and upload the
> corresponding images in the respective folder.
>
>
> Nalin Chandra
> Ofbiz developer
>
>
>
> BJ Freeman wrote:
>> Ok the way to upload changes with out a form.
>> First understand that in the form way the image is uploaded into server
>> memory, from your desktop, then you read the data from the server memory
>> and put it into a file. This is a function of the webbrowser not ofbiz.
>> The problem is that a server can not read from you local drive. You can
>> only do this thru a web browser.
>>
>> so you must either put the images on the server, thru FTP then reference
>> them that way in your excel.
>> or put the images on a machine that is exposed to the internet with a
>> web server and give a http:// type url to be uploaded, in your excel.
>>
>>
>> nalin chandra sent the following on 5/10/2009 7:24 AM:
>>> Thanks for reply.
>>>
>>> But that is not my problem.
>>> Once again i clear my problem.
>>> I want to upload all the information about product category etc from the
>>> excel sheet where we write all the required information about product and
>>> category. In that excel sheet i also mentioned the local system path of
>>> image. Now i want to upload the image from local system to remote server
>>> using path of that. Now i read the local system file path from excel
>>> sheet
>>> as shown in bold.
>>>
>>> I know we need a form to upload the file
>>> <form method="post" action="uploadCategory"
>>> enctype="multipart/form-data">
>>>   file: <input type="file" name="file-upload">
>>>   <input type="submit">
>>> </form> .
>>>
>>> but i my case there is no form. i got the file path in my method now i
>>> want
>>> to upload the that file from local system to remote server.
>>>
>>> I think now you under stand my problem.
>>> with the use of this we have no need to upload the product detail one by
>>> one
>>> to the server.
>>>
>>> waiting for your reply.
>>>
>>> Thanks
>>>
>>> Nalin Chandra
>>>
>>>
>>>
>>>
>>>> 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
>>>
>> --
>> 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.
>>
>>
>>
>

--
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.

su2
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

su2
In reply to this post by Nalin Chandra
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
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

Jacques Le Roux
Administrator
I'd use CSV with https://localhost:8443/webtools/control/viewdatafile. I let you find more on this ML archives...

Jacques

From: "su2" <[hidden email]>

>
> 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.
>

su2
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

su2
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" <shuchi@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.
>
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

S K Pradeep kumar
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 <[hidden email]> 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" <[hidden email]>
> >>
> >> 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.
>
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

Jacques Le Roux
Administrator
And for that use viewdatafile.... There are messages in user ML explaining how to do it...

Jacques

From: "S K Pradeep kumar" <[hidden email]>

> 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 <[hidden email]> 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" <[hidden email]>
>> >>
>> >> 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.
>>
>

su2
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

su2
In reply to this post by S K Pradeep kumar
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 <shuchi@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" <shuchi@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.
>
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

S K Pradeep kumar
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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]>
> >> >>
> >> >> 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.
>
su2
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

su2
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 <shuchi@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 <shuchi@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" <shuchi@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.
>
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

BJ Freeman
In reply to this post by Jacques Le Roux
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" <[hidden email]>
>>> 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.

Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

S K Pradeep kumar
In reply to this post by su2
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]>
> >> >> >>
> >> >> >> 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.
>
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

Rishi Solanki
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 <
[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]>
> > >> >> >>
> > >> >> >> 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.
> >
>
su2
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

su2
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 <shuchi@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 <shuchi@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 <shuchi@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" <shuchi@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.
> >
>
su2
Reply | Threaded
Open this post in threaded view
|

Re: how to import data from excel sheet

su2
In reply to this post by Rishi Solanki
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 <shuchi@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 <shuchi@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 <shuchi@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" <shuchi@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.
> >
>