Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to insert a string with '/' using COPY command from a text file

How to insert a string with '/' using COPY command from a text file

From
"shreedhar"
Date:
Hello All
 
 
 tblTemp (
catalogid INTEGER NOT NULL DEFAULT 0,
DirPath Varchar(250) NOT NULL DEFAULT '',
TypeName VARCHAR(100)  NOT NULL DEFAULT '', 
size FLOAT NOT  NULL DEFAULT 0, 
filedate datetime NOT NULL
) ;
 
Input text file
10|cdrom\games\themes\newyears1.zip|WinZip File (Folder)|729990|11/24/2001 01:41:32 AM
 
If I directly use Copy command
 
1) data is storing in table as following
 
catalogid     dirpath                                        typename                 size                    filedate
10    cdromgamesthemesnewyears1.zip         WinZip File            729990        2001-11-24 01:41:32
 
 
2) If change input as
 
10|cdrom/\games/\themes/\newyears1.zip|WinZip File (Folder)|729990|11/24/2001 01:41:32 AM
 
Output in table is
 
catalogid     dirpath                                typename                 size                    filedate
10    cdrom/games/    hemes                 WinZip File            729990               2001-11-24 01:41:32
        / ewyears1.zip                
 
I mean it is taking TAB and NEWLINE characters.
 
3) If change input as
 
10|cdrom/\games\\themes\\newyears1.zip|WinZip File (Folder)|729990|11/24/2001 01:41:32 AM
 
Output in table is
 
catalogid     dirpath                                typename                 size                    filedate
10    cdrom/games\themes\newyears1.zip               WinZip File            729990               2001-11-24 01:41:32
 
I mean '\' is appearing instead of '/'
 
My expected output is
catalogid     dirpath                                typename                 size                    filedate
10    cdrom/games/themes/newyears1.zip               WinZip File            729990               2001-11-24 01:41:32
 
How can I change my input file to get expected result.
 
REGARDS,
SREEDHAR                        

Re: How to insert a string with '/' using COPY command from a text file

From
Richard Huxton
Date:
On Thursday 10 Apr 2003 11:32 am, shreedhar wrote:
> Hello All
>
>
>  tblTemp (
> catalogid INTEGER NOT NULL DEFAULT 0,
> DirPath Varchar(250) NOT NULL DEFAULT '',
> TypeName VARCHAR(100)  NOT NULL DEFAULT '',
> size FLOAT NOT  NULL DEFAULT 0,
> filedate datetime NOT NULL
> ) ;
>
> Input text file
> 10|cdrom\games\themes\newyears1.zip|WinZip File (Folder)|729990|11/24/2001
> 01:41:32 AM
>
> If I directly use Copy command
>
> 1) data is storing in table as following
>
> catalogid     dirpath                                        typename
>           size                    filedate 10
> cdromgamesthemesnewyears1.zip         WinZip File            729990
> 2001-11-24 01:41:32

You probably want to escape the backslashes, so you have something like:

10|cdrom\\games\\themes\\newyears1.zip...

In your examples you seem to be using / (forward slash) rather than \
(back-slash). Some codes that might get you:

\t    tab
\r    carriage-return
\n    newline

So - anywhere you have backslash in your input make sure you double it.

--
  Richard Huxton