Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: BDU and dates problem

    Using Pervasive v9.5...

    Using BDU, I'm trying to load a text file into a table that contains a date column. The date in the text file is formatted correctly (yyyy-mm-dd); however, when I run BDU, the date stored in the table is not correct. For example, with a date in the text file of:
    2008-06-17
    the resulting value in the DB is:
    4/30/10234

    I have put the date in single quotes with the same result.

    Is there a way to format the date to get it loaded correctly using BDU?

    TIA for any insight into this dilemma.

    al

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Please post a sample line from your text file and the exact command line you are using for BDU. I've seen problems with the command line causing problems in the data.
    Also, make sure your date field is in the right place in the text file.
    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.

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    Thanx for the quick reply.



    I've attach a screen print of the table structure.

    Sample data record that I'm trying to load:
    0 13408 2008-06-17 01:01:00 01:02:00 MWTH N 2008-06-18 01:02:00 1 1


    Command line:
    bdu <db> <tablename> <path>\tab_test.txt


    I've also created the data as a comma-delimited file using this command line:

    bdu <db> <tablename> <path>\comma_test.txt -t ,


    but the results in the DB are the same.

    I've also attached the results of a load attempt. Note that the RowID, RecorderID, and InboundScheduleID have correct values, but the DATE, TIME, and INTEGER values are incorrect.
    Attached Thumbnails Attached Thumbnails table.JPG   results.JPG  

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Well, I tried it here using 9.70 with your table (I recreated it based on the screen shot) and copied the one record you posted. I was able to get the data into the table and it showed correctly in ODBC Test. I did verify that the data only had one tab between values.
    I'm attaching the actual text file I used. Can you try it? Does it add the record correctly? If so, can you post the actual file (even just trimmed down)?
    Attached Files Attached Files
    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.

  5. #5
    Join Date
    Apr 2009
    Posts
    4
    I tried your file - same bad values.

    The record that I previously posted is from the actual file.

    It sounds like the command and the text file are formatted correctly.

    Any other suggestions on how I might (quickly) get data from this text file into the table? There are roughly 350 records in the text file (too many to key in manually).

    I appreciate you responses. I'm not a power user by any means, but I can find my way around pretty well. I was thinking (hoping?) I was missing something simple.

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    If the file I attached didn't work, I'm wondering if there's something wrong with the data file itself. Can you attach an empty copy of the data file? The easiest way to get an empty file is to use BUTIL and issue a CLONE operation like:
    BUTIL -CLONE <newfile> <originalfile>
    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.

  7. #7
    Join Date
    Apr 2009
    Posts
    4
    After discussing your replies with a colleague, I came up with a workaround. I created a temporary table with the same column names as the original table. For the columns with DATE and TIME data types in the original table, the temp table has CHAR columns. For the columns with INTEGER data types in the original, the temp table has NUMERIC columns. I chose CHAR and NUMERIC data types because I had successfully used these with BDU on a prior project. After loading the text file into the temp table, I did an

    INSERT INTO <original-table> (RowID,Recorderid,<etc.>)
    SELECT * FROM <temp-table>

    No CAST or CONVERT statements were necessary.

    It appears that the BDU process does not use the same conversion routines as P-SQL/CC, at least in v9.5

    Thanx for the help, and for the quick replies!

Posting Permissions

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