Million dollar question - bulk field listing + character width change 1

[ziut3k]

Hi,

To begin with, I'm not an expert obviously but been using VFP for a while now.
At work, we have a various databased/tables coming to us. I need basically to write a simple program which would:

a) List all field names in a table (that's an easy part)
b) After listing these it would change very single field's character width to a certain digit (let's say 60)
b1) It would be helpful if the program could also change all of these types to 'character' as some of them would be set as 'numeric'

I've figured out to to list/display all field names as below:

gnFieldcount = AFIELDS(gaMyArray)
CLEAR
FOR nCount = 1 TO gnFieldcount
? gaMyArray(nCount,1)

ENDFOR

...but that's about it. After many attempt I cannot figure out how to store these names (cursor?) and tell VFP to change each of these character width to a given number.


Many thanks in advance for any advice!

 

[Olaf Doschke]

the fast advice:

ALTER TABLE is there to change field types, you can indeed turn numeric into char and get the numbers preserved, but that's not a given for any type.
The simplest change to all character fields would perhaps be to simply use
USE table
COPY TO some.txt TYPE CSV

Now by nature of the CSV format you have all data in a text file, which can be read back into a table with only char fields via APPEND FROM some.txt, if that is more helpful to you. The question arises why? It's preferable to have data in the field types they are to work with them, eg you can't sum char fields.

Bye, Olaf.

 

[ziut3k]

Thanks for your advice Olaf.
..but It's exactly opposite. We have a sortation program, which exports files to CSV only.
Once you save a CSV file as a .dbf it's changing all field widths to whatever it wants (i.e.e maximum length of characters in that field)
The problem is, I need to shuffle up all fields and in order to do so, I need to change all field widths to the same value.

I've been doing that by hand for the last 6 years so I'm a bit fed up with manual work...

Quick example:

Name - field width 25
address1 - field width 40
address2 - field width 50
postcode - field width 10

I need to change all of the above field width 50. That's all.

I'll appreciate any further help, thanks.

 

[Olaf Doschke]

As you work with AFIELDS, there is another fine thing you can do with that array, you can create a secondary cursro by CREATE CURSOR new FROM ARRAY gaMyArray, and before you do that can change the array columns 2 to "C" and array column 3 to 60 to let that generate a cursor with all char(60) fields and then append old to new cursor.

* placeholder for your dbf
Create Cursor curTest (iint I, nNumber N(10), cText C(30))
Append Blank

* the conversion process, not converting the old cursor, but creating a new one
For lnCol = 1 To AFields(laFields)
    laFields[lnCol,2]= "C"
    laFields[lnCol,3]= "60"
EndFor lnCol 

Create Cursor curNew FROM ARRAY laFields
* insert all data and let it auto convert, as said this may not convert all types as you would want,
* but surely works with char or numeric fields.
Append From Dbf("curTest")
Browse

As a side note, this makes use of AFIELDS() both creating the array, which is iterated and changed to let all fields be C(60), and that AFIELDS returns the number of rows the array has. This is only executed once, not for each iteration, which is VFP specific for the upper limit of FOR loops, eg this does not lead to an endless loop:

lnCount = 2
For lnCount = 1 To lnCount+1
   ? lnCount
Endfor

Bye, Olaf.

 

[Olaf Doschke]

It rather should be laFields[lnCol,3] = 60, but astonishingly also worked with laFields[lnCol,3] = "60".

Bye, Olaf.

 

[Mike Lewis]

Hi Ziut3k, and welcome to the forum.

Something like the following might work:

USE TheTable
COPY STRUCTURE EXTENDED TO Temp
UPDATE TEMP SET FIELD_LEN = 60 WHERE FIELD_TYPE = "C"
CREATE NewTable FROM Temp
SELECT MewTable
APPEND FROM TheTable

I haven't tested this, but it should point you in the right direction.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ziut3k]

Thanks Olaf.
I've copied your program, run it on one of our databases and all I've got is a new table called 'curnew' with three fields iint, nnumber, ctext

What am I doing something wrong here.... ?

 

[Olaf Doschke]

This was just a sample, you can take the code from the line starting the for loop on any table you currently have open, it always creates a cursor, yes. You only need a temporary cursor to export to CSV, you don't need to change the DBFs.

Bye, Olaf.

 

[ziut3k]

Mike! Thanks a million!

I've tweaked it a tiny bit I had to add 'CLOSE all' after UPDATE TEMP SET FIELD_LEN = 60 WHERE FIELD_TYPE = "C"
I'll now change it slightly to make suit our needs. Cannot image it would be so simple!

Thanks and greetings from the London! ;-)

Kind Regards,

 

[Mike Lewis]

No need to thank me, Ziut3k. Just send me the million dollars and we'll call it quits.

Seriously though ... You'll find in Visual FoxPro there are usually several ways to solve a given problem. My first thought was along the same lines as Olaf's - especially the use of AFIELDS(). But in this case, COPY STRUCTURE EXTENDED seems simpler.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

>in this case, COPY STRUCTURE EXTENDED seems simpler

True indeed, an UPDATE-SQL of the structure data is more elegant than any loop, no matter with which bells and whistles you shorten the loop code.

Bye, Olaf.