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_

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.

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_

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.