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
  • hi manuel,

     

    Check to see if your write is successful.  If r = 0 then display o.sLastErrorMsg.  This should be one of your first steps in debuggging any problems where the data is not getting into the Sgae 100 system.  If your write is not success (1) then check the error message.

     

    My guess is that it'll tell you, you are missing required fields or something.

     

    Thanks

    Elliott

Reply
  • hi manuel,

     

    Check to see if your write is successful.  If r = 0 then display o.sLastErrorMsg.  This should be one of your first steps in debuggging any problems where the data is not getting into the Sgae 100 system.  If your write is not success (1) then check the error message.

     

    My guess is that it'll tell you, you are missing required fields or something.

     

    Thanks

    Elliott

Children
No Data