Read Tab Delimited Text file using a Schema.ini into an Access database (ADO)
i am trying to read a Tab-Delimited Text file using a Schema.ini into an Access database (ADO). however, i dont know how to include the Schema.ini file in ADO.
the Schema.ini file has: [Sample.txt] Format=TabDelimited
code: Sub ADOOpenTextFileImport1()
On Error GoTo ErrHandler
Dim adoCn As ADODB.Connection Dim strCn As String Dim strSQL As String
lblAction.Caption = "ADO Import 1..."
strCn = "Driver=" & txtDriverISAM.Text & ";" & _ "DBQ=" & App.Path & ";" & _ "DefaultDir=" & App.Path & ";" & _ "Uid=Admin;Pwd=;Extensions=asc,csv,tab,txt;"
Set adoCn = New ADODB.Connection adoCn.Open strCn
If chkCreateTbl.Value = 1 Then 'Use this if you do not already have a table created in Access. 'Creates and appends the data in one step. strSQL = "SELECT * INTO [" & txtTable.Text & "] IN '" & App.Path & "\" & txtDatabase.Text & "'" strSQL = strSQL & "FROM " & txtFile.Text adoCn.Execute strSQL Else 'Delete data before importing - use if necessary. strSQL = "DELETE FROM [" & txtTable.Text & "] IN '" & App.Path & "\" & txtDatabase.Text & "'" adoCn.Execute strSQL 'Append data to Access table. strSQL = "INSERT INTO [" & txtTable.Text & "] IN '" & App.Path & "\" & txtDatabase.Text & "'" strSQL = strSQL & "SELECT * FROM " & txtFile.Text adoCn.Execute strSQL End If
ErrHandler: lblAction.Caption = "ADO Import 1 - Error." MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
ExitSub: lblAction.Caption = "Complete..." adoCn.Close Set adoCn = Nothing
Where do i include the Schema.ini file in this code?
right now it does not seem to read the text file correctly as some column headers are getting clubbed together along with their data.
FMT=TabDelimited does not work.
unable to fetch data that is tab delimited from text file using OleDbConnection string as below using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + @";Extended Properties='text;HDR=YES;FMT=TabDelimited';"))0 votes1 answerSumit Mogareon Aug 10, 2017
Can I export to a text file via a query without wrapping quotes around my strings?
Hi, I'm trying to do an export from access into a text file via a query select CustomerName into [Text;FMT=TabDelimited;HDR=NO;DATABASE=C:\Temp\;].CustomerList.txt from Customer0 votes0 answersJamie Alfordon Apr 22, 2016
Changing data types in a column. Numeric to Alphanumeric
I'm trying to read in a .csv file and I'm running into a problem where the "type" of data changes in a column, so most of the data is numeric, but then some values are text. For example: LocationCode0 votes0 answersBrian Fasanoon Oct 28, 2015
Custom delimited text file
if i have multiple custom delimited text file like (comma,vertical bar etc..). i have tried like "Format" = "Delimited(;),(|)" but failed.. Delimited columns Provider=Microsoft.Jet.OLEDB.4.0;Data Sou-1 votes0 answersGutha Shiva Karan Reddyon Jan 21, 2013
- Load more posts