Re: import text file to access table using vb

opss...please read the below detail.:blush:

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

---

Thanks for help!

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.

leinad31：
hi,thanks your reply.i understand what you mean,but this HR software is only can export the data to txt File.:( :( for this way what to do next?

Thanks for help!

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.

leinad31,
sorry this question abit silly:blush: ,how to create this "Then read the txt file and create another textfile (the CSV)."

thanks for help!

What's the structure of the destination table (column names, data types, etc)? How do the table columns and the textfile data match?

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

Thanks for help!

Code:

---

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.:thumb: 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.:blush:

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!:blush:

Yup. Let us know if there are errors.

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.

leinad31:
thanks ,basically the from cvs file import to table is done.:thumb: 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.

StaffNo, StaffName,TotalHrs ,DateFrom,DateTo
"00004", "LIM BEE LENG", 0,1/06/07,29/6/07
"00023", "YONG CHEE KHEONG", 0,1/06/07,29/6/07
"00032", "HUI CHEE KING", 174.4,1/06/07,29/6/07
"00037", "TAN WEI PENG", 199.17,1/06/07,29/6/07
"00046", "YEOH CHOON HUAT", 190.87,1/06/07,29/6/07

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

  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

---

Thanks for help!:blush:

After organizing per page and before the iteration which strips the header, extract the dates.

Dim strFrom As String
Dim strTo As String
Dim lPos As Long

lPos = InStr(1, strArr(0), " TO ")
strFrom = Mid$(strArr(0), lPos - 10, 10)
strTo = Mid$(strArr(0), lPos + 6, 10)

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.

leinad31,
hi,thanks you reply.i have try to added the code,but have error appear.

Code:

---

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))
        lPos = InStr(1, strArr(0), " TO ")
        strFrom = Mid$(strArr(0), lPos - 10, 10) 'error appear at this line
        strTo = Mid$(strArr(0), lPos + 6, 10)
        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

---

thanks for help!

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).

Please refer to my previous post.

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.

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

---

thanks for help!:blush:

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.

BTW, it would be best to post the current source textfile so we can identify differences in format if there are any.

leinad31:
thanks for your reply.i already post the current textfile here.please have a look.Thanks for help!:)

Ahh kk, my bad. Try index 1 or strArr(1).

leinad31:
thanks your reply,the csv problem basically is done!:afrog: 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

---

thanks for help!:blush:

From is a reserved word, try using StartDate and EndDate for your column names.

leinad31:
hi,basically csv file imported to database is done,thanks :afrog:
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.

leinad31:
hei guy,thanks alot!take your time.sorry to disturb you:blush: