Data Transfer from Text file to Excel & MS Access table

[VBFnewcomer]

I just learned how to copy data in a ADO recordset into Excel File using this link by Si_the_geek.
I also learned some Textfile manipulations from the links of dee-u & Pengate.

Thank you everyone.

Now I am wondering how to transfer the data
i) from a textfile (which is having ":" as delimiter) to access table
ii) from a textfile (which is having ":" as delimiter) to Excel File
ii) from a Excel file to an MS Access Table

I tried using the below code. No data was transferred. So its obviously incorrect

Dim fNo As Integer
    Dim sTemp As String
    fNo = FreeFile
    
    Open app.Path & "\Text1.txt" For Input As fNo
        Do While Not EOF(fNo)
            Line Input #fNo, sTemp
        Loop
        oSheet.Range("A1").Value = sTemp
        oWorkbook.SaveAs App.Path & "\TestBook.xls"
    Close fNo

I request the members show me how to do the above or point to some tutorial.
Thankyou.

[leinad31]

Use a standard delimiter (either a comma to create CSV, or a tab to create tab-delimited file) so you can use Jet database engine to extract records from file and insert into Access as a batch (rather than executing insert query per record).
http://www.vbforums.com/showthread.p...sert+csv+parse

[VBFnewcomer]

sorry leinad31 I am not able to follow the coding in your link. That appears to be more specific to the text file attached there. Can you show me a simpler coding. OK I will go for vbTab as delimiter.Let us go step by step. First show me how create CommaSV/TabSV file.

[leinad31]

You can use Replace() fnuction to change all instances of colon to tab/comma.

Then try connecting to a CSV or tab-delimited file. Research appropriate connection string. http://www.carlprothman.net/Default.aspx?tabid=81

Once you have the connection, you just execute the query.

[VBFnewcomer]

I have come this much

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
 
Private Sub Command1_Click()
    'On Error Resume Next
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM Text3.txt", conn, adOpenStatic, adLockOptimistic, adCmdText
        If rs.RecordCount > 0 Then
            For i = 0 To rs.RecordCount - 1
                MsgBox rs.Fields(0).Name & " --> " & rs.Fields(0)
                rs.MoveNext
            Next i
        End If
    rs.Close
 
End Sub
 
Private Sub Form_Load()
    Set conn = New ADODB.Connection
    conn.Open _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=" & App.Path & "\;" & _
        "Extensions=asc,csv,tab,txt"
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

but when I change to this line

rs.Open "SELECT * FROM Text3.txt WHERE value1 =20", conn, adOpenStatic, adLockOptimistic, adCmdText

I get err msg ODBC driver does not support the requested properties.
I have also attached the sample data I am using.

[leinad31]

Other than doing an rs.MoveNext when your iterating the column names and not row data... sorry I am unable to simulate your error (check if you have filename right)... it works fine here.

Option Explicit

Private oConn As ADODB.Connection

Private Sub Form_Load()
Dim rs As ADODB.Recordset
Dim i As Long

   Set oConn = New ADODB.Connection
   'oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=c:\temp\;" & _
      "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
  
   oConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
      "Dbq=c:\temp\;" & _
      "Extensions=asc,csv,tab,txt"
'-----
   'Set rs = oConn.Execute("SELECT * FROM text3.txt WHERE Value1 = 20 ")
    
   Set rs = New ADODB.Recordset
   rs.Open "SELECT * FROM text3.txt WHERE Value1 = 20 ", oConn, adOpenStatic, adLockOptimistic, adCmdText
'-----
   For i = 0 To rs.Fields.Count - 1
      Text1.Text = Text1.Text & vbTab & rs.Fields(i).Name
   Next
   Text1.Text = Text1.Text & vbCrLf & rs.GetString(, , vbTab, vbCrLf, "")
End Sub

[VBFnewcomer]

I had the code revised (minor) and the criteria is working

Dim oExcel As Object
Dim oWorkbook As Object
Dim oSheet As Object
Dim oRangeTargetCell As Object
 
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
 
Dim lngRcdsAffected As Long
 
Private Sub Command1_Click()
    lngRcdsAffected = 0
    Set oExcel = CreateObject("Excel.Application")
    Set oWorkbook = oExcel.Workbooks.Add
    Set oSheet = oWorkbook.Worksheets(1)
        GetTextFileData "SELECT * FROM Text3.txt WHERE value1 = 2  ORDER BY [Grand Total] DESC", "F:\Tips for VB Access etc\VB\Export Txt to Excel\"
        oWorkbook.SaveAs App.Path & "\Book" & Format(Date, "d_M_yyyy") & ".xls"
    Set oSheet = Nothing
    Set oWorkbook = Nothing
    oExcel.Quit
    Set oExcel = Nothing
    MsgBox "Export Sucessful" & vbCrLf & lngRcdsAffected & " Records Exported", vbInformation, App.Title
End Sub
 
Private Sub GetTextFileData(strSQL As String, strFolder As String)
 
    Set conn = New ADODB.Connection
 
    conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=" & strFolder & ";" & _
        "Extensions=asc,csv,tab,txt;"
 
    If conn.State <> adStateOpen Then Exit Sub
    Set rs = New ADODB.Recordset
 
    MsgBox strSQL
    rs.Open strSQL, conn, adOpenStatic, adLockOptimistic, adCmdText 'adOpenForwardOnly, adLockReadOnly, adCmdText
 
    If rs.State <> adStateOpen Then
        conn.Close
        Set conn = Nothing
        Exit Sub
    End If
    
    ' the field headings
    Dim i As Integer
    For i = 0 To rs.Fields.Count - 1
        oSheet.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    oSheet.Range("A2").CopyFromRecordset rs
    
    oSheet.Range("A1:F1").Font.Bold = True
    oSheet.Columns.EntireColumn.Autofit
    
    lngRcdsAffected = rs.RecordCount
    
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

But another problem is noticed. If the data in the txt file is CSV then the export works properly, but if the data is TSV (Tab seperated) all values are loaded into a single column see my attachment Is there something more to be done in case of TSVs.

[leinad31]

Try the Jet OLEDB connection string. My suggestion was primarily for file to access transfer using INSERT INTO. Also, Excel can open tab-delimited files by default (no need to go through recordset) so no need to convert to xls unless you plan to include other features of Excel such as functions.

[VBFnewcomer]

Try the Jet OLEDB connection string.

instead of this

  conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=" & strFolder & ";" & _
        "Extensions=asc,csv,tab,txt;"

the usual Microsoft.Jet.OLEDB.4.0

[VBFnewcomer]

Excel can open tab-delimited files by default (no need to go through recordset) so no need to convert to xls

can you explain more. Or preferably post the codes using my earlier ex.

[leinad31]

If your using WinXP simply right click file then select Open With... then use Excel to open tab-delimited file. Which shows it can be opened using excel without having to setup a recordset (which is similar to using the data import feature of excel).

[VBFnewcomer]

Which shows it can be opened using excel without having to setup a recordset

Ok but how do I do it through code

[leinad31]

You will need to change name of first column from ID (case sensitive) http://support.microsoft.com/kb/215591

Input: sample.txt

Option Explicit

Private Sub Form_Load()
Dim oExcel As Object
Dim oWorkbook As Object

   Set oExcel = CreateObject("Excel.Application")
   Set oWorkbook = oExcel.Workbooks.Open("c:\temp\sample.txt", , , , , , , , 1)  '1 indicates delimiter is tab
   oWorkbook.SaveAs "c:\temp\sample", 1   '1 indicates xls file type
   oWorkbook.Close
   oExcel.Quit
   Set oWorkbook = Nothing
   Set oExcel = Nothing
   Unload Me
End Sub

Output: sample.xls

Additional reference http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

[VBFnewcomer]

me too was reading the same links
http://support.microsoft.com/kb/215591
http://support.microsoft.com/kb/247412
but these do not allow to retrieve records from the txtfile based on some criteria. am I correct or is it possible?

[leinad31]

Yes, tab-delimited file is loaded into the worksheet as it is. If you want functionality of WHERE clause then I guess the CopyFromRecordset() implementation is the way to go.

[VBFnewcomer]

then how to overcome prblm reported in #7

[leinad31]

For tab delimited files, you need to create schema.ini file located in same folder as textfile being read by Jet DB engine. Ini file provides format info (delimiter used) of textfiles http://msdn2.microsoft.com/en-us/library/ms709353.aspx