How to write schema.ini and why am I using Schema.ini in the folder of text file.

Hello All,

Trying to connect a text file as a database and query since I have 2.6 million records of data in a text file.

I understood that I should maintain schema.ini file in the folder which contains text files. I am unable to write the data in schema.ini nor understand what data to include and what format in the schema.ini.

Any help is highly appreciated.

1 answer

Hello,

to connect text file you don't have to use schema.ini, but then use standard connection string:

"Driver={Microsoft Text Driver (.txt;.csv)};Dbq=c:\txtFilesFolder;Extensions=asc,csv,tab,txt;" (https://www.connectionstrings.com/textfile/).

For identification fileds name i always use sql querry: "SELECT * FROM [Your_file.txt]", then i loop for fields name - VBA exemple :

Sub TestFile
   
    Dim RS as Object,CNN as String,SQL_Q as String,i as Interger

    CNN="Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq=c:\txtFilesFolder \;Extensions=asc,csv,tab,txt;"
    SQL_Q="SELECT * FROM [Your_file.txt]"
    Set RS=CreateObject("ADODB.RecordSet")
    RS.Open SQL_Q,CNN,2,4

    For i=1 to RS.Fields.count step 1
        Debug.Print RS.Fields(i-1).Name  // or diffrent place to print fields.name
    Next i
    RS.Close

    Set RS=Nothing

End Sub

If this dosent work create schema.ini in this folder by create new text file. In this file write:

[Your_file.txt]

Format=Delimited(;)

save and close file than change file extension to .ini. Delimited says what kind of separator is used in [Your_file.txt]