Getting the Christmas Blues over this one!

[glenschild]

RE: Getting the Christmas Blues over this one!

Geoff:

Thanks for the advice.

To answer your question about why excel is being used is simply that I have created a process for an online fantasy sports competition which requires information from all entries received to be sent to me for inclusion in the competition.

I have not been involved in the "front end" of the web facility which receives the initial entries and payments (hence why I do not wish to get involved because of the complexities of receiving payments and all that involves!)

At present I do not have access to the online database so a system has been devised whereby after each entry is processed and payment accepted, an automated email is sent to a specified address. This email contains a "cut and paste" section which is then copied into excel as it easily placed into the correct rows and columns. That is as much as I want excel to do with the data apart from then saving the file in dbf format.

Yes this process does mean that someone has to cut and paste each email but that is significantly quicker than keying all data by hand and the errors that would produce.

Not an ideal scenario but until I can rely on getting my hands on the database itself in a timely fashion it is the best solution I have come up with....unless anyone knows better!??

Thanks for your interest

Glen

RE: Getting the Christmas Blues over this one!

Glen,

Why not try to use the PADL() function, where you can pad each number with leading zeros. As long as you have a final length in mind, the PADL() function will fill out each number to the predetermined length by putting the correct number of zeros in front of each regardless of the original length of the numerals themselves.

So if you, say, ran the function as : PADL(TRIM(corrected_uid), 10, "0") would produce the number strings you want. (In this case, each would be 10 characters long; i.e. 0000000001, 0000000002, 0000000102 and so on. If you want the whole string to be 5 characters in length, just change the 10 to a 5 in the ablove expression.

Hope this helps... But perhaps the most important question would be why are you using Excel, a program designed as a spreadsheet to crunch numbers, as a database in the first place?

I know from being in the data business for 20 years that many people MISuse it as such, and I have also learned that sooner or later, their data gets altered in such strange ways that it can't alwyas be recovered.

From truncating leading zeros in ZIP Codes, to sorting data in colums only, while leaving the rest of the rows intact; to truncating data when special care isn't taken to set the sheet to a fixed position font and then automatically re-sizing the columns, Excel is dangerous to your data. And, even though one can take steps to guard against this and may survive using Excel for this purpose unscathed, many have not... Be careful; it looks like a browse table, but there is no built in row (record) integrity... It's a spreadsheet in database clothing.

Regards,

Geoff Hollander

[glenschild]

RE: Getting the Christmas Blues over this one!

As always thanks for the prompt responses.

Charlie. I had thought of making the adjustment in the excel file and already have converted the cells to text format, however they contain the numbers without the preceding 000's. Also with the number of lines of data (several thousand) did not really want to start messing around with formulas in excel.

Vince, the replicate function works well with minimal modification (for the number conversion to chr) ended up with

replicate("0",5-LEN(allTRIM(STR(PLAYERCODE))))+alltrim(str(PLAYERCODE))

Thanks again to both of you and seasons greetings from England

Glen

RE: Getting the Christmas Blues over this one!

Oops,

Either I mistyped or the board ate some of the expression.

It should be:

Replicate("0",10-LEN(TRIM(CHARFIELD)))+CHARFIELD

Where the 10 is replaced by the length of your field.

RE: Getting the Christmas Blues over this one!

Glen,

I tried the stuff function once and didn't have any luck. To fill a character field with leading zeros use the PADL or the Replicate function. I personally use the Replicate function to do this all the time.

The example in the A5 book is a good one:

Replicate("0"-LEN(TRIM(CHARFIELD)))+CHARFIELD

[crimmelcp]

RE: Getting the Christmas Blues over this one!

Glen:
In Excell try putting a single quote in front of the zeros

'0001234
Excel will treat this as text.
You might have to defune your cell as text

Charlie Crimmel

[glenschild]

Hi all:

This should be a simple one but cannot figure it out.

I have a table called entries.dbf (created in excel) which includes several fields which need working on before they are usable in the database. Names split into component parts etc. All no problems except for a unique id field which excel treats as a number and needs converting to a character field with preceeding zeros (characters) ie: 00001, 00002, 00102 and so on.

I use a second table to append all lines from entries.dbf called corrected_entries.dbf in this table I have extra fields with field rules set up to do the conversions automatically. The field that hosts the corrected uid is called "corrected_uid"!!!

I have tried to use an "if" statement to establish the length of the uid field and incorporate the "stuff" function for the preceeding 000's but cannot get the syntax right.

Can anyone help.

Cheers

Glen Schild