Data's there but won't export

[Acquiesce]

Hi,

Not sure if this is the correct forum to post this in but here goes anyway!

I recently received the task of some data integration from an ancient version of TAS. I eventually managed to produce a DBF file from the application but when i opened it up in Excel it appeared that some of the data was missing ie.

Field1 Field2 Field3
D 0 sign Studio
S 0 ellite Lighting

it appears that field2 replaces 1 or 2 letters from each row

Now here's the FoxPro relevant bit, when i open the same dbf in foxpro the missing letters are displayed correctly, however when i attempt to export it to any format they again get replaced with zero's

Anyone have any ideas? Any help here is much appreciated, if i'm in the wrong forum I apologise

Many thanks

Waylon

 

[EzLogic]

When you open it in Foxpro. What is the structure of the table like?



Ali Koumaiha
TeknoSoft Inc
Farmington Hills, Michigan

 

[Acquiesce]

Hi Ali,

I'm not 100% sure what you mean, if you're asking what are the data types of the fields then its as follows: -

Field1 - Character
Field2 - Number
Field3 - Character

Now I know that appears to explain it but when I browse the data in FoxPro Field2 displays 1 or 2 characters until I place the cursor in its cell and then it becomes a 0, i've tried changing the field data type to character but then they all become 0's. I've tried adding a new character field and copying the data from field2 to this new field but again they all came across as 0's

I really don't understand why its doing this, its so frustrating, I can SEE the data its on the screen in front of me but i can't do anything with it because as soon as I do it changes to 0

many thanks

Waylon

 

[EzLogic]

If I understand you correctly, you are saying that Field2 is NUMERIC but, when you browse you see 'Characters?' but, when you go to that field, it changes it to Numeric value of 0 (since the value of a character is 0)?

try this in the command window:

?ltrim(str(field2))
and see what you get!

Ali Koumaiha
TeknoSoft Inc
Farmington Hills, Michigan

 

[Acquiesce]

Hi Ali,

That command returned 0

I have taken a couple of screenshots and uploaded them:-

http://www.cw003b3710.pwp.blueyonder.co.uk/browsedata.gif

http://www.cw003b3710.pwp.blueyonder.co.uk/tabledesigner.gif

hopefully you'll see what I mean from them

thanks

Waylon

 

[wgcs]

I think, Clearly, that the data in the DBF is screwed up... basically, the value (such as "Design Studio&quot is being put in the first field, but spanning across several fields, since the first (character) field is described in the header as being shorter than the data that's being stored in it.

However, since the second, and third, etc. records Still line up properly, the record size is calculated correctly, which would make me think that the Third field is Too Big by the same amount that the First field is Too Small.

You Can't fix this problem easily in VFP. You could use either VFP low level file funcitons, or a Hex-based File editor to actually examine the DBF Header and adjust the field widths.


A Better Approach, however, I believe would be to go back to the orignal TAS program and Export the data in a more manipulatable format, such as Plain text. VFP is Expert at manipulating text files, and can easily APPEND FROM a text file of type SDF (fixed-width fields), CSV (comma separated values) or DELIMITED fields with just about any delimiter.

I think that the Export to DBF function in TAS must be broken.

 

[MomTheNerd]

Can you repost this screen shot

http://www.cw003b3710.pwp.blueyonder.co.uk/browsedata.gif

after selecting a cell in the 2nd column. The black boxes might be tab characters, or something else that cannot be displayed in the default font.

You could copy the contents of the cell and paste it into a tiny file that could be analyzed by a hex editor-type program. Fox also has functions that can give you the hex code for a single character.

Laura

 

[baltman]

What other export options does the program have? Sometimes the quickest route is not the direct one.

Brian

 

[Acquiesce]

Hi all

Thank you all for the responses so far

unfortunately the TAS application that the data has been taken from stores its information in a proprietory, unreadable file structure and the only export option available is to the DBF you see here :-(

If anyone has any further ideas it would be great to hear them, failing that its a case of a lot of copying and pasting!

Many thanks

Waylon

 

[baltman]

OK... what exactly is TAS? Google seems to bring up multiple types of software as hits... Who made it, what version is it, and might there be an ODBC driver for it?

Brian

 

[Acquiesce]

Hi Brian,

Its an accounting package TAS Professional v3.x (they always have stupid data storage!) that runs in DOS.

Nice idea but i doubt there'll be an ODBC driver for it

Waylon

 

[dbMark]

While it might be that the field sizes are improperly reported in the table header, another possibility is that if invalid characters are present in a DBF field, you will see the behavior you described while in BROWSE. There might be some NULs there (Hex 00) or other values less than 32 (Hex 20), though in my experience NULs are the worst on display issues. As mentioned above you could see exactly what's in there by using the low level functions FOPEN(), FREAD() and FCLOSE(). If you examine enough records in there you could see the TAS program's writing pattern and write code to recopy it into a proper DBF table. Hopefully there arn't too many fields...