Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    20

    Unanswered: Export Data: Table with nullable cols

    Hello Pervasive.SQL experts,

    I am trying to export some data from a table using the export wizard (Task/Export Data). All is well with this except some problem on the output where a "0" is added in front of nullable column causing an extra data column for each nullable. To illustrate:

    MyTable
    Field type null
    Col1 integer N
    Col2 integer Y
    Col3 integer Y

    Sample data:
    1 1 1
    2 2 2
    3 3 3

    If I export this data with table column on first row option (to say MyTable.csv), the output will be like this:

    "Col1","Col2","Col3"
    "1","0","1","0","1"
    "2","0","2","0","2"
    "3","0","3","0","3"

    If I try to load this file into another program, say Excel, the Column and Data won't match because of the extra zero's for the nullable columns.

    I understand that this is the extra byte used to signal nullability of a column. Thus, it should be treated as 'internal' data and not exported. So, is there a way to make (or force) the Export wizard not to export the "0"'s ?

    Note: Converting tables using Null Conversion utility is not possible in my situation.

    Many thanks in advance.

    _msd_

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    What version of the engine are you using? This was a problem at one time but was addressed in the Export Utility. You could look at a third party tool that exports the data as well. There are several available.
    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
    Aug 2004
    Posts
    20
    Hi Mirtheil,

    Thanks for your reply. I am using Pervsasive.SQL 2000i, PCC v7.90.230.036 (if this means something).

    Currently I use MS Access for data extraction but was hoping to be able to do this task from within PCC because it allows me to do SQL (selective data) export while using MS Access only retrieves the whole table. Note: Eventually we (my team) want to be able to do Import as well.

    If I cannot get around the Export Utility problem on nullable columns, do you know of any other third party tool that can be used with my version to do SQL Import/Export?

    _msd_

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    7.90 is SP3. That being said, you may have SP4 as the PCC was not updated in SP4. You can check the version of W3ODBCCI.DLL or W3ODBCEI.DLL to be sure (SP4 is 7.94.xxx while SP3 is 7.90.203).
    I know that the V8 version of the Export Util does not export the null columns the same way.
    As far as export, DataXtraction is a tool I've written that will allow for SQL statements to be issued to export to CSV, HTML, or an ASP page to grab the data on the fly. It's available at http://www.mirtheil.com/dataxtraction.asp. It does not do import. however. There are probably other tools but I'm not aware of any.
    You should be able to issue an SQL statement through Access by creating a SQL Pass Through Query.
    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.

Posting Permissions

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