Save table as fixed length file?

[buddyrich2]

Seems like a simple question, but for some reason can't find an answer here. Is it possible to save a simple Foxpro table as a fixed length data file? Is so, what are the steps to do so?

Thanks in advance for your time!

 

[craigber]

FoxPro tables are fixed length data files. But, data files can be many different formats. What specific format are you looking to use?

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Mike Lewis]

Do you mean a text file where each field occupies a fixed number of characters?

If so, try this:

COPY TO Somefile.txt TYPE SDF

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

"Is it possible to save a simple Foxpro table as a fixed length data file?"

As has been stated above already, you can Export a FP/VFP data table into a number of different formats. Most of these Export methodologies use the COPY TO utility which Mike has referenced above.

Since there are a lot of interpretations of the phrase fixed length data file we will leave it up to you to decide which of the COPY TO format options (look into your FP/VFP Help) best meets your needs.

If it turns out the none of the COPY TO format options meets your needs, then come back with a more clear explanation of what you need and we'll see how we can assist you.

Good Luck,
JRB-Bldr

 

[buddyrich2]

I think Mike's solution will work. Just to be clear, is the length of the field in VFP exactly the same length that will come out in the text file? The reason I ask is that I am sending the file somewhere else and they seem very particular about making sure it is a certain length.

Thanks!

 

[Mike Lewis]

is the length of the field in VFP exactly the same length that will come out in the text file?

Yes, that's exactly right.

If I remember right, memo fields don't get copied, and I think there might be an issue with the width of date fields. But, that apart, it should do what you want.

I suggest you experiment a bit with your actual data.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[buddyrich2]

Thanks! As usual, ya'll are champs!

 

[JRB-Bldr]

But, keep in mind...

"length of the field in VFP exactly the same length that will come out in the text file? The reason I ask is that I am sending the file somewhere else and they seem very particular about making sure it is a certain length."

Making the text file 'field' the same length as the data table field MAY NOT ensure that the length is the 'certain length' that the recipient is looking for.

If that were the case then you would possibly have to pre-process your 'raw' data and pad-out some of the field lengths to meet the recipient's requirements.

SELECT PADR(ALLTRIM(Fld1),20) AS Fld1,;
       PADR(ALLTRIM(Fld2),30) AS Fld2,;
         <etc.>;
       FROM RawData;
       INTO CURSOR GoodData READWRITE

SELECT GoodData
COPY TO Somefile.txt TYPE SDF

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

JRB-Bldr,

SDF has some conventions. Of course eg an integer field with 4 bytes is not exported with 4 digits in the sdf file, but always is exported as 11.

Besides this you're right though, that if a customer imports adf, it needs to fit exact, eg if you export a C(100) field and import into a C(101) field, this consumes one char of the next field, while it wouldn't matter in case of CSV.

What MS SQL Server does with the bulk copy utility, is to create a format file besides the flat text file. You can take that idea and create some kind of additional file for imports with field names and field lengths (legth as is within the sdf file) and field types, or offer an ANSI compliant create table with ANSI field types with the table as needed for import of the sdf. Some problems may remain like local different formatting of dates. It's recommended to export (and import) YYYYMMDD format (see the help on copy to in the SDF section).

Bye, Olaf.