Data Transfer from Text file to Excel & MS Access table
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. :thumb: :wave:
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 :eek:
Text to Excel Code:
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. :wave: :afrog:
Re: Data Transfer from Text file to Excel & MS Access table
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
Re: Data Transfer from Text file to Excel & MS Access table
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.
Re: Data Transfer from Text file to Excel & MS Access table
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.
1 Attachment(s)
Re: Data Transfer from Text file to Excel & MS Access table
I have come this much
New Code:
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
Error Point Code:
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.
Re: Data Transfer from Text file to Excel & MS Access table
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.
Code:
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
1 Attachment(s)
Re: Data Transfer from Text file to Excel & MS Access table
I had the code revised (minor) and the criteria is working
Revised Code:
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 :confused: Is there something more to be done in case of TSVs.
Re: Data Transfer from Text file to Excel & MS Access table
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.
Re: Data Transfer from Text file to Excel & MS Access table
Quote:
Try the Jet OLEDB connection string.
instead of this
Code:
conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & strFolder & ";" & _
"Extensions=asc,csv,tab,txt;"
the usual Microsoft.Jet.OLEDB.4.0
Re: Data Transfer from Text file to Excel & MS Access table
Quote:
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.
Re: Data Transfer from Text file to Excel & MS Access table
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).
Re: Data Transfer from Text file to Excel & MS Access table
Quote:
Which shows it can be opened using excel without having to setup a recordset
Ok but how do I do it through code
Re: Data Transfer from Text file to Excel & MS Access table
You will need to change name of first column from ID (case sensitive) http://support.microsoft.com/kb/215591
Input: sample.txt
Code:
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
Re: Data Transfer from Text file to Excel & MS Access table
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?
Re: Data Transfer from Text file to Excel & MS Access table
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.
Re: Data Transfer from Text file to Excel & MS Access table
then how to overcome prblm reported in #7
1 Attachment(s)
Re: Data Transfer from Text file to Excel & MS Access table
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