Solved

Prepare Retail Edge POS Import File in Excel

Posted on 2011-02-24
3
572 Views
Last Modified: 2012-05-11
Hi,

I need to create an import file for a POS system (RetailEdge) in order to import all inventory items to the POS software. I have all inventory items in an Excel spreadsheet, all the required information is separated by columns and in (1) line for example:

Inv_Sku, Inv_Desc, Inv_ItemID, Loc_settings_LocID,.........

But the problem is that the format required by Retail Edge to import inventory items is the following:

<REDI_Invent>
Inv_Sku,Inv_Desc,Inv_ItemID,Loc_Settings_LocID,Inv_Quan,Inv_DepartID,Inv_VendorID,Inv_ClassID,Inv_Cost_Last,Inv_Cost_Avg,Inv_Part_Mfr,Inv_Part_Vendor,Inv_StockType,Inv_UPC1,Inv_UPC2,Inv_ExpCode,Inv_Weight,Inv_User1,Inv_User2,Inv_User3,Inv_User4,Inv_User5,Inv_MatrixID,Inv_Matrix_D1,Inv_Matrix_D2,Inv_Matrix_Parent,Inv_AddOns,Inv_Aliases,Inv_AutoScale,Inv_Case_Parent,Inv_Case_Count,Inv_Loc_Taxable,Inv_Loc_Price_Was,Inv_Loc_Price1,Inv_Loc_Price2,Inv_Loc_Price3,Inv_Loc_Price4,Inv_Loc_Price5,Inv_Loc_Min1,Inv_Loc_Min2,Inv_Loc_Min3,Inv_Loc_Min4,Inv_Loc_Max1,Inv_Loc_Max2,Inv_Loc_Max3,Inv_Loc_Max4,Inv_Loc_BinShelf
1001,"Sony TV 32"" TEST ITEM",843712,MainStore,0,ELECTRONICS,VENDOR,~~Unassigned~~,2.99,0.00,,,Stock,12121212121565212,,,,,,,,,~~Unassigned~~,,,,,,False,,0,True,5.99,5.99,5.99,5.99,5.99,5.99,0,0,0,0,0,0,0,0,</REDI_Invent>
<REDI_Depart>
Dept_DepartID,Dept_Desc,Dept_Margin
ELECTRONICS,ELECTRONICS,50</REDI_Depart>
<REDI_Vendor>
Vend_VendorID,Vend_Desc,Vend_Contact,Vend_AccountNum,Vend_Street1,Vend_Street2,Vend_City,Vend_State,Vend_PostalCode,Vend_Country,Vend_Phone,Vend_Fax,Vend_EMail
VENDOR,Vendor,,,,,,,,,,,</REDI_Vendor>

NOTE: The bold letters mean the information from (1) record or inventory item, the other words are the heading or field names.


The example above is only the information of (1) item, imagine if I have to import 2,000 inventory items. I know that should exist a way to extract the information that I have on the table and convert it to this format. I need a simple example of how to do it in Excel, Access or Visual Basic in order for me to get an idea.


If you have any questions please just let me know.

Thanks in advance for any comments.

Regards,
0
Comment
Question by:mmoralespr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 34976971
Hi, without a sample xls file, and a sample output file, I have created this script which might do the job for you.

Specify your XLS file, and the name you want for the output file, and it should open the XLS file, read the rows (from row 2), and create the import data.

Regards,

Rob.
strExcelFile = "Inventory.xls"
strImportFile = "ImportData.txt"

Const xlUp = -4162
Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(strExcelFile, False, False)
objExcel.Visible = True
Set objSheet = objWB.Sheets(1)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutput = objFSO.CreateTextFile(strImportFile, True)

For intRow = 2 To objSheet.Cells(65536, "A").End(xlUp).Row
	strRecord = "<REDI_Invent>" & VbCrLf & _
		"Inv_Sku,Inv_Desc,Inv_ItemID,Loc_Settings_LocID,Inv_Quan,Inv_DepartID,Inv_VendorID,Inv_ClassID,Inv_Cost_Last,Inv_Cost_Avg,Inv_Part_Mfr,Inv_Part_Vendor,Inv_StockType,Inv_UPC1,Inv_UPC2,Inv_ExpCode,Inv_Weight,Inv_User1,Inv_User2,Inv_User3,Inv_User4,Inv_User5,Inv_MatrixID,Inv_Matrix_D1,Inv_Matrix_D2,Inv_Matrix_Parent,Inv_AddOns,Inv_Aliases,Inv_AutoScale,Inv_Case_Parent,Inv_Case_Count,Inv_Loc_Taxable,Inv_Loc_Price_Was,Inv_Loc_Price1,Inv_Loc_Price2,Inv_Loc_Price3,Inv_Loc_Price4,Inv_Loc_Price5,Inv_Loc_Min1,Inv_Loc_Min2,Inv_Loc_Min3,Inv_Loc_Min4,Inv_Loc_Max1,Inv_Loc_Max2,Inv_Loc_Max3,Inv_Loc_Max4,Inv_Loc_BinShelf" & VbCrLf
	For intCol = 1 To 47
		strRecord = strRecord & objSheet.Cells(intRow, intCol).Value & ","
	Next
	strRecord = strRecord & "</REDI_Invent>" & VbCrLf & _
		"<REDI_Depart>" & VbCrLf & _
		"Dept_DepartID,Dept_Desc,Dept_Margin" & VbCrLf
	For intCol = 48 To 51
		strRecord = strRecord & objSheet.Cells(intRow, intCol).Value & ","
	Next
	strRecord = strRecord & "</REDI_Depart>" & VbCrLf & _
		"<REDI_Vendor>" & VbCrLf & _
		"Vend_VendorID,Vend_Desc,Vend_Contact,Vend_AccountNum,Vend_Street1,Vend_Street2,Vend_City,Vend_State,Vend_PostalCode,Vend_Country,Vend_Phone,Vend_Fax,Vend_EMail" & VbCrLf
	For intCol = 52 To 64
		strRecord = strRecord & objSheet.Cells(intRow, intCol).Value & ","
	Next
	strRecord = strRecord & "</REDI_Vendor>"
	
	objOutput.WriteLine strRecord
Next

objOutput.Close

objWB.Close False
objExcel.Quit

MsgBox "Done. Please see " & strImportFile

Open in new window

0
 

Author Closing Comment

by:mmoralespr
ID: 34978895
Ohh men, you are amazing, It worked. I love Expert Exchange for this type of things.

I cant believe it. hehehehe!

Thanks Rob.  Take Care!

Michael
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34983843
No problem.  Please make sure the exact number of fields are in the output file.  I got confused with all the commas in your example....

Thanks for the grade.

Regards,

Rob.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to transfer values from one userform in excel to another 14 43
Lookup range formula 7 27
Excel - IF criterion 2 23
Excel Index/Match issue 4 14
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question