Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Posts
    1

    Question Unanswered: CSV exporting with Oracle and VB??

    Hi!
    I was just wondering if anyone could point me in the right direction on CSV files? I want to export csv files containing sql*plus query results made in Visual Basic, and using Oracle for a database. If anyone can help me out at all, that would be fantastic!!

    Thankyou for your time and assistance

    Matt from infs3801gA

  2. #2
    Join Date
    Dec 2001
    Location
    Paris
    Posts
    5

    Re: CSV exporting with Oracle and VB??

    Hi,

    I'm not sure to well understand that you want to do, but anyway:

    1. If you want to generate the CSV from Oracle with your VB code, just insert ';' or ',' between each field of your recordset/resultset. You'll then be able to open it with Excel for instance.

    2. I guess the tur question is how to load the CSV into Oracle !? ...Here is some informations :
    You will need -at least- a control file (.ctl) and a CSV ! You can also create a batch to launch the import ... for example:

    a.code for the batch (importData.cmd) :
    sqlldr80 userid=usr/pwd@database control='importData.ctl' log='importData.log'
    this will connect to the database with right credentials and load data from the CTL file. A report status will be log ... in the LOG file.

    b. code for the control (importData.ctl) :
    Here you can use the full Oracle SQL loader power ! (a book treats that in the Oracle documentation), but for example :
    Code:
    OPTIONS (BINDSIZE=655350, SKIP=1)
    LOAD DATA
    INFILE 'myData.csv'
    BADFILE 'myData.bad'
    TRUNCATE
    INTO TABLE MYDATATABLE
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
      COLNAME1  INTEGER EXTERNAL,
      COLNAME2  CHAR "LTRIM(RTRIM(REPLACE(:COLNAME2,'''','\')))",
      COLNAME3  DECIMAL EXTERNAL NULLIF COLNAME3=BLANKS,
      COLNAME4  CHAR(2000) NULLIF COLNAME4=BLANKS "LTRIM(RTRIM(:COLNAME4))",
      COLNAME5  "REPLACE(DECODE(:COLNAME1,NULL,:COLNAME2||'_'||:COLNAME3,:COLNAME4||'_'||:COLNAME3||'_'||:COLNAME3),' ','')"
    )
    
    this sample do the following:
    -open the myData.csv file with a controlled buffer size (bindsize) and skip the first line (it is supposed to be a title line)
    -datas are loaded in the MYDATATABLE Oracle table which is first truncated.
    -It loads data separated ',' and eventually enclosed in "
    -the bad datas are rejected in myData.bad file (txt format)
    -you must then indicate the column to use (treated in the order of the column from the csv) and that kind of data/treatments are expected ...

    There are many other options with CTLs files ... check the Oracle docs.

    Hope it will help, because it costed me some time
    Fabrice.

    Originally posted by infs3801gA
    Hi!
    I was just wondering if anyone could point me in the right direction on CSV files? I want to export csv files containing sql*plus query results made in Visual Basic, and using Oracle for a database. If anyone can help me out at all, that would be fantastic!!

    Thankyou for your time and assistance

    Matt from infs3801gA

  3. #3
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    31
    If you want to create a *.csv file that will be opened by Excel but delivered by a webpage.. then an interesting bug in how IE handles the csv extensions arises.

    IE will recognise that csv is handled by Excel, but will not tell Excel that it is a csv file, so Excel doesn't know to parse it by comma delimeters.

    If this is the scenario you are trying to fulfill, then use a csv extension (so that IE passes it to Excel), but delimit with tabs and not comma's. Tabs are natively recognised as a delimiter by Excel and this means that when loaded Excel will instantly create the appropriate columns for you.
    My homepage:
    http://www.buro9.com/
    My work:
    http://www.btopenworld.com/
    http://www.officialfootballsites.co.uk/
    http://www.jeepster.co.uk/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •