Import from Oracle

Import from Oracle into Excel using CMD

We can take export into excel file directly from SQL Developer. Here is one more method, using which we can take export of sql output into excel. How to Implement? Create a .sql file, say TakeExport.sql & add the below code into it. SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF ; SPOOLoutput.xls; SELECT 'a' AS A FROM DUAL UNION ALL SELECT 'b' AS A FROM DUAL UNION ALL SELECT 'c' AS A FROM DUAL; -- sample query SPOOL OFF; EXIT; Create batch file & add the following code to call the TakeExport.sql sqlplus abcd/welcome1@CRMPRD @ TakeExport.sql

Fast Import from Oracle using Vbscript

I created this utility to export large amount of data (> 100000) from oracle. We can export from SQL Developer in excel sheet/csv, but to export large amount of data from SQL Developer takes significant amount of time. This is a lightweight utility, which can be useful to export data very quickly (approx. . .1k data in 20 minutes). Limitation:- Export data not shown properly, when LONG column available in output. Prerequisites:- Create DSN in odbcad32 Procedure:- Create folder “BULK_EXPORT” Prepare the SQL statement; store it in a text file “sqlQuery.sql” in the folder created in step 2. The sql statement should be saved in a single line. Create Init.vbs file & save the below text in it. This vbs file will call another vbs file “TakeExport_Notapad.vbs” using cscript. Create Init.vbs file & save the below text in it. This vbs file will call another vbs file “TakeExport_Notapad.vbs” using cscript. Set objShell = CreateObject("WScript.Shell") returnParam = objShell.Run ("C:\Windows\SysWOW64\cscript.exe TakeExport_Notapad.vbs", 0, true) Create a new vbs file named “TakeExport_Notapad.vbs” & add the below text into it. TakeExport    Function TakeExport On Error Resume Next sqlQuery = ".\sqlQuery.sql" traceFile = ".\EXPORT.txt"   connectionString = "Driver=(Oracle in OraHome92);" & "Data      Source=PROD_ENT;UID=ABCD;Password=welcome1;" Const ForReading  = 1, ForWritting = 2, ForAppending = 8 Set objFso    = CreateObject("Scripting.FileSystemObject") Set file  = objFso.OpenTextFile(sqlQuery, ForReading, True ) query         = file.ReadLine file.Close Set file  = objFso.OpenTextFile(traceFile, ForAppending, True ) file.WriteLine ("Export Start") file.Close Set connection   = CreateObject("ADODB.Connection") connection.Open connectionString Set rs = connection.Execute(query) input = "$"       Do Until rs.EOF   Set file   = objFso.OpenTextFile(traceFile, ForAppending, True )   output= ""   for i = 0  To rs.Fields.Count - 1       output = output & "" & rs.Fields.Item(i) & "" & input   Next   file.WriteLine (output)          file.Close   rs.MoveNext Loop Set file  = objFso.OpenTextFile(traceFile, ForAppending, True ) file.WriteLine ("Export End") file.Close Set connection = Nothing Set rs = Nothing   End Function