Linker IT Software
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
Query text files (Read 2812 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Query text files
01.10.08 at 20:46:52
Query text files
Text files can be queried using SQL in SQL*XL. I have a large text file I need to insert into Oracle. The text file contains over 200,000 rows.
The first step is to connect to the text file. In the connection dialog I used New Connection and I used the database connection wizard to create the connection. I selected the Text/CSV File "database type" and entered the directory where the text file is located. I noticed that if the file is in the root of the drive you need to type C:\
Two connections were successful on my machine, OLEDB with Microsoft Jet OLEDB 4 and an ODBC MSDASQL connection. I used the first: OLEDB with Jet. The connection is saved with user Admin. Note that no password is required. Just connect.
To find out how to use the text files I opened the SQL dialog and started the SQL editor. Opening the schemas, current schema I saw that the text file was listed as myfile#txt so apparently the driver replaced the .txt with #txt...
I used the SQL: select * from myfile#txt. I returned to SQL*XL and ran the query. The record fetching was quick, approximately 500-1000 rows per second.  
I terminated the fetch after a few thousand rows. I found out that the column was named as NoName. I wanted to only select the first 20 characters from each line so I changed the SQL to: select left(noname,20) from myfile#txt.  
Back to top

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