Search code examples
sqlvisual-foxprofoxpro

converting input fields from text to columns in FoxPro or SQL

I have a set of input data in FoxPro. One of the fields, the grp field, is a concatenated string, the

individual portions of which are delimited by the pipe symbol, "|". Here are some examples of the values it can take:

grp:

ddd|1999|O|%

bce|%

aaa|2009|GON|Fixed|big|MAE|1

bbb|PAL|N|Fixed|MAE|1

aaa|SMK|O|Fixed|MAE|1|1

ddd|ERT|O|%

eef|%|N|%

afd|2000|O|%

afd|200907|O|%

swq|%|O|%

%

I would like to write a query that will separate the data above into separate fields and output them to another sql table, where the deciding factor for the separation is the pipe symbol. Taking the first two rows as an example, the output should read

Record 1:

Field1 = ddd Field2 = 1999 Field3 = O Field4 = %

Record 2: Field1 = bce Field2 = % Field3 holds no value Field4 holds no value

It will not be known in advance what the greatest number of pipe symbols in the data will be. In the example above, it is 6, in records 3 and 5.

Is it actually possible to do this?

Solution

  • You can create a cursor and append the data into it using 'append from' (another way would be to use 2 alines, one for rows other for columns data). For example using your data as a text variable:

    Local lcSample, lcTemp, lnFields, ix
TEXT to m.lcSample noshow
ddd|1999|O|%
bce|%
aaa|2009|GON|Fixed|big|MAE|1
bbb|PAL|N|Fixed|MAE|1
aaa|SMK|O|Fixed|MAE|1|1
ddd|ERT|O|%
eef|%|N|%
afd|2000|O|%
afd|200907|O|%
swq|%|O|%
%
ENDTEXT

lnFields = 0
Local Array laText[1]
For ix=1 To Alines(laText, m.lcSample)
    m.lnFields = Max(m.lnFields, Occurs('|', m.laText[m.ix]))
Endfor

#Define MAXCHARS 20 && max field width expected

Local Array laField[m.lnFields,4]
For ix = 1 To m.lnFields
    m.laField[m.ix,1] = 'F' + Ltrim(Str(m.ix))
    m.laField[m.ix,2] = 'C'
    m.laField[m.ix,3] = MAXCHARS
    m.laField[m.ix,4] = 0
Endfor

lcTemp = Forcepath(Sys(2015)+'.txt', Sys(2023))
Strtofile(m.lcSample, m.lcTemp)

Create Cursor myData  From Array laField
Append From (m.lcTemp) Delimited With "" With Character "|"
Erase (m.lcTemp)
Browse

    However, in real world, this doesn't sound to be very realistic. You should know something about the data ahead.

    And also, you could use FoxyClasses' import facilities to get the data. It lets you to choose the delimiters, map the columns etc. but requires some developer intervening for the final processing of the data.