Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

BCP Format files

5 posts in Windows NT Last posting was on 2000-06-30 13:20:07.0Z
Scott Landers Posted on 2000-05-24 21:29:15.0Z
From: "Scott Landers" <scott770@bellsouth.net>
Subject: BCP Format files
Date: Wed, 24 May 2000 16:29:15 -0500
Lines: 15
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2615.200
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Message-ID: <oqVxwgcx$GA.261@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 38.216.214.75
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2114
Article PK: 1089522

Hello,

Is there a way to have ASE 11.5 generate a format file for a given table. I
have a database with 100+ tables and I am trying to keep from having to
create these manually. I have an Ascii dump file from each table in the
database that does not contain any kind of field delimiters. It only
contains record delimiters. Does anyone have any suggestions on how to get
the data into the tables?

Your help is appreciated!

Scott Landers
Solutions Plus, Inc.


sverre tvedt Posted on 2000-06-30 13:20:07.0Z
From: "sverre tvedt" <a@b>
References: <oqVxwgcx$GA.261@forums.sybase.com>
Subject: Re: BCP Format files
Date: Fri, 30 Jun 2000 15:20:07 +0200
Lines: 58
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <W$vEBcp4$GA.204@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: ti01a72-0061.dialup.online.no 130.67.39.61
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1953
Article PK: 1089369


Scott Landers <scott770@bellsouth.net> wrote in message
news:oqVxwgcx$GA.261@forums.sybase.com...
> Hello,
>
> Is there a way to have ASE 11.5 generate a format file for a given table.
I
> have a database with 100+ tables and I am trying to keep from having to
> create these manually. I have an Ascii dump file from each table in the
> database that does not contain any kind of field delimiters. It only
> contains record delimiters. Does anyone have any suggestions on how to
get
> the data into the tables?
>
> Your help is appreciated!
>
> Scott Landers
> Solutions Plus, Inc.
>
>

I had the same problem myself. The only solution I have found is something
like this:

NT command script:

echo 10.0 > MyTable.fmt
isql -Sservername -Uuser -Pxxx -iSmartScript.sql >> MyTable.fmt


Now, what is the content of Smartscript.sql ?

use mybase
go
set nocount on
go
select count(*) from sysobjects o,syscolumns c,systypes t
where o.Name='Mytable' and o.id=c.id and o.type and c.usertype = t.usertype
go
select <formatted stuff> from sysobjects o,syscolumns c,systypes t
where o.Name='Mytable' and o.id=c.id and o.type and c.usertype = t.usertype
go

This is only a sketch that needs further elaboration. You will have to pipe
the result into a utility that removes headers and dashes from the result
sets. The format for the fmt file is described in the BCP manual, and that
will decide the content of <formatted stuff> and it should in principle be
possible to derive it from the suggested selection. You can also establish
the Smartscript.sql file from the NT command script through a series of
'echo' statements and in this way you can transfer parameters from the
command level into the level of 'Smartscript.sql'. Tricky, but once you have
made it will work on all tables with exacty the format you want.

;-) sverre


"Mark A. Parsons" <pegasys Posted on 2000-05-25 06:33:58.0Z
Message-ID: <392CC955.B3489519@_internet.co.nz>
Date: Thu, 25 May 2000 18:33:58 +1200
From: "Mark A. Parsons" <pegasys@_internet.co.nz>
Organization: Pegasys (2000) Limited
X-Mailer: Mozilla 4.72 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: BCP Format files
References: <oqVxwgcx$GA.261@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 30
NNTP-Posting-Host: p292.ipa1-n8-16.iconz.net.nz 210.48.25.36
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2110
Article PK: 1089516


> Is there a way to have ASE 11.5 generate a format file for a given table. I
> have a database with 100+ tables and I am trying to keep from having to
> create these manually. I have an Ascii dump file from each table in the
> database that does not contain any kind of field delimiters. It only
> contains record delimiters. Does anyone have any suggestions on how to get
> the data into the tables?

Just curious ... no field delimiters, huh? So these files are based on fixed
length records, right?

A couple ideas ...

1) Massage the ascii dump files (with a shell script?) and insert field
delimiters.

or

2) Get copies of a few format files (generate them for a couple of your ascii
dump files), figure out the format of the file, and then write SQL to generate
the files ... going against the system files (e.g., sysobjects, syscolumns,
etc.) to get the data you'll need to build the format files.

If #2 leaves you in the dark just post back ... with the results of sp_help for
a given table as well as the associated format file.

--
Mark A. Parsons

Iron Horse, Inc. iron_horse@compuserve.com
Pegasys (200), Ltd pegasys@internet.co.nz


Jason Froebe Posted on 2000-05-24 21:38:11.0Z
Message-ID: <392C4BC2.C9D8090E@sybase.com>
Date: Wed, 24 May 2000 16:38:11 -0500
From: Jason Froebe <jfroebe@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.15 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Scott Landers <scott770@bellsouth.net>
Subject: Re: BCP Format files
References: <oqVxwgcx$GA.261@forums.sybase.com>
Content-Type: multipart/mixed; boundary="------------610172920D6AEB41AF93C5D7"
Newsgroups: sybase.public.sqlserver.nt
Lines: 39
NNTP-Posting-Host: jfroebe-desktop.sybase.com 157.133.46.52
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2113
Article PK: 1089526


Scott Landers wrote:

> Hello,
>
> Is there a way to have ASE 11.5 generate a format file for a given table. I
> have a database with 100+ tables and I am trying to keep from having to
> create these manually. I have an Ascii dump file from each table in the
> database that does not contain any kind of field delimiters. It only
> contains record delimiters. Does anyone have any suggestions on how to get
> the data into the tables?
>
> Your help is appreciated!
>
> Scott Landers
> Solutions Plus, Inc.

Hi Scott,

Take a look at the utilities "defncopy" and "bcp". They are described in
detail in "Utility Programs for Windows & Windows NT" and "Utility Programs for
Unix".

defncopy: extracts/imports object definitions (tables, stored procedures,
etc.)
bcp: extracts/imports the actual data

Jason


Download VCard jfroebe.vcf


"Mark A. Parsons" <pegasys Posted on 2000-05-25 06:29:58.0Z
Message-ID: <392CC865.C1EB1202@_internet.co.nz>
Date: Thu, 25 May 2000 18:29:58 +1200
From: "Mark A. Parsons" <pegasys@_internet.co.nz>
Organization: Pegasys (2000) Limited
X-Mailer: Mozilla 4.72 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: BCP Format files
References: <oqVxwgcx$GA.261@forums.sybase.com> <392C4BC2.C9D8090E@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 9
NNTP-Posting-Host: p292.ipa1-n8-16.iconz.net.nz 210.48.25.36
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2111
Article PK: 1089513


Jason Froebe wrote:

> defncopy: extracts/imports object definitions (tables, stored procedures,
> etc.)

That's cool! When did defncopy start extracting table definitions?

Heading back to RTF(What's New)M ...

--
Mark A. Parsons

Iron Horse, Inc. iron_horse@compuserve.com
Pegasys (200), Ltd pegasys@internet.co.nz