FoxPro Help and Support - Import non-delimited text file?

Author

Import non-delimited text file?

Barley Man

2005-05-06, 11:24 am

Yet ANOTHER text file query. Now the client has supplied me with a text field
of 200,000 records, I have the record layout and I can 'view' the data but
there are no delimiters at all between fields. I am using VFP 7 (while
awaiting my copy of VFP 9) Are there any simple methods to import data from
such a text file with no delimiters into a table in VFP 7

The record length is long but here is one complete record:-

7465271 0 7.90P .00 0 09 7.90 1 50427
..00 .00 0 0 0 0 0 .00 0 0 0 0
.00 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0

I suspect I could use the AT() command to insert such a delimiter but I
would have a problem with the two fields (7.92P) as I can't replace anything
between "7.92" and "P"as there isn't anything TO be replaced.

Ian

Olaf Doschke

2005-05-06, 1:24 pm

> there are no delimiters at all between fields.
Are you sure? Maybe the file ist TAB-Delimited!
Find out, if you have chr(9) in between fields.

Try an import with Excel, that will find such things
quite intelligently, although it can't handle that much
rows.

Bye, Olaf.

Olaf Doschke

2005-05-06, 1:24 pm

And if there even is no TAB delimiter, the fields should be fixed length
within that text file, that would be an SDF file, might have the extension
TXT. You could import from such a file APPEND FROM ... TYPE SDF.
Your cursor must have fields with the correct lengths of all the fields in
the SDF file.

Bye, Olaf.

Barley Man

2005-05-06, 8:25 pm

Thank you, Olaf.

I'll try the SDF approach.

Excel is eliminated as there are over 200,000 records and Excel limits out
at 65,000.

Ian

"Olaf Doschke" wrote:

> Are you sure? Maybe the file ist TAB-Delimited!
> Find out, if you have chr(9) in between fields.
>
> Try an import with Excel, that will find such things
> quite intelligently, although it can't handle that much
> rows.
>
> Bye, Olaf.
>
>
>

Barley Man

2005-05-06, 8:25 pm

Thanks Olaf!

I'd forgotten all about SDF. It works perfectly! That's GREAT news!

Ian

"Olaf Doschke" wrote:

> And if there even is no TAB delimiter, the fields should be fixed length
> within that text file, that would be an SDF file, might have the extension
> TXT. You could import from such a file APPEND FROM ... TYPE SDF.
> Your cursor must have fields with the correct lengths of all the fields in
> the SDF file.
>
> Bye, Olaf.
>
>
>

Villi Bernaroli

2005-05-09, 3:24 am

Mi e' parso che Barley Man abbia scritto:

> Thanks Olaf!
>
> I'd forgotten all about SDF. It works perfectly! That's
> GREAT news!

And if you don't want to manually create a table with all the
fields of the exact length, use an ODBC Driver for text files.
Then you can connect to the source, issue a command as
SQLEXEC("select * from "+TABLE_NAME)
and then issue "copy to "+TABLE_NAME+".dbf" to create a dbf on
disk.
This way is much more useful on big quantities of files, where
you would have to manually create every table before the append
from, so it makes you really save time. I use a little prg like
this. Since I often have to import data from various file
formats, the first thing I do is to try to abtain then in a ODBC
data source, since after that I only need to run this prg and all
the files become DBFs.
[warning: this is a version I modified for this post, since the
original version I use is in italian and is environment
specific].
hndl=SQLCONNECT(Data
SourceName)
IF hndl>0
=SQLTABLES(hndl, "TABLE", "dbResult")
SELECT dbResult
IF reccount()>0
copied_tab=0
empty_tab=0
SCAN
SQL_Statement = "select * from "+allt(TABLE_NAME)
NOME_DBF = allt(TABLE_NAME)+".dbf"
=sqlexec(hndl,SQL_St
atement,"cTabella")
if reccount()>0
copied_tab=copied_ta
b+1
copy to (NOME_DBF)
else
empty_tab=empty_tab+
1
endif
use in cTabella
ENDSCAN
wait wind "Extraction OK, copied " +allt(str(curr_tab))
+"
tables."chr(13)+chr(10)+ ;
"There were also " +allt(str(empty_tab)
)+" empty tables."
ELSE
wait wind "!!! - Data Source is empty"
ENDIF
ELSE
wait wind "!!! - Unable to connect to Data Source"
ENDIF
--
The answer to the ethernal question is:
put a read events after the launch of the form

Olaf Doschke

2005-05-09, 3:24 am

I have experimented with Copy To ... Type SDF
and ODBC (a DSN for *.txt,*.csv files) using
remote views to the created sdf files.

Foxpro only copies records to the file, no
first line containing the field structure.

ODBC seems to analyze the first line as field
names, although it does weird mappings.
As I exported a record containing two c(10)
field with values "hello" and "word", ODBC
made a view out of that with one memo field
named hello_____world. Weird.

I wonder how that first line must look like to
get correct field count and lengths.

Bye, Olaf.

Villi Bernaroli

2005-05-12, 3:25 am

Mi e' parso che Olaf Doschke abbia scritto:

> ODBC seems to analyze the first line as field
> names, although it does weird mappings.
> As I exported a record containing two c(10)
> field with values "hello" and "word", ODBC
> made a view out of that with one memo field
> named hello_____world. Weird.
>
> I wonder how that first line must look like to
> get correct field count and lengths.

I don't know, henostly, but also have a pair of utilities who
change memo fields to character fields if the longest datum
contained is shorter than 255 bytes: just issue

calc max(len(allt(FIELDNA
ME))) to new_fld_len

and use it as the length of the new character field, so you ADD
the char field, replace it with the memo contents, ALTER the memo
column to make it a C(new_fld_len) (this blanks the field losing
all data) then replace the altered field with the values in the
temp char field, then DROP the temp char field.
This way the memo fields remain in theyr position.
I also use some utilities who check if there are fields allowing
null values and ALTER the columns so that they do not accept
null.
This is a bunch of small very useful routines I used on many data
conversions, and which saved me from having to manually write my
"CREATE TABLE ..." statements.
--
The answer to the ethernal question is:
put a read events after the launch of the form

Sponsored Links