RE: creating an ascii text file

["Urbanek, Marty" <Marty_Urbanek@xxxxxxxxxxxx>]

Angela,

I know several people already posted solutions which are probably more
robust, but your question gave me an excuse to play with java. Here is a
small pgm that uses JDBC to copy the data from an AS/400 file to an IFS
stream file delimited like you were talking about. Maybe the additional
level of control from having your own pgm would be useful in your
application.

If you would like to try it:

Save the source below in an IFS file named MAKECSV.java
Start QShell: STRQSH
Change directory to the dir with MAKECSV.java
Compile: javac MAKECSV.java (case sensitive)
Run: java MAKECSV mylib.mydbfile csvout.txt userid userpwd

This will put the output file in the current directory, or you can specify a
complete path. Because it writes to IFS, you could probably use QNTC to
write the output directly to a NT file share somewhere, if you wanted to.

You can also run it from CL/command line:
RUNJVA CLASS(MAKECSV) +
PARM(mylib.mydbfile '/home/mydir/csvout.txt' userid userpwd) +
CLASSPATH('/ifspathtoMAKECSV.class')


// MAKECSV.java source starts here
// Make an AS/400 database file into Comma Separated Values in an IFS stream
file

import java.io.*;
import java.sql.*;

class MAKECSV {

public static void main(String args[]) {

    String url = "jdbc:db2://localhost";
    String query = "SELECT * FROM ";

    if (args.length != 4) {
        System.out.println("\nUsage: MAKECSV infile outfile userid
password\n");
        System.out.println("  infile   = LIBRARY.FILE");
        System.out.println("  outfile  = /ifspath/filename");
        System.out.println("  userid   = user profile with authority to
infile");
        System.out.println("  password = password for userid profile\n");
        System.exit(1);
    }

    // add lib/file to query statement
    query = query + args[0];

    try {
        Class.forName("com.ibm.db2.jdbc.app.DB2Driver");
        Connection con = DriverManager.getConnection( url, args[2], args[3]
);
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);

        writeResultSet(rs, args[1]);

        rs.close();
        stmt.close();
        con.close();
    }
    catch (SQLException ex) {
        while (ex != null) {
            System.out.println("SQL Exception: " + ex.getMessage());
            ex = ex.getNextException();
        }
    }
    catch (java.lang.Exception ex) {
        ex.printStackTrace();
    }

}  // end main


private static void writeResultSet (ResultSet rs, String filename)
                           throws SQLException {

    FileWriter out;
    String outstr = new String();
    int count = 0;

    try {
        out = new FileWriter(filename);

        int numCols = rs.getMetaData().getColumnCount();

        while (rs.next() ) {
            ++count;
            outstr = "";
            for (int i=1; i<=numCols; i++) {
                if (i > 1)
                    outstr = outstr + ",";
                if (rs.getMetaData().getColumnType(i) ==
java.sql.Types.CHAR) {
                    outstr = outstr + "'" + rs.getString(i).trim() + "'";
                }
                else {
                    outstr = outstr + rs.getString(i);
                }
            }

            outstr = outstr + "\r\n";
            out.write(outstr);

        } // end while

        System.out.println(count + " records were processed.");
        out.close();

    } catch (IOException e) {
        System.out.println("IO Error on output file: " + e);
        return;
    }

}  // end printResultSet


}  // end class MAKECSV
// MAKECSV.java source ends here

That's it.
-Marty


-----Original Message-----
From: Angela Wawrzaszek [mailto:awawrzaszek@nucorauburn.com]
Sent: Friday, November 30, 2001 1:48 PM
To: Midrange Mail List (E-mail)
Subject: creating an ascii text file


    I am trying to create an ascii text file from an as/400 file.   I have
tried using CA file transfer,  cpytoimpf,  and excel.   I need the file to
either have eachfield separated with a comma and text fields with single
quotes   OR   field delimiter is an * and no quotes on text fields.   The
cpytoimpf doesn't like the *   or the single quote as the string delimeter.
I would like to automate this process.  I am using the newly created file on
a Dunn & Brad Ram product.

Any help???

Thanks!

Angela Wawrzaszek
Nucor Steel Auburn INC.
Programmer/Analyst
(315) 258 4205
wawrzaszek@nucorauburn.com