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
Use SQL for text files (Read 2395 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Use SQL for text files
01.10.08 at 20:48:07
 
Use SQL for text files
 
Text files can be accessed with SQL using the SQL*XL software. My experience to connect to and access a large text file can be read here:
 
It is interesting to investigate the SQL that can be used to work with the text file. This may be specific tot he driver I chose to use: OLEDB with Microsoft Jet OLEDB 4.
 
To only fetch the first 100 rows I used:
SELECT top 100 * FROM myfile#txt
 
To filter certain lines I used the following SQL:
SELECT top 100 * FROM myfile#txt where noname not like 'D%'  and noname not like 'C%' and noname not like '---%'
I do not want to retrieve lines starting with a D or with a C or lines staring with ---
 
The instr function can be used to find substrings.
The left function can be used to get a substring from the field.
In my file, data fields are separated by a pipe symbol (|). To only get the first field I used:
SELECT top left(noname,instr(1,noname, '|')-1) FROM myfile#txt
 
To create a new text file (a new table) you can use a create table statement:
CREATE TABLE newfile.txt(   abc TEXT(5) )
 
You can also use the multiple rows insert feature to insert rows into a text file.
Back to top
 
« Last Edit: 02.10.08 at 08:43:44 by Gerrit-Jan Linker »  

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