I have a single field (extracted from a pdf order) which alternates item numbers and quantities like this:
6-593
8.000
6-641
3.000
6-4212F
4.000
etc.
What is the easiest way to reformat and reimport the data so I can bring it into two fields, one named ItemNumber and the other named Quantity? I’ve tried to manipulate it with arraybuild but not succeeding.
This cannot be done with arraybuild or any single Panorama tool. To parse this will require a custom program using a loop. There is no “easy” way to do this without some programming skills.
As Jim suggests you will need to use a loop to accomplish your goal. I have some of your sample data in a single field A and use a looparray to move things to alternately fields B and C. Here is the code in the procedure:
looparray arraybuild(¶, "", "A"),¶,element,item
if item mod 2 = 1
B=element
else
C=element
downrecord
endif
endloop
Not at all elegant as what Gary wrote for you but coming from a different perspective, I saw this inquiry as a great opportunity to show how you can use Panorama’s almost simplistic English procedure code to accomplish a need. Sometimes the simpler is a great way to go. If you can read English, and understand the beginnings of coding, this will help you see answers in the future do not have to be complex to work wonders in Panorama.
(Do note that the below code works with all Text fields as it is not doing any error checking for pasting into what could be a numeric field.)
FirstRecord
Loop
Left
Until Info("Stopped")
Loop
DownRecord CopyCell
UpRecord Right PasteCell
Left DownRecord DownRecord
Until Info("Stopped")
Right
Select «» ≠ ""
RemoveUnselected
After you have copied and pasted this into your Procedure window, Step it through and see how it is just doing what you would do manually except that it is magically fast.
I am wondering how Barry extracted this information from a PDF file into the database in the first place. I suspect you could adapt Gary’s technique to reformat the data immediately, before it is even put into the database in the first place.
The pdf is a mess as might be expected. I copied the whole pdf and then used tagdata to isolate the portion of the pdf that included item numbers and quantities. Unfortunately a lot of crap still came along, so the next steps included text funnels and other bits of code to strip it all down to alternating lines of item numbers and quantities. I learned some new tricks along the way, too–thanks Jim for the recording feature!
I did try a loop somewhat like Robert’s but something is wrong in it as it keeps throwing errors. I’ll try his (which looks simpler) after the grandkids and puppy all fall asleep this evening.
Quite likely that more complex coding could strip out and correctly format the needed info before the import, but I know that’s more than I can handle. The sad part is that this is the result of one regular customer upgrading their software. Their old pdfs were easy to deal with. The new ones are much prettier to look at and a royal mess when turned into text.
When I start with the first record in the list below, the downrecord command jumps the selection from the first record to the 3rd to the 5th etc. Is it possible for a visible record to
not really be there?
This is a known bug that occurs when both the data sheet and a View-as-List window are open at the same time. Close one or the other to solve the problem.