Back Slash \ issue

[Guntry Vinod]

Hi Team,

We have dump from DB2 and trying to upload it Postgre.

The records which has \(back ward) are failing to import.

Can anyone in the team help us to resolve the issue.

Can you also let us know if we are posting the question to the right team.

Regards,
Vinod


Disclaimer:  This message and the information contained herein is proprietary 
and confidential and subject to the Tech Mahindra policy statement, you may 
review the policy at http://www.techmahindra.com/Disclaimer.html 
 externally 
http://tim.techmahindra.com/tim/disclaimer.html 
 internally within 
TechMahindra.

RE: Back Slash \ issue

[Guntry Vinod]

Hi Adrian\Kiran, 

Below is the issue. 

We are migrating from Db2 to Postgre. 

The Db2 dump consists of back Slash \ with in the data [dump] , but postgre is 
not escaping the Slash.

For example if name consist of Vinod\G after the inserting the dump the value 
in postgre is VinodG where as I need it as Vinod\G.

Since the dump is huge data I can't replace \  within the data to escape Slash. 

We are using import utility provided by Postgre. 

Can you please give me any solution in this regard?.

@kiran-let me also try solution you provided in the below mail. 

Regards, 
Vinod
___
From: Adrian Klaver [adrian.kla...@aklaver.com]
Sent: 02 May 2019 20:04
To: ravikris...@mail.com; Guntry Vinod
Cc: pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi; Biswa 
Ranjan Dash
Subject: Re: Back Slash \ issue

On 5/2/19 7:30 AM, ravikris...@mail.com wrote:
>  > We have dump from DB2 and trying to upload it Postgre.
> DB2 export command has an option to export it as CSV which quotes data
> so that any embedded
> lines or special characters inside the data is treated fine.  Does the
> csv format has quotes enclosing it ?

Please show the command you use to import into Postgres.
Also the error message you get.


--
Adrian Klaver
adrian.kla...@aklaver.com


Disclaimer:  This message and the information contained herein is proprietary 
and confidential and subject to the Tech Mahindra policy statement, you may 
review the policy at http://www.techmahindra.com/Disclaimer.html 
<http://www.techmahindra.com/Disclaimer.html> externally 
http://tim.techmahindra.com/tim/disclaimer.html 
<http://tim.techmahindra.com/tim/disclaimer.html> internally within 
TechMahindra.

RE: Back Slash \ issue

[Guntry Vinod]

Hi Team,

We are using the below command

COPY <> from 'C:\Data_Dump\ABC.txt' DELIMITER '|';  

Regards,
Vinod

-Original Message-
From: Adrian Klaver  
Sent: Thursday, May 2, 2019 8:58 PM
To: Guntry Vinod ; ravikris...@mail.com
Cc: pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi 
; Biswa Ranjan Dash 
Subject: Re: Back Slash \ issue

On 5/2/19 8:19 AM, Guntry Vinod wrote:
> Hi Adrian\Kiran,
> 
> Below is the issue.
> 
> We are migrating from Db2 to Postgre.
> 
> The Db2 dump consists of back Slash \ with in the data [dump] , but postgre 
> is not escaping the Slash.
> 
> For example if name consist of Vinod\G after the inserting the dump the value 
> in postgre is VinodG where as I need it as Vinod\G.
> 
> Since the dump is huge data I can't replace \  within the data to escape 
> Slash.
> 
> We are using import utility provided by Postgre.

Are you talking about COPY or some other utility?

> 
> Can you please give me any solution in this regard?.

In order for a solution to be found we will need more explicit information on 
what you are doing.

> 
> @kiran-let me also try solution you provided in the below mail.
> 
> Regards,
> Vinod
> ___
> From: Adrian Klaver [adrian.kla...@aklaver.com]
> Sent: 02 May 2019 20:04
> To: ravikris...@mail.com; Guntry Vinod
> Cc: pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi; 
> Biswa Ranjan Dash
> Subject: Re: Back Slash \ issue
> 
> On 5/2/19 7:30 AM, ravikris...@mail.com wrote:
>>   > We have dump from DB2 and trying to upload it Postgre.
>> DB2 export command has an option to export it as CSV which quotes 
>> data so that any embedded lines or special characters inside the data 
>> is treated fine.  Does the csv format has quotes enclosing it ?
> 
> Please show the command you use to import into Postgres.
> Also the error message you get.
> 
> 
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
> ==
> ==
> 
> Disclaimer:  This message and the information contained herein is proprietary 
> and confidential and subject to the Tech Mahindra policy statement, you may 
> review the policy at http://www.techmahindra.com/Disclaimer.html 
> <http://www.techmahindra.com/Disclaimer.html> externally 
> http://tim.techmahindra.com/tim/disclaimer.html 
> <http://tim.techmahindra.com/tim/disclaimer.html> internally within 
> TechMahindra.
> 
> ==
> ==
> 
> 


--
Adrian Klaver
adrian.kla...@aklaver.com

RE: Back Slash \ issue

[Guntry Vinod]

Hi Andrew,

So you mean to say we need to replace \\ in data. If so the data what we 
receive is huge chunk(we cannot open in notepad++ also) .

If we can pass the CSV instead of .txt or any other format. Do we have any 
solution. if Yes Can you please give me some example.

Many Thanks,
Vinod


-Original Message-
From: Andrew Gierth  
Sent: Friday, May 3, 2019 1:37 PM
To: Guntry Vinod 
Cc: Adrian Klaver ; ravikris...@mail.com; 
pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi 
; Biswa Ranjan Dash 
Subject: Re: Back Slash \ issue

>>>>> "Guntry" == Guntry Vinod  writes:

 Guntry> Hi Team,
 Guntry> We are using the below command

 Guntry> COPY <> from 'C:\Data_Dump\ABC.txt' DELIMITER '|';  

COPY in postgresql expects one of two data formats; since you did not specify 
CSV, in this case it's expecting the default postgresql format which requires 
the use of \ for escapes like \r, \n, \t, \\, \012 and for the default NULL 
specification of \N.

If you use this format it is therefore your responsibility to ensure that any 
literal \ characters in the data are escaped as \\, and that any literal 
appearance of the delimiter character or a newline is also escaped.

See https://www.postgresql.org/docs/current/sql-copy.html under "Text format".

--
Andrew (irc:RhodiumToad)


Disclaimer:  This message and the information contained herein is proprietary 
and confidential and subject to the Tech Mahindra policy statement, you may 
review the policy at http://www.techmahindra.com/Disclaimer.html 
<http://www.techmahindra.com/Disclaimer.html> externally 
http://tim.techmahindra.com/tim/disclaimer.html 
<http://tim.techmahindra.com/tim/disclaimer.html> internally within 
TechMahindra.

RE: Back Slash \ issue

[Guntry Vinod]

The postgre is running on Windows platform.

-Original Message-
From: Matthias Apitz  
Sent: Friday, May 3, 2019 2:32 PM
To: Guntry Vinod 
Cc: Andrew Gierth ; pgsql-gene...@postgresql.org; 
Adrian Klaver ; ravikris...@mail.com; Venkatamurali 
Krishna Gottuparthi ; Biswa Ranjan Dash 

Subject: Re: Back Slash \ issue

El día Friday, May 03, 2019 a las 08:45:02AM +, Guntry Vinod escribió:

> Hi Andrew,
> 
> So you mean to say we need to replace \\ in data. If so the data what we 
> receive is huge chunk(we cannot open in notepad++ also) .
> 
> ...

Hi Guntry,

What about piping the data on a Linux or any other UNIX (or even Cygwin on 
Windows) through a sed-Kommand to do the necessary changes, like

echo 'bla\foo' | sed 's/\\//'
bla\\foo

matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 
Public GnuPG key: http://www.unixarea.de/key.pub
70 years of NATO - 70 years of wars (Jugoslavia, Afghanistan, Syria, ...) and 
70 years of war preparation against Russia.  -- PEACE instead of NATO !


Disclaimer:  This message and the information contained herein is proprietary 
and confidential and subject to the Tech Mahindra policy statement, you may 
review the policy at http://www.techmahindra.com/Disclaimer.html 
<http://www.techmahindra.com/Disclaimer.html> externally 
http://tim.techmahindra.com/tim/disclaimer.html 
<http://tim.techmahindra.com/tim/disclaimer.html> internally within 
TechMahindra.

RE: Back Slash \ issue

[Guntry Vinod]

Hi Team,

Here we go. I will give the problem in more detail

Step 1:We get the dump from DB2 and this dump is flat file which can be csv,txt
Step2:There is table in PostGre where we are suppose  to upload the dump
Step3:We are using copy command to upload dump to the table using (COPY 
<> from 'C:\Data_Dump\ABC.txt' DELIMITER '|';  )
Step 4:In the above step we are using delimiter because the data is separated 
(:) in the flat which we have received from the flat file

Problem Statement:We are able to upload the data from the flat file which we 
got from the DB2 but few data the data consist of  " \".For example if the 
CustomerName is Vinod\G in the flat file ,we expect the same data in PostGre  
table for CustomerName as Vinod\G but we see VinodG(slash is missed).

Possible Solution: We can replace "\" with "\\" but if the file is in too large 
we cannot open it(we can replace if the file is medium or small)

Expectation: We need a command or utility which can upload the data as it is 
(for example if Vinod\G then we should see in PostGre as Vinod\G but not VinodG)

Hope Iam detail this time :-)

Regards,
Vinod


 

-Original Message-
From: Ravi Krishna  
Sent: Friday, May 3, 2019 8:43 PM
To: Michael Nolan 
Cc: Adrian Klaver ; Guntry Vinod 
; pgsql-gene...@postgresql.org; Venkatamurali 
Krishna Gottuparthi ; Biswa Ranjan Dash 

Subject: Re: Back Slash \ issue

> 
> I don't think we've seen enough representative data to know exactly what the 
> backslash is doing.  It doesn't appear to be an escape, based on the sole 
> example I've seen it appears to be a data separator between first name and 
> last name.
> 
> It seems increasingly likely to me that you might not be in a position where 
> the COPY command in PostgreSQL can handle loading the database, at least not 
> without some help.  You might have to write a program to clean up the data 
> and format it for PostgreSQL.  
> 
> I've spent a lot of time over the years migrating data from one platform to 
> another, you have to know EXACTLY what data you currently have and what 
> format you need it turned into before you can figure out how to do the 
> transformation.  
> --
> Mike Nolan

Fully agreed.  I have informed Guntry via email that he has to provide more 
information before we can help further.


Disclaimer:  This message and the information contained herein is proprietary 
and confidential and subject to the Tech Mahindra policy statement, you may 
review the policy at http://www.techmahindra.com/Disclaimer.html 
<http://www.techmahindra.com/Disclaimer.html> externally 
http://tim.techmahindra.com/tim/disclaimer.html 
<http://tim.techmahindra.com/tim/disclaimer.html> internally within 
TechMahindra.

RE: Back Slash \ issue

[Guntry Vinod]

Looping Nikhil and Shiva who are from Mainframe, DB2.

Nikhil/Shiva I am trying explain the problem to the team but there few 
questions which needs your intervention.

Regards,
Vinod

-Original Message-
From: Adrian Klaver  
Sent: Friday, May 3, 2019 9:47 PM
To: Guntry Vinod ; Ravi Krishna 
; Michael Nolan 
Cc: pgsql-gene...@postgresql.org; Venkatamurali Krishna Gottuparthi 
; Biswa Ranjan Dash 
Subject: Re: Back Slash \ issue

On 5/3/19 9:05 AM, Guntry Vinod wrote:
> Hi Team,
> 
> Here we go. I will give the problem in more detail
> 
> Step 1:We get the dump from DB2 and this dump is flat file which can 
> be csv,txt Step2:There is table in PostGre where we are suppose  to 
> upload the dump Step3:We are using copy command to upload dump to the 
> table using (COPY <> from 'C:\Data_Dump\ABC.txt' DELIMITER 
> '|';  )

Should have been in my previous post. The answer to whether Step 1 is text or 
CSV is important as that determines the way you use the COPY command above. As 
has been pointed out upstream using COPY assuming text(as you are doing above) 
on a CSV file will create issues.

> Step 4:In the above step we are using delimiter because the data is 
> separated (:) in the flat which we have received from the flat file
> 
> Problem Statement:We are able to upload the data from the flat file which we 
> got from the DB2 but few data the data consist of  " \".For example if the 
> CustomerName is Vinod\G in the flat file ,we expect the same data in PostGre  
> table for CustomerName as Vinod\G but we see VinodG(slash is missed).
> 
> Possible Solution: We can replace "\" with "\\" but if the file is in 
> too large we cannot open it(we can replace if the file is medium or 
> small)
> 
> Expectation: We need a command or utility which can upload the data as 
> it is (for example if Vinod\G then we should see in PostGre as Vinod\G 
> but not VinodG)
> 
> Hope Iam detail this time :-)
> 
> Regards,
> Vinod
> 
> 
>   
> 



--
Adrian Klaver
adrian.kla...@aklaver.com


Disclaimer:  This message and the information contained herein is proprietary 
and confidential and subject to the Tech Mahindra policy statement, you may 
review the policy at http://www.techmahindra.com/Disclaimer.html 
<http://www.techmahindra.com/Disclaimer.html> externally 
http://tim.techmahindra.com/tim/disclaimer.html 
<http://tim.techmahindra.com/tim/disclaimer.html> internally within 
TechMahindra.