WbExport how to replace empty character

[vishal waghmode]

Hi,

while migrating tables from sql server to postgresql using Wbexport I am facing some issues.

WbExport 

-type=text

-file='D:\Migration\tablename.txt'

-delimiter='|'

-quotechar='^'

-encoding=UTF8

-quoteCharEscaping=duplicate

-formatFile=postgres

-header=true

-nullString=

-decimal='.'

-dateFormat='yyyy-MM-dd'

-replaceExpression='(\n|\r\n)' -replaceWith='$';

SELECT * FROM DBNAME.dbo.tablename;

I am replacing carriage return with dollar sign but I have a value in a column which doesn't contain any characters (i.e empty) that value is considered as NULL and I don't want that.

How can I replace empty character with any other character.

I have tried below logic but it is not working

1. -replaceExpression='(\n|\r\n)'|char(0) -replaceWith='$';

2. -replaceExpression='(\n|\r\n|\0)' -replaceWith='$';

3.-replaceExpression=char(0) -replaceWith='$'

   -replaceExpression='(\n|\r\n)' -replaceWith='$';

[Thomas Kellerer]

That's what -escapeText is for, e.g. -escapeText=control

https://www.sql-workbench.eu/manual/command-export.html#text-escape-switch

WbImport then needs to be told to unescape those using -decode=true

https://www.sql-workbench.eu/manual/command-import.html#text-import-decode

If you want to consider empty strings as empty strings during _import_ use WbImport with -emptyStringIsNull=false

Thomas

[vishal waghmode]

Hi Thomas,

I tried below query :

WbExport 

-type=text

-file='D:\Migration\tablename.txt'

-delimiter='|'

-quotechar='^'

-encoding=UTF8

-quoteCharEscaping=duplicate

-formatFile=postgres

-header=true

-nullString=

-decimal='.'

-dateFormat='yyyy-MM-dd'

-escapeText=control

-replaceExpression='(\n|\r\n)' -replaceWith='';

SELECT * FROM DBNAME.dbo.tablename;

but I am getting error while uploading/importing the data into postgreSQL using putty. The column has not null constraint and WBexport is converting empty character into null. I just want to convert empty character to null for one column for rest columns null value is accepted.

 

[Thomas Kellerer]

You don't need -replaceExpression if you use escapeText
Note, that if you intend to import the file with Postgres' COPY statement you should use -escapeText=pgcopy

But escapeText won't magically convert NULL values into something else. If the original column contained a NULL value, then WbExport will output NULL

The only way to get around that is to do a coalesce() in the SELECT statement for the columns in question.

> Capture.PNG
>
> but I am getting error while uploading/importing the data into postgreSQL using putty. The column has not null constraint and WBexport is converting empty character into null. I just want to convert empty character to null for one column for rest columns null value is accepted.
>

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/74f7c95c-1acd-4c50-b906-6d4adab77524%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/74f7c95c-1acd-4c50-b906-6d4adab77524%40googlegroups.com?utm_medium=email&utm_source=footer>.