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

  • Hi Doug,

     

    Thanks for the info... my probem now is the follwuing:

     

    On each row you must check to see if the previous row is associated, if so, reuse the same order number.  If you plan to assign a sales order number from MAS 90, only do so when the previous row and the current row are not associated

     

    This is what i am not able to acomplis, how to tell when the current row is or not associated  with the previous row.

     

    Regards,

    Manuel

  • I finally got it to work... kind of... goes thru the files ok and i can see the correct items and s/o numbers but it is not writting data to the mas90 tables....

    here is my code..:

     

    '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 = oSS.sModuleDate
    'sdate = oSS.sSystemDate
    'ucode = oSS.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")
    
    msgbox "I am In"
    
    ' 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
    oSEC = oSS.nSetProgram(oSS.nLookupTask("AR_Customer_ui"))
    Set oCust = oScript.NewObject("AR_Customer_bus", oSS)
    
    'Open the CSV file
    
    dim fs,objTextFile,objTextFileLine
    set fs=CreateObject("Scripting.FileSystemObject")
    dim arrStr,arrStrLines
    set objTextFile = fs.OpenTextFile("C:\Akatz\Fitness First\orders.csv")
    set objTextFileLines = fs.OpenTextFile("C:\Akatz\Fitness First\orders.csv")
    
    msgbox "CSV Files are opened"
    
    strHdrOrderNo = ""
    
    Do while NOT objTextFile.AtEndOfStream
    arrStr = split(objTextFile.ReadLine,",")
    strHdrOrderNo = arrStr(0)
    if strHdrOrderNo <> prevOrder then
    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)
    
    
    'Read AR_Customer
    msgbox "Let's read the Customer"
    
    retval = 0
    name = ""
    custExist = 0
    OrderNo = ""
    prevOrder = ""
    CustAdd1 = ""
    CustCity = ""
    CustCustNo = ""
    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)
    		retval = oCust.nGetValue("CustomerNo$", CustCustNo)
    		retval = oCust.nGetValue("Address1$", CustAdd1)
    		retval = oCust.nGetValue("City$", CustCity)
    		if UCase(strName) = UCase(name) then
    				custExist = 1
    		end if
    end if
    
    msgbox "Customer Exists ?: " & custExist & " " & name
    
    If custExist = 1 then
    
    msgbox "Customer: " & CustCustNo & " " & name & "-" & strName & " " & CustAdd1 & " " & CustCity
    
    
    ' 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$", CustCustNo)
    r = o.nSetValue("Comment$", strHdrOrderNo)
    prevOrder = strHdrOrderNo
    
    ' Create a new line for the item and write to memory file
    
    Do while NOT objTextFileLines.AtEndOfStream
    arrStrLines = split(objTextFileLines.ReadLine,",")
    LinesOrderNo = arrStrLines (0)
    strItem = arrStrLines (3)
    strQty = arrStrLines (4)
    strPrice = arrStrLines (6)
    'strFreight = arrStrLines (8)
    strFreight = strFreight + arrStrLines (8)
    
    if LinesOrderNo = strHdrOrderNo then
    
    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()
    
    msgbox "Amazon Order Number is: " & LinesOrderNo & " " & strItem
    
    end if
    
    Loop
    
    
    msgbox "Finished Looping Amazon #: " & strHdrOrderNo
    
    ' 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()
    
    msgbox "Writting S/O Number: " & OrderNo
    
    strFreight = 0
    
    
    end if
    
    
    'Close CSV file
    objTextFileLines.Close
    set objTextFileLines = Nothing
    set fs = Nothing
    
    'Open the CSV file
    'dim fs,objTextFileLine
    set fs=CreateObject("Scripting.FileSystemObject")
    'dim arrStrLines
    set objTextFileLines = fs.OpenTextFile("C:\Akatz\Fitness First\orders.csv")
    
    
    end if
    
    loop
    
    objTextFile.Close
    set objTextFile = Nothing
    set fs = Nothing

     

     

    Any suggestons...?

     

    Regards,

    Manuel

  • 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

  • Hi,

     

    The write is not succesful because.. i was in fact missing fields...

     

    Now, i am getting the following message:

     

    The Column is not in the IOList

    Item: 4406 doesn't exist...

     

    The Item exist.  Do i need to trim the item numbers???

     

    Regards,

    Manuel

  • Yes you will need to strip the trailing spaces off the item code if there are any.

  • I am still gettng the same error...

     

    Regards,

    Manuel Roman

  • Coming late to the party.  Not sure if it is your problem, but you reference a variable "freight" a few times without defining it or its value.  You also set "FreightAmt" twice, the second time setting it to this "freight" variable that has no value.

     

    r = o.nSetValue("FreightAmt", freight)
    msgbox "Finished Looping Amazon #: " & strHdrOrderNo
    
    ' 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()
    
    msgbox "Writting S/O Number: " & OrderNo
    
    strFreight = 0

     

  • The line

     

    r = o.nSetValue("FreightAmt", freight)

     

    Shoulde be:

     

    r = o.nGetValue("FreightAmt", freight)

     

    I set the value then i get iback to use it on the last line:

     

    r = o.nSetValue("DepositAmt",nontax+freight)

     

    My problem is that i am geting an error reading the Item Number... i get the message that it doesn't exist and the item is in the CI_Item table....

     

     

    Regards,

    Manuel Roman

     

     

     

  • I know you've tried TRIM, but just for giggles can you throw up a msgbox with the itemcode value to see what it is actually trying to look up?  You've probably done that though.

     

    hrm.

  • Yes, I've done that... the item displays correctly.

     

    Regards,

     

    Manuel Roman