Predetermined or predefined TAB length

[keepingbusy]

We are trying to convert an Access table to a VFP9 application.

We have exported the Access table to a CSV (with , delimiter) file which then allowed us to import it and create a table structure via VFP.

So now we have the table structure in a VFP9 table that matches the Access table structure, the "Raw" data we expect to import into the new VFP table will be a TAB delimited type txt file or may have a csv extension (this is not an issue as we can rename it etc)

When we try and import a txt file into the VFP9 table that would normally be imported into the Access table, only the first field is populated.

The code to import we are using is:

Use OurTable
APPEND FROM testfile.txt DELIMITED WITH CHAR TAB

My question is, is there a different way to achieve this or can I use some other code that will allow me to predetermine or predefine the actual length of the fields that are already known in the txt file to match that of the table field lengths in the VFP table?

If I can post any additional information that would assist, please let me know.

Thank you

Lee

 

[brigmar]

That looks to me to be the correct command for what you have described.

Check the text file in a text editor; some process/app may have replaced the tabs with spaces.

If you want to go with predefined lengths (ie, the fields are all vertically aligned in a test editor), then you'll be using APPEND FROM testfile.txt SDF

Just make sure the DBF fields are the correct length.

 

[JRB-Bldr]

"the "Raw" data we expect to import into the new VFP table will be a TAB delimited type txt file"

I can't speak for others, but personally I have had trouble with importing TAB delimited data and having all of the field data go into the correct table field. Sometimes it worked and other times "Not So Much".
It should be straight forward, but I can only speak from my own experiences.

You are much better with CSV (Comma separated data) which can either be Comma only or Quote-Comma.
NOTE1 - if you data should possibly contain commas, use the Quote-Comma format so that the import does not get 'confused'.

But my own experiences have been successful the vast majority of times by with doing:

USE OurTable EXCLUSIVE
SELECT OurTable
APPEND FROM <whatever CSV file> DELIMITED

NOTE2 - as shown above, for best import speed make sure your recipient data table is used EXCLUSIVE.

If your data is already in TAB delimited and you want to consider changing, make a copy of your file and then just replace all of the TAB's with a Comma. Then try importing into a VFP data table.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Lee,

You said" .. can I use some other code that will allow me to predetermine or predefine the actual length of the fields that are already known in the txt file

The length of the fields in the text file? Are you implying that the fields are a fixed width? If so, they're not tab-delimited.

If each field is the same width in every record, then the file is SDF, not delimited.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[keepingbusy]

brigmar

If you want to go with predefined lengths (ie, the fields are all vertically aligned in a test editor), then you'll be using APPEND FROM testfile.txt SDF

We will give that a try and post back

jrbbldr

You are much better with CSV (Comma separated data) which can either be Comma only or Quote-Comma

I agree with you but unfortunately the original txt file will only be sent in the format I have described

Mike

The length of the fields in the text file? Are you implying that the fields are a fixed width? If so, they're not tab-delimited.

Now you've got me thinking... This could well be the case and as brigmar has mentioned about sdf, I'll try this out and certainly post back with the result.

Thanks all

Lee

 

[JRB-Bldr]

"I agree with you but unfortunately the original txt file will only be sent in the format I have described"

That's why I told you how to convert a TAB delimited file into a Comma delimited file. Just because they send it one way, doesn't mean that you are stuck with it being that way.

Mike has raised a good point, if the fields are fixed in length, then you typically will not have any other delimiters (TAB or comma).

If the fields are delimited, then the field data might indeed vary in length because you are intended to use that delimiter to determine when that field's data starts and stops.

You will have to design your your table fields as big as you can possibly anticipate the individual field data arriving so that the import will not truncate the data.

If, rather than doing an APPEND FROM command, you do a low-level parsing of the incoming data, then you can analyze the length of any data element prior to inserting it into the data table. The APPEND FROM will not allow that step.

If you use low-level parsing, you will have an opportunity to do an ALTER TABLE to change the size of an individual field to meet the input data requirements if needed.

Good Luck,
JRB-Bldr

 

[keepingbusy]

All

This is what we have tried so far:

brigmar and Mke mentioned about using

APPEND FROM myfile.txt SDF

so we tried that and still didn't get the correct result with data from the myfile.txt field not ending up in the correct fields in the VFP9 table with some of it being half in one field and the other half in some other field.

I remembered sometime ago this thread:

[URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=1048898&page=3[/URL]

and in particular the Microsoft knowledge base question:

[URL unfurl="true"]http://support.microsoft.com/kb/q260405/[/URL]

So I tried that code to replicate the field lengths and types from the Access table to VFP9 table.

The structure appears to have been created however, when we tried to import the myfile.txt file into that newly created VFP9 table from the Access table, the data was again, all over the place.

I also tried a different selection of append from commands including DELIMITED WITH TAB and SDF as mentioned.
So as we stand a the moment, we have a myfile.txt file containing the relevant data, a VFP9 table which has been created as mentioned above, but still at a loss why this isn't working.

Mike also mentioned:

Are you implying that the fields are a fixed width?

Well I think this might be the case and having racked my brains and cant really think of anything else they could be. I am aware they do not change.

I'm hoping someone can please shed any more light on this as to why a simple effort to import a txt file is turning into a nightmare!

Thank you

Lee

 

[JRB-Bldr]

As I mentioned above, doing an IMPORT/APPEND relying on TAB delimiting to separate data does not always work. It may be due to how back-to-back delimiters are handled when 2 (or more) adjacent empty fields exist.

Regardless, just because you receive a file with TAB delimiters, does not mean that it has to stay that way prior to IMPORT/APPEND. You can change the TAB's to something else such as commas.

If the fields are indeed fixed length, then there are NO delimiters between fields. The character positions within any given line determine the individual field content.

You should be able to determine if the data is Fixed Length or Delimited by opening the Text file in Notepad and 'crusing' through a line with your Left/Right arrow keys. If your cursor occassionally jumps from one position to another then it is jumping over a TAB. If it moves smoothly through all of the characters, then there are no TAB delimiters. NOTE - you could also use a HEX Editor to examine the Text file content for HEX 09's (TAB's).

If you should have Fixed Length data, then you will need to use low-level parsing to extract the field data from the text line.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

You should be able to determine if the data is Fixed Length or Delimited by opening the Text file in Notepad and 'crusing' through a line with your Left/Right arrow keys.

Another way of doing it would be to open the file in Microsoft Word, and then choose the option to see "formatting marks". The tabs will appear as little arrows.

If, instead of tabs, there are several spaces after each field, it suggests the file is SDF.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[keepingbusy]

Mike and jbr

Some real good advice from your posts. I'll try those out and post back

Thank you

Lee

 

[keepingbusy]

JRB

If the fields are indeed fixed length, then there are NO delimiters between fields. The character positions within any given line determine the individual field content.

This now appears to be the case. I opened the file in Notepad and Wordpad and the cursor is only stepping a single character at a time

As you and Mike have suggested, the file we are working with appears to a System Data Format (SDF) file.

Mike

If, instead of tabs, there are several spaces after each field, it suggests the file is SDF.

As mentioned, the file appears to be an SDF type.

Ok, next step then is for me to look at "low-level parsing to extract the field data from the text line."

Thanks so far guys.

Lee

 

[JRB-Bldr]

"the cursor is only stepping a single character at a time"

OK you seem to have verified that the file is NOT TAB delimited.
So, assuming that there are no other delimiters such as commas being used, instead it is a fixed field length.

Writing a low-level parsing routine isn't too hard.
Just study the VFP Functions FOPEN(), FGETS(), FCLOSE().

Once you have a line into a string with something like:
cTextString = FGETS(lnHandle)
then you can just extract the various pieces of field-specific data from the cTextString
cFld1Val = SUBSTR(cTextString,1,20)
cFld2Val = SUBSTR(cTextString,21,40)
nFld3Val = VAL(SUBSTR(cTextString,41,50)
etc.
The just
SELECT MyTable
REPLACE Field1 WITH cFld1Val,;
Field2 WITH cFld2Val,;
Field3 WITH nFld3Val
and so on....

Then go back and get the next text string with another FGETS().

Good Luck,
JRB-Bldr

 

[keepingbusy]

jrb

Ok, I'll view the help but unless I am getting confused with this, what you have mentioned e.g.:

cFld1Val = SUBSTR(cTextString,1,20)
cFld2Val = SUBSTR(cTextString,21,40)
nFld3Val = VAL(SUBSTR(cTextString,41,50)

that would give you the length of the data in the sdf file which you could then transfer to the relevant field in the table.

What I'm not sure about here is that we don't know the actual length of the field.

Sorry if I'm missing something but I have had no dealings with this before.

Lee

 

[brigmar]

Open the file in notepad with a monospaced font

Are all the fields vertically aligned?

If so, then you DO know the field lengths; you can just count them.

If not, then it gets tricky.

If you have access to the mdb, then you can use ADO to pull the data directly, without the need for an intermediate 'delimited' file.

 

[JRB-Bldr]

lnHandle = FOPEN("MyTextFile.txt")
DO WHILE !FEOF(lnHandle)
 * --- Get Line From Text File ---
 cTextString = FGETS(lnHandle)
 * --- Test Line To See If Header Line or Data ---
 IF <some test criteria>
   * --- Data Line ---
   * ----- Field1 Char & Always From Char 1 Thru Char 20 ---
   cFld1Val = SUBSTR(cTextString,1,20)
   * ----- Field2 Char & Always From Char 21 Thru Char 40 ---
   cFld2Val = SUBSTR(cTextString,21,40)
   * ----- Field3 Numeric & Always From Char 41 Thru Char 50 ---
   nFld3Val = VAL(SUBSTR(cTextString,41,50)

   SELECT MyTable
   APPEND BLANK  && Create New Record 
   REPLACE Field1 WITH cFld1Val,;
      Field2 WITH cFld2Val,;
      Field3 WITH nFld3Val  && Populate New Record Fields
 ENDIF
ENDDO

"What I'm not sure about here is that we don't know the actual length of the field."
Whoever is creating the new 'raw', importable data text files should have that information and should provide it to you as part of the file specification.

If you absolutely need to determine this yourself (and I have had to do so a few times) then you can again open the Text file in Notepad with Format/Word Wrap not checked (or something) and count the characters between the individual pieces of data.
NOTE - Count those characters in multiple rows to ensure that your 'field' count covers ALL circumstances.

If the 'field' character count cannot be determined to be FIXED in length, then there must be some other field delimiter such as a comma being used to support variable field content length - then you could be back to possibly using an APPEND command with some DELIMITER and/or TYPE option.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Lee,

Let me get this right.

We've established that the fields are fixed length. Obviously, you can determine the length of each field.

So, remind me what the problem is with doing APPEND FROM ... TYPE SDF? It would mean that the table structure would have to match the field sizes in the imported file. Is it not possible to do that?

I'm assuming that the files that you import always have the same structure. Is that a valid assumption?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[keepingbusy]

Mike

Just to recap:

We have a txt file containing data which is the file supplied. Unfortunately we have no control over this so there's no option for CSV etc

We tried to use this file to create a vfp9 table by importing the above file however, this didn't work. When we tried to import the txt file into this table let's say it didn't fit

We then exported some data from the original access program both in CSV and TAB delimited format. Both of these were used to create a vfp9 table, again both failed using the original txt file when imported. I have mentioned previously the problems etc.

I'm sure by now we have established that the original data txt file is an SDF type format with fixed field length.

I also looked at the access table structure and whilst some fields are quite clear e.g. Character 20, memo etc. Some fields are logical or numeric but don't give the attributes e.g. Numeric 7,2

I have googled "foxpro low level parsing" and found one forum. I am going to try and work that out.

I think the whole issue with this project is trying to establish the exact structure of the access table which I can then replicate in fox.

Hope the above answers your question.

Lee

 

[Mike Lewis]

Lee,

Sorry if I'm asking more questions than I'm answering. Anyway, I'm getting the picture now.

Presumably the structure of the Access table is constant. So it will always export an SDF file with the same field widths.

So, your FoxPro table has to have those same field widths (in order for SDF imports to work).

So, am I right in thinking that the sticking point is working out what those widths are? (Hence your remark "the whole issue with this project is trying to establish the exact structure of the access table which I can then replicate in fox.").

If that's right, can you not just open the text file in a text editor or Microsoft Word; use a fixed-pitch font; set the editor to show "formatting marks", and count the characters and spaces in each field?

Or am I still missing something?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[keepingbusy]

Mike

Yes! You are right and I think the only thing we haven't tried yet is a fixed pitch font so we'll look at that and post back later.

Many thanks for the replies

Lee

 

[Mike Lewis]

Lee,

Here's a way of getting the widths of all the fields, with the minumum of keystrokes or hassle:

1. Open the text file in the Visual FoxPro text editor (with MODI FILE).

2. Select Format / View White Space.

3. In the command window, execute:

? LEN(_CLIPTEXT)

Ignore the result.

4. In the text file, highlight the first field. Hit CTRL+C.

5. In the command window, go back to the above command, either by clicking on it, or pressing the up-cursor key; then hit Enter.

6. Repeat steps 4 and 5 for each field in turn.,

7. On the background screen, you should now see a list of numbers. Ignore the first of these (this will be from the initial command in step 3). The other numbers will represent the field widths.

I'm sure you'd have worked out something similar for yourself, but I thought I'd jot it down while I was thining about it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business