NeoDBpro Data Export
Quote from Andy Marshman on July 16, 2021, 5:49 pmHi,
I'm having trouble with NeoDBpro not exporting an SQL Server Timestamp column correctly. I think it is interpreting it as a Bytes(8) column and when exported using any of the dbpExportTo commands I am getting unexpected characters which are causing problems when used to import the data or open it in Excel.
Has anyone else seen this before?
Regards
Andy
Hi,
I'm having trouble with NeoDBpro not exporting an SQL Server Timestamp column correctly. I think it is interpreting it as a Bytes(8) column and when exported using any of the dbpExportTo commands I am getting unexpected characters which are causing problems when used to import the data or open it in Excel.
Has anyone else seen this before?
Regards
Andy
Quote from Gaev on July 17, 2021, 3:56 pm@andy-marshman
Discussions on this page ...
https://docs.microsoft.com/en-us/answers/questions/238819/purpose-to-use-timestamp-datatype-in-sql-server.html
... might explain the unexpected results.
Discussions on this page ...
... might explain the unexpected results.
Quote from Andy Marshman on July 17, 2021, 4:22 pm@gaev That's an interesting article but doesn't explain why the data I'm being exported look like just random characters. According to that article and several others I've read the type should be a binary number so byte(8) is probably correct but the data exported does not match what is displayed in SQL Server.
Regards
Andy
@gaev That's an interesting article but doesn't explain why the data I'm being exported look like just random characters. According to that article and several others I've read the type should be a binary number so byte(8) is probably correct but the data exported does not match what is displayed in SQL Server.
Regards
Andy
Quote from Gaev on July 17, 2021, 4:54 pm@andy-marshman
doesn't explain why the data I'm being exported look like just random characters
the data exported does not match what is displayed in SQL Server.Can you post a few (say 5) examples of what you see in SQL Server vs. what is exported ?
doesn't explain why the data I'm being exported look like just random characters
the data exported does not match what is displayed in SQL Server.
Can you post a few (say 5) examples of what you see in SQL Server vs. what is exported ?
Quote from Andy Marshman on July 17, 2021, 7:23 pm@gaev
Here are a some screen shots of how it looks in SQL Server Management Studio, Database.NET Pro and Notepad along with the original export file.
Regards
Andy
Here are a some screen shots of how it looks in SQL Server Management Studio, Database.NET Pro and Notepad along with the original export file.
Regards
Andy
Uploaded files:- You need to login to have access to uploads.
Quote from Gaev on July 17, 2021, 9:41 pm@andy-marshman
The two viewers ... SQL Server Management Studio and Database.NET Pro ... display the tststamp field/column as ...
0x000000000039D384
... the prefix 0x means that it is displaying binary data in its Hex (hexadecimal) format i.e. each byte is displayed as two (hexadecimal) characters (0 to 9, A to F).
I do not believe that you can export binary data to a CSV (comma separated values) format, which was designed (back in the 1980's) to handle printable characters ... as a result, when you try and view the csv file using text editors (notepad or scite) that handle text characters, they get confused when displaying non printable characters ... hence, you end up with squigly characters.
Looks like this field was defined (automatically or manually) on SQL Server as an 'eight byte binary'
Do you need this field in to be imported into Excel ? ... it is something internal to the operation of SQL Server
a) if you don't, you can just hide this column before doing the Export
b) if you do, you might consider creating an additional field/column, where you 'convert the binary value in tstamp to an integer' in every record, just before the you do the Export (might not be practical if the database is large).
The two viewers ... SQL Server Management Studio and Database.NET Pro ... display the tststamp field/column as ...
0x000000000039D384
... the prefix 0x means that it is displaying binary data in its Hex (hexadecimal) format i.e. each byte is displayed as two (hexadecimal) characters (0 to 9, A to F).
I do not believe that you can export binary data to a CSV (comma separated values) format, which was designed (back in the 1980's) to handle printable characters ... as a result, when you try and view the csv file using text editors (notepad or scite) that handle text characters, they get confused when displaying non printable characters ... hence, you end up with squigly characters.
Looks like this field was defined (automatically or manually) on SQL Server as an 'eight byte binary'
Do you need this field in to be imported into Excel ? ... it is something internal to the operation of SQL Server
a) if you don't, you can just hide this column before doing the Export
b) if you do, you might consider creating an additional field/column, where you 'convert the binary value in tstamp to an integer' in every record, just before the you do the Export (might not be practical if the database is large).
Quote from Andy Marshman on July 18, 2021, 11:48 am@gaev
Your right it is only an internal field to SQL Server and I don't really need it to export.
The export is only done based on the results of an SQL query against each field in a table where certain data resides. So I am then just doing an executeSQL with a select * from table name, and then all of the required columns in the where clause.
You mentioned hiding the column prior to export. How can this be done?
Regards
Andy
Your right it is only an internal field to SQL Server and I don't really need it to export.
The export is only done based on the results of an SQL query against each field in a table where certain data resides. So I am then just doing an executeSQL with a select * from table name, and then all of the required columns in the where clause.
You mentioned hiding the column prior to export. How can this be done?
Regards
Andy
Quote from Gaev on July 18, 2021, 3:22 pm@andy-marshman
You mentioned hiding the column prior to export. How can this be done?
1) use dbpSetFieldProperties to hide the field (https://neodbprohelp.visualneo.com/Grids.html#dbpSetFieldProperties) ...
Visible=yes/no
Yes = the field is displayed in the grid. No = the field does not appear in the grid.2) When you invoke the dbpExportToCSV command (https://neodbprohelp.visualneo.com/ImportExport.html#dbpExportToCSV) ...
IncludeHidden=Yes/No
Yes = export both visible and hidden fields. No = export only visible fields.
You mentioned hiding the column prior to export. How can this be done?
1) use dbpSetFieldProperties to hide the field (https://neodbprohelp.visualneo.com/Grids.html#dbpSetFieldProperties) ...
Visible=yes/no
Yes = the field is displayed in the grid. No = the field does not appear in the grid.
2) When you invoke the dbpExportToCSV command (https://neodbprohelp.visualneo.com/ImportExport.html#dbpExportToCSV) ...
IncludeHidden=Yes/No
Yes = export both visible and hidden fields. No = export only visible fields.
Quote from Andy Marshman on July 18, 2021, 3:27 pm@gaev
Thanks, that has solved the problem. When I saw that in the help I thought it only related to fields displayed in a grid and as I am not using a grid just a dbpExecSQL command to a temp table I hadn't thought to try it.
Again, thanks for your help.
Regards
Andy
Thanks, that has solved the problem. When I saw that in the help I thought it only related to fields displayed in a grid and as I am not using a grid just a dbpExecSQL command to a temp table I hadn't thought to try it.
Again, thanks for your help.
Regards
Andy