Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2005
    Posts
    20

    Unanswered: Import into a Pervasive Table

    I currently recieve various text files that have to be massaged into a suitable format to up load into a btrieve table. The process was very manul intensive, so I set up an Access database to do all of the work. However when I connect the Access database to the Btrieve table via an ODBC connection I get trailing blanks on the fields that have been set up longer than the actual data going in. For example, Agree_No is a zstring 7, but the data imported could be either 4 or 5 characters in length.

    The sql statement that I am using is wrapped inside of a VB code and its as follows.

    DoCmd.RunSQL sqlstr

    sqlstr = " INSERT INTO ZAgents ( Code, Agree_No, Distrib_Date, Distrib_Status, Title, Name, First_Name, Middle_Name, Sex, Address_Link, Agent_Broker, Region, Agreement_Name, Brk_Rate_VPC, Brk_Rate_NoVPC, EffDate, Service_First,Created, Modified)SELECT NewDMS.Code, rtrim(NewDMS.Code), NewDMS.Date1, NewDMS.Status, NewDMS.Title, NewDMS.Name, NewDMS.First_Name, NewDMS.Middle_Name, NewDMS.Sex, NewDMS.Address_Lnk, NewDMS.Agent_Broker, NewDMS.Region, NewDMS.Agreement_Name, NewDMS.Brk_Rate_VPC, NewDMS.Brk_Rate_NonVPC, NewDMS.Eff_Date, NewDMS.Service_First, Created, Modified FROM NewDMS;"
    DoCmd.RunSQL sqlstr

    I have tried using the trim commands but that doesn't work.

    I do have a third party software that I can manually import the data into the table without this issue so there must be a way that I can get access to do the same.

  2. #2
    Join Date
    Feb 2005
    Posts
    20
    Sorry, I should have mentioned that I am on Pervasive 2000i

  3. #3
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    I'm not sure I understand. What are you expecting instead of trailing blanks?
    I do have a couple of questions:
    1. How are the fields defined in the DDF (for example the "Agree_No" field you mention)?
    2. What happens if you issue an Import statement through the Pervasive COntrol Center?
    3. Is is possible to change the text file into CSV in the record layout you need? If so, you might be able to use the Pervasive Import Wizard.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  4. #4
    Join Date
    Feb 2005
    Posts
    20
    The Agree_No is a key field in the software package and this field should not contain any trailing blanks, but can contain nulls.

    The DDF has this field defined as a Zstring, 7 characters long.

    The text file could be converted into a CSV file and manually loaded, but this would defeat the purpose of building a fully automatic process with no manual intervention required.

  5. #5
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Are you expecting the field to be binary zero padded rather than space padded?
    Can you post a BUTIL -STAT report of the file? ALso, can you post the record layout as the DDFs describes it?

    I've seen this type of behavior when the data type in the DDF is a space padded type while the data file is expecting a binary zero padded value.

    If you know the record structure and know the structure of the text file, you might consider writing (or having written) a program that uses the Btrieve API (rather than SQL) to insert this data.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  6. #6
    Join Date
    Feb 2005
    Posts
    20
    Butil -stat and DDF report attached
    Attached Files Attached Files

  7. #7
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Okay, your DDFs are wrong. You've got fields defined as String and as ZString. This could cause the exact behavior you are seeing. Specifically, your Btrieve file has String fields for the index but one of the indexes (and fields) in the DDFs defines that same field as a ZString. THis is the "name" field. In the Btrieve file, the "name" field is an index and is defined as a string. In the DDF, it's defined as a ZString. The String field is space padded while the ZString is binary zero padded.
    I would suggest rebuilding the DDFs to correct the inaccuracies.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  8. #8
    Join Date
    Feb 2005
    Posts
    20
    Thank you so much, I have just spent a very frustrating 12mths with the software vendors to try to resolve this issue without any where near the success your advice has given me.

    My very first run with changing the DDFs has shown some very positive results, but I will probably need to spend another few months rebuilding and testing all of our DDFs to the max.

    Once again, thank you for you help.

  9. #9
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    You're welcome. Good luck with the rebuilding..
    Post if you have any other questions.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  10. #10
    Join Date
    Feb 2005
    Posts
    20

    Network Issue

    Well every thing worked OK on my C drive, but when I transferred this into the Network, I now get the following error-
    "You tried to assign the Null value to a variable that is not a Variant data tye. No:3162"

    The DDF's and the data files on the network are exactly the same as those on the C drive.

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •