Translation Fields Data Text Excel Copy Column

right on, thanks for taking the time to help with this! I'm glad to hear you are in a different "head space" and aren't really thinking about this kind of stuff. Sometimes we get too involved in one tiny thing, or get so into just doing one kind of thing, that we neglect the Whole Big World out there. Balance is key, as I always say!

Yup, but I don't mind getting my head in that stuff, it would just take me a while to refresh my memory. Quite possibly more time than for you to figure it out yourself etc.

I wish I could go through a space of time not thinking about work or computers and stuff. For now I am the only guy that can do the weekly stuff for Madison's, I'm hoping by this time next year to have taken on a partner (a smaller share partner of course) so I can get more than these weeny four day breaks. Am off to Whistler, actually, Friday with Marie to stay at Janice Esau's cabin (oh, gotta call her) until Tuesday so that will help a little.

Cool, expand and farm out work, and become more free in the process. Been doing that for decades, on and off, and its fun stuff!

You're totally right about taking it step by step, moving forward logically. I realized this morning, or it could have been last night, that I forgot to do one extremely important thing at the beginning stages. Its so important, no wonder it didn't work!, that I'm not going to tell you for fear of you thinking I'm vaguely . . . dumb. haha! but this is all very new to me.

I can imagine, but it's to be expected and I do the same stuff all the time, smacking myself in the forehead with my palm once I figure it out. That's what I meant about the "getting your head into it". Takes time, and best approach is to go carefully, not rush, possibly make notes somewhere to remind yourself later etc. I'm pretty sure that, when I did have my head in it, I wrote all the instructions quite meticulously, so if you meticulously follow them and carefully consider everything you are doing, it should work.

Anyway, feeling pretty good about my foray yesterday. Now that I realized what I missed I think I can work in the new version of sqlite admin, which seems like it can do way more than I need. so, all I gotta do is add those two new fields at the end. at the bottom of the column. Its already there in Access, and - as far as I got with importing the table - everything fell in the right place so I don't see why the new field info won't as well. Then I'm going to upload it and hope I don't destroy the universe. I already have the current copy of the mills.db on my computer, and on my backup drive actually, in case I find some issue.

Don't forget to also change the name of the online file, which makes it a good backup. Ie- change file.db to file_old.db or file.db_old, for example. Then if you screw up you can just erase the new file (or name to file_new.db) and change the old file back to normal. Online is probably the best backup you can have.

If possible, if you are interested in testing it, that would be great. You have noticed finicky things before that needed fixing that I missed. It won't be for a couple of weeks so you might be back in computer mode by then.

Will try to get mobile internet into the mountains of bg once I get back. We'll see.

After I am sure and confident etc. I want to go about adding the Transporters and other sections (which you already made room for in Access), then once THAT is done I want to put the US mills on. That will probably be at the beginning of next year.

Sounds exciting. When I get back to bg I will try to complete the babyfication etc. Roger has all sorts of little computer jobs for me and I am trying to appease him cause I want to make sure he's happy with me so that I can stay on this gig for a couple of years. Once that has been reasonably secured, I want to shift more to my own projects again.

So blah blah blah enough about work you still haven't told me how Jens' wedding and festivities were. How did your speech go? Did you organize a bachelor party for him? How's your scabby head?

Enjoy picking away at it and almost gone. Wedding was great and very luxurious, but I guess I drank a lot and also froze a bit in front of those 50 people, so it took me a while to remember where to start, and I only remembered about half the speech. But everyone said I was very cute and it looked like I pretended I forgot. Will upload some pics later. Jens was too busy for any bachelor party.

Now I'm off to Amsterdam, and then Croatia for about a week. Friend said he'd pay for my gas a food to Amst and back, and when I was a Natasha's they were so impressed with my cooking they said I'm welcome in Croatia if I cook for them. They have a yacht there etc. and seems like nice additions to my little planned trip. Lovin the randomness of it all! latero

I told mum, but only after you wrote about it a week later, not when I first heard, because I don't need her FREAKING OUT, as she always does, "OH MY GOD IS HE OKKKKK???" total over-react-a-tron. So yeah, if she asks you about it that's why. Ok by for now lalalala

--------------------------------

okay, I think I figured it out. If I am not mistaken, I think the last thing you were working on was Processing1.xls and your job was to use a shortcut macro to turn some fields into "Other", shifting a row to the right. Am I correct? I believe the problem was that there were too many fields for an Excel file and we had to cut down the number a bit by not babyfying some fields into Yes/No but rather adding them to an "Other" field.

Attached is what I processed.

But I see some problems.

First of all, looking at the file, the second row shows: Dimensions<tab>1

while on about row ten we have:

dimension<tab>"(rough & dressed,2x3,2x4,2x6,2x8,2x10)"

How is it possible that the dimension field for a particular mill can have the response "yes" (1 will be converted to "yes", otherwise "no") while for other mills it has more detailed data like in line 10? I need to understand this correctly before proceeding.

Lines 3 to 8 or something have:

other dowels

other S4S turnings

other handrails

other laminating

other cabinet & furniture components

but the script wont work properly, since the same-named field (other) repeats several times for the same line (always started by "Mill name fart").

This instead needs to be converted to the format:

other dowels, S4S turnings, handrails, laminating, cabinet & furniture components

which I accomplished by selecting from "dowels" until the end of "components", then S&R (ctrl h) the following:

^pother^t

with

where ^p = paragraph mark and ^t = tab.

If you think about it a bit you should understand the concept.

There cannot be several fields of the exact same name within a row. Therefore, what you basically have to do is the following:

- whenever you see "Mill name fart" you know that a new row is starting, and will end the next time this text appears again

- scan through that section and make sure there is no duplication of the same named field (such as "other"). If there is a duplication it all has to be compiled together as I showed such that all the field contents (in this case " dowels, S4S turnings, handrails, laminating, cabinet & furniture components ") are together under only ONE instance of that field name.

If you end up seeing something like:

other dowels

other S4S turnings

other handrails

dimensions 1

other laminating

other cabinet & furniture components

you will have to fumble around more, possibly clicking with your left mouse in the far left of the row (which selects the entire row in PC Word) to move it (ctrl x) to the end of the section and get something like:

other dowels

other S4S turnings

other handrails

other laminating

other cabinet & furniture components

dimensions 1

Once you accomplish that and get all the "other" fields under one another, you can conglomerate them into one according to the instructions I stated above.

- beyond this you could scan through and contemplate or explain to me what is the meaning of "dimensions = yes", for example, or any other quirks.

As I explained a million years ago, there is a way in Word how you can see the <tabs>, if you need. I can explain again if you want. Same for the ^p / paragraph marks. Make sure you do not replace any tabs with something else like a space because that will screw up everything. The tab will be considered as a field delimiter (separator) by the script, so it must not be removed.

As a reminder, keep in mind that this applied only BETWEEN the Mill Name sections.

For example, you would not want to commit something like the following – converting:

other dowels

Mill name fart

other S4S turnings

other dowels

other S4S turnings

Mill name fart

and eventually conglomerate the "other" fields. These particular ones should be in different rows… I figure you should understand what I'm trying to say if you think about it a bit.

- in one row I noticed:

other "16"" Fivex shingles,18"" perfections"

Perhaps you might reflect on cases like this and check if there was a mistake somewhere. Or it looks like the double quotation marks are supposed to represent inches? In this case it would probably be good to do a global search and replace (scan or ctrl f through the document first to make sure my hunch is correct), replacing double quotation marks with something unique like iinchess. At the end this could be s&red back to single quotation marks, for example, but the scripts I'm using pay particular attention to quotation marks and I think it would be better to make this adjustment now, in case it screws something up.

Okay, went through the file a bit more and I cannot see any other issues.

Should be reasonably straight forward but looks like another migraine of tedious work. I can see that this babyfication will take longer than I originally expected, but creating such a babyfication from the original pdf format is quite a feat. Will practically have to write a thesis about it for the demo site once we're done with all this.

Make sure you read all this carefully and understand the concept fully before jumping into this task, so you do not screw anything up. It should be pretty straight forward once you understand it.

Good luck! k

--------------------------------

found a beer in bro's fridge, he gave me his keys and said no rush, truck still not fixed yet, so while procrastinating here thought I'd take a look at the file, processed it a bit, and got the following (first part of file):

Mill name fart

dimension 1

mouldings 1

dowels 1

S4S turnings 1

handrails 1

laminating 1

cabinet & furniture components 1

Mill name fart

boards 1

dimension (rough & dressed^2x3^2x4^2x6^2x8^2x10)

export blanks 1

Mill name fart

fencing 1

fingerjoint 1

J-grade 1

pulp chips 1

railroad ties 1

studs (2x3^2x4^2x6^72iinchess^84iinchess^96iinchess+)

timbers" 1

Mill name fart

KD dimension 1

studs PET 1

MSR 1

Mill name fart

fingerjointed studs (2x4^8ft to 10ft)

That indicates to me that this is DEFINITELY an older version because the Other was not added. The last job you did as I remember was adding the Other cause there was simply too many fields, so I believe my hunch was correct and now your job will be too conglomerate the Other etc. as I instructed in my last email. I do not remember ever asking you to do this before. Do you remember doing something like that? Anyway, that's what needs to be done in order for me to proceed, unless you find the file where you did that, but I don't think you did. These steps will probably need to be duplicated for all the field babyfications, which includes Studs, for example, once Products is babyfied (Studs would be a further babyfication and subsequent step).

Lots of poop to do, boohoo, but babyfication is what you requested!

Since I assume you'll be busy doing this for a bit, if the house I'm at in Croatia has internet I'll try to figure out what's up with the glitch. Got any more search examples for me (special characters which cause the thing to go bonkers)? One hunch that just occurred to me is that it may be due to the codepage setting. Maybe I'll try unicode or something. Just a total hunch. latero!

Hey, OK, here's the Product babyfication1.doc file. I think you will see its all good except the quotes ". The processing1 file has a sheet for Species babyfication which does not look like it has been done. I just need instructions on how to handle the percentages: same as Products where there are no spaces except in front of 1, and it either has a 1 or a percentage? lalalala

> > In the Processing1.xls file I sent you a long time ago, I gather

> you did the same

> > procedure for the Species field (as with the Products field/file,

> which you are

> > working on now)? The same procedure will have to be done with each

> babyfied

> > field and I'll send those to you as you complete others (like the

> Products one

> > now). k

--------------------------------

That's such a juicy response from you that I wanted to answer quickly and combined mobile to computer. Read it quickly once and glad you seem to be getting onto the logical ball. Good as we are fine tuning and coming to the home stretch. Should be a real juicy database and a good advertisement for me (the demo version I will set up once complete).

Now to answer your questions.

Wanted to take a look at Species first before letting you take it, to see what stage it was in.

Okay, now you're going to get fancy shmancy and become an ace programmer.

Create a macro script in the Excel file and assign it a shortcut key, such as ctrl o (as in "other"). Place the cursor at the first column of some row. Go to Tools > Create Macro or something like that, make sure the Relative button is depressed on the little popup toolbar (not Absolute, which should be the automatic default), and now start recording. You've got to copy the text of the first column, then put the cursor at the start of the next column, paste the text, perhaps add : space, go back to the first column and type in Other. Perhaps throw in there a comma space if separating several Others. Screw around with this until you are amazed at your genius.

Now, let's consider the merging of data from this Excel to the .db. The sorting of the companies is the same as before you added or deleted new records (mills – do you have a copy of it before you added records? Perhaps I do). You basically have to copy the data from Excel into the correct rows and columns (will be created), and make sure the mills line up properly. I only told you not to make any changes to the Products etc., since we were working on them. In that case you can feel free to screw around with everything else. It's just the sorting and lined-up thing we have to worry about when copying the Excel data into the Access dbase.

Perhaps since you are up to date on what you updated, you should perform this copy/paste yourself – very carefully, make sure to have a backup, and with my consultation.

Concerning the glitches you mentioned in the Products Done tab:

- if you are finding the occasional typo of commonly repeated fields, I would suggest you run an s&r to standardize the spellings according to your preference. Go back to the Word file and fix it up – then I simply run it through my script and send it back for your perusal in the Excel spitout.

Otherwise, if you want to try a manual copy paste of certain sections to other columns, that is totally your call. You know what the final outcome has to be – just be careful not to overcopy some records while pasting over large sections. Perhaps you could ctrl down arrow to confirm that there are no stray records within a section you plan to copy over.

- concerning the sections that repeat, I know it's a glitch in the script and I've been fixing these sections manually. You should always be able to compare against the MSA data when double checking for any script errors.

You are starting to understand how the babyfication works, so feel free to tamper with things, because now you are starting to understand the outcome. It is your database and doing these tweaks is part of its management. The more involved you are in the creation process the better you will be able to manage it by yourself in the future.

With the repeated glitch, perhaps we could try to figure out why the script is doing that.

For AJ 1584, one alternative is you have a 1 for Baby Squares, then add a comment field next to it. We would create a new field and call it Baby Squares – Comments, for example.

So by this you can understand the babyfication process. It's like branches of a tree, breaking up into smaller and smaller networks of connected data. You proceed as intricately as you like – just depends on how much data you can squeeze into a page. Or you can branch out to other pages, clicking off a particular link or piece of information. You can do this yourself simply by creating a new column in the Excel file and naming it appropriately. This would then become visible as check boxes (conversion from one – where blank could be converted to 0, which means "no" or unchecked). Next to certain check boxes you can squeeze a comment field (you don't have to put a label "comment" and it can be just blank if the record is empty).

So, for this babyfication, I would suggest you grab the bull by the horns and make it as sophisticated as you like. You could even try adding those fields as you like on the webpages using your Dreamweaver. Perhaps you will not like my choice where to put the additional fields etc.

Okay, it's nice to be on the same page and working towards the end, and seeing how you are taking things under control. Like I wrote above, the more you get your head into this and do this stuff, the more you will understand it, and be able to expand it according to imagination, creativity and understanding. I think it's quite good we are developing it this way. Most programming companies would probably have you by the balls and charge you the shit out of you to do it instead.

Good luck!

--------------------------------

get my last fantastic response? Finally at internet again.

Hey Booble, Woohoo! Almost done yay. Will do species today, I asked you before if you wanted me to do it all at once and you said to just get the products done first. The question about Species was how to handle the percentages: if it is Fir 80% Spruce 20% should I make it Other Fir/Spruce 80/20% or is that too confusing? Don't want to lose info, I mean don't want to erase percentages for all the Other fields. What if its Balsam 10% Aspen 5% Chickory 3% Black Oak 4% . . . know what I mean? It will look like Other Balsam/Aspen/Chickory/Black Oak 10/5/3/4% looks weird. What can be dooooone??

> >> okay, processed the file and attaching it. Tried to run the scrip

> >> on the Species-

> >> Keta Working On but ran into same problem. Have you processed

> this

> >> data yet? If

> >> not it needs to be Othered (ctrl e is the shortcut in the Excel

> >> file). There are

> >> simply too many fields so the rare ones need to be Othered and

> >> then the Others

> >> merged as you last did (copy the data into a Word file, without

> >> formatting).

> >> Check the Products Done tab and see if its okay, and then tell me

> >> which columns

> >> you want further babyfied. We'll s&r the quotations back once we

> >> are completely

> >> done. Feeling like a comp geek considering the lingo we've

> >> developed? If

> >> someone hacked into our email box think they'd have absolutely no

> >> clue what

> >> we're talking about. Getting close to the end and exciting huh?