Extract from table - send to mainframe

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extract from table - send to mainframe

Author

Topic

JeffT
Posting Yak Master

111 Posts

Posted - 2007-01-24 : 08:09:26

Hi,

I want to extract all fields from a table and ftp the output file to the mainframe. My problem is the columns in the extracted file do not line up, which is required by the mainframe program. Example column would be this column of last names:
Smith
Jones
Sullivan

How could I format the extracted data to look like this:
Smith
Jones
Sullivan

Thanks,
Jeff

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-24 : 08:12:53

You can use BCP or DTS to output the data from table to fix record length file and ftp to the mainframe.

JeffT
Posting Yak Master

111 Posts

Posted - 2007-01-24 : 08:14:48

Thanks very much khtan...I will try that...
Jeff

JeffT
Posting Yak Master

111 Posts

Posted - 2007-01-24 : 08:49:27

Hi khtan,
I'm looking at the BCP output options (-c, -k, -t, etc.). How would I specify that I want to output a fixed record length file ?
Thanks,
J

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-24 : 08:57:16

take a look at the bcp format file

JeffT
Posting Yak Master

111 Posts

Posted - 2007-01-24 : 10:32:40

OK, I created a format file:
bcp tblNPIData FORMAT dummy.txt -fxwlk.fmt -c -U%HDRUSER% -P%HDRPASSWORD% -S%HDRSERVER%
and then ran the bcp with the format file:
bcp tblNPIData out c:\out\Crosswalk.dat -b1000 -c -k -t~ -f xwlk.fmt -U%HDRUSER% -P%HDRPASSWORD% -S%HDRSERVER%
Unfortunately, I'm still getting output columns not lined up.
Thanks,
Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-24 : 10:46:28

post four format file

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JeffT
Posting Yak Master

111 Posts

Posted - 2007-01-24 : 10:50:09

Hi Brett,

Here its is:
8.0
21
1 SQLCHAR 0 12 "\t" 1 iNPIId ""
2 SQLCHAR 0 10 "\t" 2 INumber SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 9 "\t" 3 IRSTaxId SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 12 "\t" 4 iCompanyId ""
5 SQLCHAR 0 25 "\t" 5 vchTPID SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 10 "\t" 6 Tcode SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 10 "\t" 7 FedId SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "\t" 8 PartAB SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9 iProviderTypeID SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 255 "\t" 10 vchCompanyName SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 255 "\t" 11 vchAddress1 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 255 "\t" 12 vchAddress2 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 255 "\t" 13 vchCity SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 2 "\t" 14 State SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 5 "\t" 15 ZipCode SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 4 "\t" 16 ZipPlus4 SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 255 "\t" 17 Comments SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 5 "\t" 18 tiRecordStatus ""
19 SQLCHAR 0 3 "\t" 19 btComplete ""
20 SQLCHAR 0 24 "\t" 20 dtCreateDate ""
21 SQLCHAR 0 24 "\r\n" 21 dtUpdateDate ""

Thanks,
Jeff

JeffT
Posting Yak Master

111 Posts

Posted - 2007-01-24 : 12:46:37

Just an FYI, I was able to accomplish what I wanted using "osql" (see below):

osql -U%HDRUSER% -P%HDRPASSWORD% -S%HDRSERVER% -d%HDRDB% -h-1 -w1500 -Q"SET NOCOUNT ON SELECT * FROM tblData" > c:\out\Crosswalk.dat

Thanks to everyone for your help,
J

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources