hello,how to import text file to access table using vb?what i'm trying to do is import the text data from the select text file using commandialog select desire text file to import into the access database.i upload the text file here,the data want to import from text is only the value.please help.Thanks alot!
this is the commondialog i done with!
Code:
Private Sub CmdSearch_Click()
On Error GoTo ErrHandler
With CommonDialog1
.CancelError = True
.Filter = "General Modules (*.txt)/*.txt"
.ShowOpen
End With
ErrHandler:
Err.Clear
Exit Sub
End Sub
I suggest you reinterpret the file as a CSV with column headers. You will have to parse the file anyway, its unavoidable.
When you have the CSV, try the following query format
INSERT INTO tablename (fieldslistdest) SELECT fieldslistsrc FROM tablesrc IN 'CSV path'
sample
INSERT INTO desttable (field_date, field_long, field_str) SELECT mydate, mylong, mystr FROM mysourcetable IN 'C:\sample\parsedCSV.txt'
Where mydate, mylong, mystr are column header names you created for the CSV file. You then just have to execute the query on the connection object connected to the access database.
Then read the txt file and create another textfile (the CSV). Then INSERT query the CSV to the access database.
a) You'll have to parse the original text file anyway to ignore the extraneous text.
b) You'll transfer the records as a batch rather than one record at a time.
ok,from the text file it may have the title header all that,but i just want to import the value to database table.The text file i only want to import is start from start and 1-3 column to the destination database fields StaffNo,StaffName,WorkTotalHours .
StaffNo,StaffName datatype is Text
WorkTotalHours datatype is Number
Option Explicit
Private Sub Command1_Click()
Dim iFileNum As Integer
Dim strText As String
Dim strArr() As String
Dim lIdx As Long
Dim strNo As String
Dim strName As String
Dim sngHrs As Single
iFileNum = FreeFile
Open "C:\Temp\ATTEND TOTAL HOURS.TXT" For Input As #iFileNum
strText = vbCrLf & Input(LOF(iFileNum), #iFileNum)
Close #iFileNum
strArr = Split(strText, vbCrLf & " Dufu Industries Sdn Bhd") 'organize per page
strText = "" 'reclaim memory
For lIdx = 0 To UBound(strArr) 'Strip headers
strArr(lIdx) = Mid$(strArr(lIdx), InStrRev(strArr(lIdx), "Leaves" & vbCrLf) + 8)
Next
strArr = Split(Join(strArr, ""), vbCrLf) 'reorganize per line
iFileNum = FreeFile
Open "C:\Temp\CSV.txt" For Output As #iFileNum
Print #iFileNum, "StaffNo, StaffName,TotalHrs" 'print CSV column headers
For lIdx = 0 To UBound(strArr) - 2
strNo = Trim(Left$(strArr(lIdx), 10))
strName = Trim(Mid$(strArr(lIdx), 11, 40))
sngHrs = CSng(Mid$(strArr(lIdx), 51, 12))
strText = Chr$(34) & strNo & Chr$(34) & ", " _
& Chr$(34) & strName & Chr$(34) & ", " & Round(sngHrs, 2)
Print #iFileNum, strText 'append to CSV
Next
Close #iFileNum
End Sub
leinad31:
thanks,the reinterpret text to csv file code is done. below is the import csv to the access database table.have error appear regarding the path of CSV.txt location is invalid.i been check the location i save the csv file is at d:\Attend\csv.txt . please help have a look for the strsql statement not sure i done the correct way.
Code:
Private Sub CmdImport_Click()
Dim conDataConnection As Connection
Dim strsql As String
Dim Split_Data
Dim Raw_Data As String
'Create adatabase connection
Set conDataConnection = New Connection
conDataConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & _
"\Attendance.mdb;Persist Security Info=False"
'Open the source text file
Open App.Path & "\CSV.txt" For Input As #1
While Not EOF(1)
Line Input #1, Raw_Data
Split_Data = Split(Raw_Data, Chr(44), -1, vbTextCompare)
strsql = "INSERT INTO TempA (field_StaffNo, field_StaffName, field_WorkTotalHours) SELECT StaffNo, StaffName, TotalHrs FROM mysourcetable IN 'D:\Attend\CVS.txt'"
conDataConnection.Execute strsql
DoEvents
Wend
Close
conDataConnection.Close
Set conDataConnection = Nothing
End Sub
thanks for help!
Last edited by gracehskuo; Jul 2nd, 2007 at 10:22 PM.
Sorry the point of reference was wrong. Connect to the textfile using jet database engine and insert into access DB using IN clause.
Code:
Private Sub Command2_Click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Temp\;" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited"""
conn.Execute "INSERT INTO DestTbl (StaffNo, StaffName, TotalHrs) IN 'C:\Temp\db1.mdb' " _
& " SELECT StaffNo, StaffName, TotalHrs FROM CSV#TXT "
conn.Close
'see if records transferred
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Temp\db1.mdb;"
Set rs = conn.Execute("SELECT * FROM DestTbl ")
Debug.Print rs.GetString(, , ", ", vbCrLf, "")
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub
No need to iterate through the CSV.
Last edited by leinad31; Jul 3rd, 2007 at 12:54 AM.
leinad31:
thanks ,basically the from cvs file import to table is done. sorry to disturb you regarding the text file reinterpret to csv file.if i want to add 2 field for date From and date To to the csv file,what should i edit from the statement?
example:this is date is taken from the text file FROM 01/06/2007 TO 29/06/2007 ,i want this date from and date To ,save display at every after TotalHrs field.
Private Sub CmdConvert_Click()
Dim iFileNum As Integer
Dim strText As String
Dim strArr() As String
Dim lIdx As Long
Dim strNo As String
Dim strName As String
Dim sngHrs As Single
iFileNum = FreeFile
Open "D:\Attend\Temp\ATTEND TOTAL HOURS.TXT" For Input As #iFileNum
strText = vbCrLf & Input(LOF(iFileNum), #iFileNum)
Close #iFileNum
strArr = Split(strText, vbCrLf & " Dufu Industries Sdn Bhd") 'organize per page
strText = "" 'reclaim memory
For lIdx = 0 To UBound(strArr) 'Strip headers
strArr(lIdx) = Mid$(strArr(lIdx), InStrRev(strArr(lIdx), "Leaves" & vbCrLf) + 8)
Next
strArr = Split(Join(strArr, ""), vbCrLf) 'reorganize per line
iFileNum = FreeFile
Open "D:\Attend\Temp\CSV.txt" For Output As #iFileNum
Print #iFileNum, "StaffNo, StaffName,TotalHrs" 'print CSV column headers
For lIdx = 0 To UBound(strArr) - 2
strNo = Trim(Left$(strArr(lIdx), 10))
strName = Trim(Mid$(strArr(lIdx), 11, 40))
sngHrs = CSng(Mid$(strArr(lIdx), 51, 12))
strText = Chr$(34) & strNo & Chr$(34) & ", " _
& Chr$(34) & strName & Chr$(34) & ", " & Round(sngHrs, 2)
Print #iFileNum, strText 'append to CSV
Next
Close #iFileNum
MsgBox " Sucess Saved CSV File"
End Sub
Add the relevant columnsheaders in the CSV, and append to strText. You didn't mention the data type of date column in destination table (you will have to nest the date with # if datetime data type), and your date format may run into regional settings problems when you convert the date as string into date as datetime data type.
If LPos is less than 11, Mid$ is trying to start before the first character (character position LPos - 10). This could be due to " TO " being less than 11 characters from the start of strArr(0), or there's no " TO " in strArr(0) at all (giving you an LPos of 0).
The most difficult part of developing a program is understanding the problem.
The second most difficult part is deciding how you're going to solve the problem.
Actually writing the program (translating your solution into some computer language) is the easiest part.
Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read. Please Help Us To Save Ana
I said the snippet comes just before iteration (first loop) and after organizing stream per page (the first split). Or between those two, I didn't say it goes in the second iteration.
Last edited by leinad31; Jul 3rd, 2007 at 07:19 PM.
leinad31,
hi,thanks you reply,i been refer back the previous post follow the step from your guide.but still have same error appear when excute the code.
please help!
Code:
Private Sub CmdConvert_Click()
Dim iFileNum As Integer
Dim strText As String
Dim strArr() As String
Dim lIdx As Long
Dim strNo As String
Dim strName As String
Dim sngHrs As Single
Dim strFrom As String
Dim strTo As String
Dim lPos As Long
iFileNum = FreeFile
Open "D:\Attend\Temp\ATTEND TOTAL HOURS.TXT" For Input As #iFileNum
strText = vbCrLf & Input(LOF(iFileNum), #iFileNum)
Close #iFileNum
strArr = Split(strText, vbCrLf & " Dufu Industries Sdn Bhd") 'organize per page
strText = "" 'reclaim memory
lPos = InStr(1, strArr(0), " TO ")
strFrom = Mid$(strArr(0), lPos - 10, 10)'error still appear at this line
strTo = Mid$(strArr(0), lPos + 6, 10)
For lIdx = 0 To UBound(strArr) 'Strip headers
strArr(lIdx) = Mid$(strArr(lIdx), InStrRev(strArr(lIdx), "Leaves" & vbCrLf) + 8)
Next
strArr = Split(Join(strArr, ""), vbCrLf) 'reorganize per line
iFileNum = FreeFile
Open "D:\Attend\Temp\CSV.txt" For Output As #iFileNum
Print #iFileNum, "StaffNo, StaffName,TotalHrs,From,TO" 'print CSV column headers
For lIdx = 0 To UBound(strArr) - 2
strNo = Trim(Left$(strArr(lIdx), 10))
strName = Trim(Mid$(strArr(lIdx), 11, 40))
sngHrs = CSng(Mid$(strArr(lIdx), 51, 12))
strText = Chr$(34) & strNo & Chr$(34) & ", " _
& Chr$(34) & strName & Chr$(34) & ", " & Round(sngHrs, 2) & "," _
& Chr$(34) & strFrom & Chr$(34) & "," & Chr$(34) & strTo & Chr$(34) & ""
Print #iFileNum, strText 'append to CSV
Next
Close #iFileNum
MsgBox " Sucess Saved CSV File"
End Sub
That will only happen if the format of your source textfile is different or the string " TO " ins't in the header. You will have to parse the file differently if the format has changed.
leinad31:
thanks your reply,the csv problem basically is done! now i have the imported csv to database error.sql syntax error appear.i refer back to the previous post you mention nest # for date data type.please help!
Code:
conn.Execute "INSERT INTO Attendance (StaffNo, StaffName, TotalHrs,#From) IN 'D:\Attend\Attendance.mdb' " _
& " SELECT StaffNo, StaffName, TotalHrs,From FROM CSV#TXT "
conn.Closesyntax error at this line
leinad31:
hi,basically csv file imported to database is done,thanks
ok i have some question regarding the convert orginal textfile to csv file by using commondialog to select which by difference month of textfile to convert izzit possible to add in this?And for the import the csv file has been import to the database before,if import at second time it may have msgbox prompt out want to overwrite the existing data from database.sorry disturb agian,thanks for help!
Sorry kinda busy right now, will try to get back at you later. In case someone is free to look at the code, regarding existing records we'll look into modiying insert query to have pseudo
INSERT INTO tbl (flds) IN 'access db path' SELECT B.flds FROM csv#txt AS B WHERE NOT EXISTS (SELECT staffno, startdate, enddate FROM tbl AS A IN 'access db path' WHERE A.staffno = B.staffno AND A.startdate = B.startdate AND A.enddate = B.enddate)
If query is not possible, we'll look into creating a filtered csv.
Reply With Quote
below is the import csv to the access database table.have error appear regarding the path of CSV.txt location is invalid.i been check the location i save the csv file is at d:\Attend\csv.txt . please help have a look for the strsql statement not sure i done the correct way.
now i have the imported csv to database error.sql syntax error appear.i refer back to the previous post you mention nest # for date data type.please help!