topic Re: FTP a file in Operating System - HP-UX

FTP a file

Oviwan — Wed, 12 Sep 2007 11:03:10 GMT

Hey Folks

We have a file on windows with this content:
F00092 2132.0F03B.....

we created this file with bcp of ms sql with this parameter DATAFILETYPE = 'widechar' because the ö,ä,ü etc...

If we ftp it to our HPUX box we get the following:
ascii or bin mode:
ÿþF00092 2132.0F.....

is there a way to remove these first two character "ÿþ" from this file? if I copy this characters to the shell I get this "^?~"

the size of the files are 20GB to 90GB. with vi we get Line too long...

other thing I tried:
copy the file then
dd if=/tmp/file1 of=/tmp/file2 bs=1 skip=2
to skip the first two bytes but this takes too long ~300MB/30min.

has anyone a fast way to do this?

Thanks in advance

Regards

Re: FTP a file

Geoff Wild — Wed, 12 Sep 2007 11:08:25 GMT

zip the file first on Windoze...then ftp as bin....

Rgds...Geoff

Re: FTP a file

Steven Schweda — Wed, 12 Sep 2007 15:09:33 GMT

> the size of the files are 20GB to 90GB.

> zip the file first on Windoze...then ftp
> as bin....

Released versions of Info-ZIP UnZip (5.52 is
current) can not cope with files bigger than
2GB. Unreleased versions

ftp://ftp.info-zip.org/pub/infozip/beta/

should do better, but it's not obvious to me
why Zip+UnZip would do any better than binary
FTP.

If you really are using binary FTP, and those
bytes appear at the destination, then I'd
suspect that those bytes are there at the
source. Do you have a low-level tool like a
UNIX "od" which you could use to see the real
data in the source file?

If those bytes are really in the source file,
then there may be data other than the first
two bytes which will also cause problems.

Re: FTP a file

Hein van den Heuvel — Wed, 12 Sep 2007 17:56:09 GMT

bcp (bulk copy) accepts a slew of options.

-w selects unicode characters, so that' 2 bytes per character. It that what you are ready to deal with?

Form the bcp man page:

http://msdn2.microsoft.com/en-us/library/ms188289.aspx

"Unicode character format data files follow the conventions for Unicode files. The first two bytes of the file are hexadecimal numbers, 0xFFFE. These bytes serve as byte-order marks, specifying whether the high-order byte is stored first or last in the file."

It seems to me that those 'funny' characters are supposed to be there for a proper Unicode file usage.

What do yo intent to do with the file once on HPUX?

If the hpux consuming program really can not deal with the MSB/LSB flag, then I suspect you want to look in the direction of Perl or a C program for a solution.
Perl support Unicode, but also supports 'binmode'.

With binmode you could read the file in say 2048 or 512 byte chunks and stagger the output by 2 bytes as needed, much like dd, but using larger steps through the data.

What record length is expected?
What record terminator expected?
What was the record select expression? All columns?

By default bcp will deliver cr-lf (\r\n) line ends and tab (\t) column seperators.

Be sure to do a $ od -x | head to get an impression of what is coming at you in detail.

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

Re: FTP a file

Hein van den Heuvel — Wed, 12 Sep 2007 18:01:31 GMT

Oh and btw... when mucking with this kind of stuff, do yourself a favor and make a small test case first.

As my teacher used to say... design allowing for your first solution to be thrown away... because you will.

In your case that probably means to re-execute a bcp and on the SELECT add a LIMIT clause to say 1000. Now transfer and try.
So much more manageable!

Cheers,
Hein.

Re: FTP a file

Oviwan — Thu, 13 Sep 2007 03:11:56 GMT

Hey Hein

the parameter of bcp
-t "" -r ""\n

I'm trying to load these files (2000) with sqlloader in an oracle db. and it works also for each row except for the first because this characters.

so I will load each file with sqlloader and after that I write the first line of each file to another file and remove this characters. after that I load this new file.

The problem is, that I can't edit a 20Gb file for example with vi.

what is the best way to redirect the first line of a file to an other file?

Thanks

Re: FTP a file

Hein van den Heuvel — Thu, 13 Sep 2007 06:28:48 GMT

Is there a good, strong reason you used as record terminator () ?
That's why 'vi' fails!
Why not accept the default (\r\n) or specify just the unix standard newline (\n = linefeed )

In perl you can specify the line terminator to be , but not in vi. So maybe this will work. (untested)

perl -pe 'BEGIN {$/=} $_ = substr ($_,2)} old > new

This might work on a small file but fail on a large one due to 'slurp'ing the file. If so, use read/sysread to stomp through the file in chunks, copying as you go.

hth,
Hein.

Re: FTP a file

rajdev — Thu, 13 Sep 2007 06:31:13 GMT

Hi Oviwan,

have u tried using sed/head/cut etc
I am not sure if it works for > 20GB files , otherwise you have to use split command to split into multiple smaller size files.

>>>>> what is the best way to redirect the >>>>> first line of a file to an other file?

you can try this :

### check if u have enough space

head -1 file | cut -c 3- > file.line1
sed '1d' file > file.remaining

you can use these files to load to the database.

Regards,

Rajdev

Re: FTP a file

Hein van den Heuvel — Thu, 13 Sep 2007 06:35:26 GMT

More thoughts....

Maybe you do want the \r\n dos default terminator to keep it a two-byte sequence. Just tell sqlloader?

>>> and it works also for each row except for the first because this characters.

if all but the first line is there, then why not fix the database entry for that first line?

You could extract just enough to have the first record using for example : $dd -count=1 -bs=512 if=export of=tmp
Then edit this small file and sqlload the missing record?

Hein.

Re: FTP a file

Oviwan — Thu, 13 Sep 2007 08:51:23 GMT

what I'm trying to do is to migrate a ms sql db to oracle.

I export the tables with bcp (with the -w parameter and -r "") of mssql to a *.dat file.

and create the *.ctl files with the oracle migration workbench. the record separator is automatically "". I could edit the files to change it, but this are over 2000 files...

now I have the ctl and dat files for sqlloader.

In the dat files are the two first bytes these ugly characters. so the sqlldr process fails... if I delete this chars with vi and do run sqlldr then the Ã¶ Ã¤ Ã¼ char converts to a Â¿ sign. when I open the dat file with vi i see Ã¶ Ã¤ Ã¼ .

does someone have experience with this kind of migration?

Re: FTP a file

Oviwan — Thu, 13 Sep 2007 11:33:44 GMT

Ok now it works with Ã¶ Ã¤ Ã¼ (vowel mutation). I set
export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

But the problem with the first two bytes still exists.

I can redirect the first line to an other file:
# sed -n "1p" file1 >> file2

But how can I delete the first line of a file and replace this line with nothing so that there is no blank line at the beginning of the file.

Summary.
the second line become the first line...

Re: FTP a file

Hein van den Heuvel — Thu, 13 Sep 2007 12:20:47 GMT

Well, if that first sed worked to get the first line, then the next sed might get the rest:

$ sed -n '2,$p' all > all-but-one

fwiw,
Hein.

Re: FTP a file

rajdev — Fri, 14 Sep 2007 00:21:02 GMT

Hi,

answering to your last query :

>>>>>>>>>But how can I delete the first line of a file and replace this line with nothing so that there is no blank line at the beginning of the file.

Just do

#sed '1d' file > file2

Regards

Re: FTP a file

Oviwan — Fri, 14 Sep 2007 06:52:53 GMT

It seems to work now, I tested it with a 10Gb file.

instead of
dos2ux file1 > file2 #didn't works for huge files
used:
sed 's/^M$//' file1 > file2

then for first line and rest:
sed -n '1p' file2 > first_line
sed '1d' > file2 > all

And in the ctl file for sqlloader is set \r\n as record separator instead of \n

thanks for helping me

Regards