Import sales orders from a csv file

Hi Forum,

 

I've trying to have this script working but i guess i need to post it to get help from you guys....

What i am trying to do is import sales orders from a csv file.  some orders have more than 1 line, most of them are single line.

i need to get the Customer Number doing a find by customer Name. Once i get the customer number i build the S/O header and lines. My goal is to have one script... i have one script that creates new customers and new ship to codes.  The S/O code will be appended to the other script, but that's a different story.

i cannot make it loop if the order has more than 1 line.

 

Here is the code... what's left of the code, i've been adding and deleting lines...

 

'Create ProvideX COM Object
Set oScript = CreateObject ("ProvideX.Script")

'The Init method must be the first method called
' Please remember to correct the MAS90\Home path
oScript.Init("C:\Sage Software\MAS 90\VERSION4.4.6\Mas90\home")

'The Session object must be the first MAS 90 object created
Set oSS = oScript.NewObject("SY_Session")

mdate = oSession.sModuleDate
'sdate = oSession.sSystemDate
'ucode = oSession.sUserCode

' Set the user for the Session
r = oSS.nLogon()
If r=0 Then
	' Please remember to correct the username, password
	r = oss.nSetUser("im","metals")
End If

' Set the company, module date and module for the Session
r = oss.nsetcompany("FIT")
r = oSS.nSetDate("S/O",mdate)
r = oSS.nSetModule("S/O")

' Instantiate a Sales Order business object
oSEC = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_ui"))
Set o = oScript.NewObject("SO_SalesOrder_bus", oSS)

' Instantiate a AR_Customer business object
'Set oCust = oScript.NewObject("AR_Customer_bus, oSS)

'Open the CSV file

dim fs,objTextFile
set fs=CreateObject("Scripting.FileSystemObject")
dim arrStr
set objTextFile = fs.OpenTextFile("C:\Akatz\Fitness First\orders.csv")

Do while NOT objTextFile.AtEndOfStream
arrStr = split(objTextFile.ReadLine,",")
strOrderNo = arrstr(0)
strName = arrStr(1)
strPhone = arrStr(2)
strAddress1 = arrStr(12)
strAddress2 = arrStr(13)
strAddress3 = arrStr(14)
'strCity = arrStr(15)
'strState = arrStr(16)
strZip = arrStr(17)
strCountryCode = arrStr(18)
strItem = arrStr(3)
strQty = arrStr(4)
strPrice = arrStr(6)
'strFreight = arrStr(8)
strFreight = strFreight + arrStr(8)

'Read AR_Customer to find if Customer exists

retval = 0
name = ""
custExist = 0
ARDivisionNo = "00"

retval = oCust.nSetIndex("KNAME")
retval = oCust.nFind(strName)   ' this will fail
retval = oCust.nMoveNext()           ' go to next record
if Not(cBool(oCust.nEOF)) then
		retval = oCust.nGetValue("CustomerName$", name)
		if UCase(strName) = UCase(name) then
				custExist = 1
		end if
end if

If custExist = 1 then

' Create a new header record using the next available order number
OrderNo = ""
r = o.nGetNextSalesOrderNo(OrderNo)
r = o.nSetKey(OrderNo)
r = o.nSetValue("ARDivisionNo$", "00")
r = o.nSetValue("CustomerNo$", "0179854")
r = o.nSetValue("CustomerPONo$", "BUSOBJTST")

' Create a new line for the item and write to memory file

r = o.oLines.nAddLine()
r = o.oLines.nSetValue("ItemCode$", strItem)
r = o.oLines.nSetValue("WareHouseCode$", "000")
r = o.oLines.nSetValue("QuantityOrdered", strQty)
r = o.oLines.nSetValue("UnitPrice", strPrice)
r = o.oLines.nWrite()


' Write header and lines to disk
r = o.nSetValue("FreightAmt", strFreight)
r = o.nGetValue("NonTaxableAmt", nontax)
r = o.nSetValue("FreightAmt", freight)
r = o.nSetValue("DepositAmt",nontax+freight)
r = o.nWrite()

strFreight = 0


end if

loop

 

Doing it this way creates and order per line read... there are orders with 2 o more lines, therefore i am creating 2 or 3 orders instead of 1 order with 2 or 3 lines...

 

Any suggestions?

 

Regard,

Manuel

Parents
  • manuel,

     

    You cannot instantiate the detail lines object by itself, you need to use the object handle within the header object.  The error message you are probably getting is that you have an invalid header, because you are trying to use the lines object standalone.

     

    Set oLines = o.oLines

     

    instead of how you are defining the lines object.

     

    Hope this helps

     

    Elliott

Reply
  • manuel,

     

    You cannot instantiate the detail lines object by itself, you need to use the object handle within the header object.  The error message you are probably getting is that you have an invalid header, because you are trying to use the lines object standalone.

     

    Set oLines = o.oLines

     

    instead of how you are defining the lines object.

     

    Hope this helps

     

    Elliott

Children
No Data