Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
SQL*XL processing large text files (Read 2477 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
SQL*XL processing large text files
12.05.10 at 09:28:12
 
SQL*XL processing large text files
 
SQL*XL can be very handy when work with text files. SQL*XL can 'connect' to a directory and all .txt files in that directory will become 'tables' that you can query.
 
Today I have 60 large text files that I need to analyse in Excel. The files are large, each containing some 20,000 rows and being just over 1MB in size. I want these files to be imported in Excel showing a line per row. In column A the first file should show and in the next column the next file, and so on.
 
To do this I will use SQL*XL's capabilities to use SQL on text files.
 

  • Rename files to .txt
    First I need to convert my files to have the .txt suffix. The files I need to work on are called as follows:
     
    rtbbb1010.txt
    rtbbb1100.txt
    rtbbbhhhh.txt
    rtbbz0101.txt
    rtbbz0110.txt
    rtbbz1010.txt
    rtbbz1100.txt
    rtbbzhhhh.txt
    ...
     
    I use the MSDOS ren command for this.  
    My files are originally named .out so I run the command: ren *.out *.txt
     
    Now the files are named .out:
     
    rtbbb1010.out
    rtbbb1100.out
    rtbbbhhhh.out
    rtbbz0101.out
    rtbbz0110.out
    rtbbz1010.out
    rtbbz1100.out
    rtbbzhhhh.out
    ...
     
  • Connect to the directory
    In SQL*XL make a new database connection. Use the Connection wizard to connect to text/csv files. The wizard will ask you to enter the directory where the files are stored. No password is needed and you can keep the default username.  
     
  • Retrieve file list
    Once connected to the database, actually you are connected to the directory containing the .txt files, we need the file listing. The files are regarded as tables.
     
    Open the SQL dialog and then open the SQL editor. Show the tables by expanding the tables node. Keep the tables node selected, right click and choose select.
    SQL*XL will generate the select commands for all the tables, or files actually.
     
    select * from rtbbb1010#txt;
    select * from rtbbb1100#txt;
    select * from rtbbbhhhh#txt;
    select * from rtbbz0101#txt;
    select * from rtbbz0110#txt;
    select * from rtbbz1010#txt;
    select * from rtbbz1100#txt;
    select * from rtbbzhhhh#txt;
    ...
     
    Note that the . in the file name is replaced with a #. SQL*XL does this automatically and it is needed to reference the file as a table.
     
  • Specify an output target to the select statements
    I need each file to be stored in a separate column. If I would run these commands then all the results would be placed in a single column. I am using the to clause for the select statements to direct the output automatically to the correct column:
     
    select * from rtbbb1010#txt to A1;
    select * from rtbbb1100#txt to B1;
    select * from rtbbbhhhh#txt to C1;
    select * from rtbbz0101#txt to D1;
    select * from rtbbz0110#txt to E1;
    select * from rtbbz1010#txt to F1;
    select * from rtbbz1100#txt to G1;
    select * from rtbbzhhhh#txt to H1;
    ...
     
    If you find this too much typing you can copy the select statements to Excel and use the concatenate function to add the to clause. The addresses can be generated using the cell formula: =cell(address,A1) returns $A$1.
     
  • Retrieve the data (sit back and relax)
    To retrieve the data simply run the commands. If you have typed the commands in the SQL dialog just run the commands. When you typed the commands in the spreadsheet, select all the cells that contain the SQL statements and open the SQL dialog. Select the option that each cell contains a separate SQL statement.
     
    The resultset options dialog will show. I am setting the options for maximum speed. I have unselected column headers, formatting, autoresize and the SQL comment.
     
    Press OK for All and SQL*XL will retrieve the data. It seems to take approximately 20 seconds per file. Time for a well deserved coffee!
Back to top
 
« Last Edit: 12.05.10 at 09:42:32 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1