in my txt file, there are 3 columns separated by space. In each column, data length is the same. It's like below -
OQ3900097 9383722662 2
OQ3900098 9383722663 2
OQ3900099 9383722664 2
In VB6, can we import such a text file into Access 2000 database directly (without replacing space into comma)?
This is pretty easy, and there is no reason to thunk over into the Desktop Text ODBC Driver because Jet 4.0 has a Text Installable ISAM that can do the job.
Examples: Append text to existing table, import text as new table.
Private Sub cmdAppend_Click()
Dim lngRows As Long
conDB.Execute _
"INSERT INTO ExistingTable SELECT * " _
& "FROM [Import.txt] IN '" & App.Path & "' 'TEXT;'", _
lngRows, _
adCmdText Or adExecuteNoRecords
MsgBox CStr(lngRows) & " rows appended to ExistingTable."
End Sub
Private Sub cmdImport_Click()
Dim lngRows As Long
conDB.Execute _
"SELECT * INTO NewTable " _
& "FROM [Import.txt] IN '" & App.Path & "' 'TEXT;'", _
lngRows, _
adCmdText Or adExecuteNoRecords
MsgBox CStr(lngRows) & " rows imported as NewTable."
End Sub
The key to making this work is to have a file named Schema.ini in the same folder as the text file (in this case I'm just using the App.Path folder for demonstration):
[Import.txt]
Format = Delimited( )
TextDelimiter = none
ColNameHeader = False
MaxScanRows = 0
Col1="Code" Text Width 9
Col2="Sequence" Text Width 10
Col3="Type" Integer Width 1
Above I made up field names and data types, which in a real case would match those in your database. Be sure to note the single blank space between the parentheses which will be the delimiting character used.
If you have several text files in the same folder to import just add additional INI sections to Schema.ini as needed.
Your program could even write such a Schema.ini to the folder dynamically if required, inserting the necessary text file name, list of fields and their names, type, sizes, etc.
I've done this before directly in Access by using the Get External data and setting up a linked table.
Otherwise it's fairly easy to use the ODBC text driver or directly parse it and import via the data connection
