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