Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Predetermined or predefined TAB length

Status
Not open for further replies.

keepingbusy

Programmer
Apr 9, 2000
1,470
GB

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:
Code:
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
 

Mike

Thank you for the post. Your suggestion is a good idea and similar to that of JRB's where we opened the txt file in notepad, wordpad and office word to see where the spaces were.

Still a slight problem there because if we get an address field such as 25 High Street which would like 25.High.Street.... with the view white space on (or other spacers in other programs) we cannot establish if the 25 is not part of 25 High Street.

We are aware that there are other fields that completely fill that field e.g. ABC12345 (C 8) then straight after it something like 0000000. There are multiple incidence like this throughout the one line of the txt file.

There are many different field types within this txt file so we will carry on with the suggestions posted and hopefully (and eventually) find the right field lengths and attributes.

As you will probably have already realised, this is one of the reasons why we don't use Access although I am sure it has it's uses out there in the relevant communities.

As soon as we get further forward I'll post back.

Thanks again.

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

I already gave you to an approach to doing a low-level parsing above.

Next...
"Still a slight problem there because if we get an address field such as 25 High Street which would like 25.High.Street.... with the view white space on (or other spacers in other programs) we cannot establish if the 25 is not part of 25 High Street."

If you are visually examining the file contents in either Notepad or the VFP Edit window, you should easily be able to see where the Address field 'columns start. Every one of them should be starting at the same character position on each and every row. And by using a non-proportional font, every field 'column' should line up visually in every row as you are examining the file.

As to the varying field types...
You should already know the Access field type. You would make your VFP field type the same. When you get the data from the text string you either use it 'as is' for a character field type or you convert the string values to the appropriate type before putting it into the VFP field (that's why I showed you and example for a numeric value above).

If there is still a question, why don't you get those responsible to send you a sample Access Export to Excel or something so that you can actually see the values within the individual fields and then get a matching Export to a text file. By being able to compare the values per field in one form and those seen in the matching 'record' in the text file, you should easily be able to zero in on this issue.

Good Luck,
JRB-Bldr
 
Craig,
The OP has determined that the file is NOT delimited by tabs by viewing the file directly in a text editor.

Lee,
If you are able, can you attach a (possibly redacted) sample of the firs few lines of the file?
 
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.

IMHO csv is one of the worst text formats to choose for use in VFP. If you need it then prefer delimited instead.

However you are saying "exported the Access table" and "import it and create a table structure via VFP". IOW you are unnecessarily using text file format to transfer data from a data backend to another data backend. Text format should not be the choice. If you want something text like use XML. But IMHO it is much better to directly connect to mdb using OLEDB and populate the VFP cursor. Here is a sample code from my FAQ entry on another forum:

Code:
Public oForm
oForm = Createobject('myForm')
oForm.Show()

Define Class myForm As Form
  Height = 450
  Width = 850
  DataSession=2
  Caption='Show Access Data'

  Add Object lblAccess As Label With ;
    Caption = "Access Database", ;
    Left = 10, Top = 15, Width = 100

  Add Object txtMDBlocation As TextBox With ;
    Left = 112, Top = 12, Width = 520

  Add Object cmdBrowse As CommandButton With ;
    Top = 10, Left = 640, Caption = "Browse", Autosize=.t.

  Add Object lblTables As Label With ;
    Caption = "Tables", Left = 20, Top = 40, Width = 40

  Add Object lstTables As ListBox With ;
    Height = 400, Left = 65, Top = 40, Width = 265

  Add Object grdShow As Grid With ;
    Height = 400, Left = 340, Top = 40, Width = 500

  Procedure listtables
    Local lnConnHandle,lcMDB
    With This.txtMDBlocation
      If Empty(.Value) Or !File(.Value)
        Return
      Endif
      lcMDB = Trim(.Value)
    Endwith

    lnConnHandle = ;
     Sqlstringconnect("Driver={Microsoft Access Driver (*.mdb)};"+;
                     "Uid=Admin;DBQ="+m.lcMDB)
    SQLTABLES(m.lnConnHandle, ['TABLE'], 'crsTables')
    SQLDISCONNECT(m.lnConnHandle)
    Select crsTables
    This.lstTables.Clear()
    Scan
      This.lstTables.AddItem(crsTables.table_name)
    Endscan
  Endproc


  Procedure txtMDBlocation.LostFocus
    Thisform.listtables()
  Endproc


  Procedure cmdBrowse.Click
    This.Parent.txtMDBlocation.Value = Getfile('MDB','','',0,'Select Access Database')
    Thisform.listtables()
  Endproc


  Procedure lstTables.InteractiveChange
    Local lnConnHandle,lcMDB,lcSQL
    With This.Parent.txtMDBlocation
      If Empty(.Value) Or !File(.Value)
        Return
      Endif
      lcMDB = Trim(.Value)
    Endwith

    lcSQL = 'select * from "'+Trim(This.Value)+'"'
    lnConnHandle = ;
     Sqlstringconnect("Driver={Microsoft Access Driver (*.mdb)};"+;
                     "Uid=Admin;DBQ="+m.lcMDB)
    SQLEXEC(m.lnConnHandle,m.lcSQL,'crsLocal')
    SQLDISCONNECT(m.lnConnHandle)
    With This.Parent.grdShow
      .ColumnCount = -1
      .RecordSource = 'crsLocal'
    Endwith
  Endproc
Enddefine

Cetin Basoz
MS Foxpro MVP, MCP
 
Hi brigmar

I will arrange for a partial example to be created and will show a link soon. I won't be around for a couple of days (daugter getting married!) so I'll post back soon.

Cetin

Thank you for the code which I will try out as soon as able.

Thanks all

Lee
 
A sample of the text file data lines is good, but ONLY if the data in each separate 'field' is clearly distinguished from the data in the adjacent 'fields'.

If you have data in your sample line where the data from one 'field' is long enough in characters to run right up next to the data from another 'field', then you cannot easily determine where one 'field' ends and another one begins.

The best approach is as I suggested above, do a cross correlation between the exact same data as seen in another manner such as:
* In an Excel file
* In a field by field list showing the field values
* An XML file
* some other manner
and those exact same individual values as seen in one of your text files.
Those individuals supplying the text files should be able to support your development by Exporting from Access into a different format for your testing analysis purposes.

With that cross correlation you will easily be able to see the field contents and can easily determine where that same field value 'resides' (beginning character number & ending character number) within the text file format that you need to import.

Good Luck,
JRB-Bldr
 
Hello all

brigmar
If you are able, can you attach a (possibly redacted) sample of the firs few lines of the file?

I'm not sure if this will help but the content alignment has not been changed just the actual data.

Since starting this thread, we have requested the actual access table structure but the previous developer has been very slow at coming forward. I'm sure there are many reasons why this is.

We do have a copy of the Access table but as mentioned previously, whatever we do to replicate this in VFP9, it just doesn't match that of the txt file that is imported into it.

Thank you

Lee
 
"We do have a copy of the Access table"

Can you open it yourself in MS Access?

If so then you can to the Export yourself so that you can cross-correlate the data as seen in something like:
Excel
HTML
against the exact same data as seen in an Export to your text file.

With that information, you have everything you need to write the parsing routine.

Good Luck,
JRB-Bldr
 

JRB
Can you open it yourself in MS Access?
Yes. We can open the table but there is no data to use within that table to export.

When we try to import the data txt file into the access table we get an error message saying "Field 64 does not exist".

This is why we are finding this difficult as the original txt file is the file used to populate the access table so somewhere down the line there must be something missing in the process.

We'll keep looking for a way around this.

Thank you for your post.

Lee
 
"We can open the table but there is no data to use within that table to export."

Then use Access to put some data into at least one record (all fields) and export it as suggested above. This will be especially helpful if you can fill the individual fields to their maximum value or character count.

In that way you will be able to see how the data will look worst case in an exported Text file.

Good Luck,
JRB-Bldr
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top