How to import enter-delimited text to Access Table

A

alap43841

Registered User.
Local time
Today, 15:55
Joined
May 9, 2006
Messages
36
I have very little knowledge on VBA, I need to import a text file where the data is delimited by enter key, to a Access table or convert the text to comma/space delimited text , sample as bellow (Bold text are the field Name and Italic texts are data):

|Symbol|DSE NEWS
|News|Today's (29.03.2011) Total Trades: 178,791; Volume: 76,264,115 and Turnover: Tk. 9,473.20 million.
|Posting Date |2011-03-29
|
|Symbol|RAKCERAMIC
|News|The Company has requested the concerned shareholders to collect their cash dividend for the year ended on December 31, 2010 from 29.03.11 to 31.03.11 from Dhaka Zilla Krira Sangshtha, Jhil Par, Motijheel, Dhaka. The Company has also informed that it has credited the Bonus shares to the respective shareholders' BO Accounts on 24.03.11.
|Posting Date |2011-03-29
|
|Symbol|CONTININS
|News|Mr. Mohammed Iqbal, one of the Sponsors/Directors of the company, has reported his intention to sell 1,700 shares (Bonus Shares) out of his total holdings of 67,429 shares of the company at prevailing market price through Stock Exchange within next 30 working days.
|Posting Date |2011-03-29
|
|Symbol|TRUSTBANK
|News|Trading of the shares of the Bank will be allowed only in the Spot Market and Block/Odd lot transactions will also be settled as per Spot settlement cycle with cum benefit from 30.03.11 to 03.04.11. Trading of the shares of the Bank will remain suspended on record date i.e., 04.04.11. Another record date for entitlement of the proposed rights share to be notified later after obtaining approval from SEC.
|Posting Date |2011-03-29

I searched around the net and got a VBScript, the link is http://www.j.nurick.dial.pipex.com/Code/VBS/talltowide.vbs
but i don't know how to execute it in MS Access, please help me and consider my limited knowledge on VBA, thanks
 
This is posible using Open File for Input methodology. Search for examples.
 
Quiet night so here is a working example based on the data supplied in the earlier post.

Copy this function into a standard module. Read the comments first on how to use this function.

Code: 
Public Function ImportMyFile()
[COLOR="Green"]'/Import a txt file into Access
'/Specially written by D Crake X-Craft Limited
'/29th March 2011

'/Define the table that will[/COLOR]
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("[B]YourTableNameHere[/B]")

Dim tFile As String
Dim tmpstr As String
Dim pipe
Dim strSymbol As String
Dim strNews As String
Dim dtmDate As Date

[COLOR="green"]'/Define the full path and filename of the incoming text file[/COLOR]
tFile = "[B]C:\Temp\Test1.txt[/B]"

[COLOR="green"]'/Open the file and loop through all the line in the file[/COLOR]
Open tFile For Input As #1
Do Until EOF(1)    
    Line Input #1, tmpstr
   [COLOR="green"] '/Ignore the first pipe symbol in the line[/COLOR]    
     tmpstr = Mid(tmpstr, 2)
    '/If lin is blank then this means it is a space between the records
    If tmpstr <> "" Then
        pipe = Split(tmpstr, "|")
        '/What is the heading of the field being read
        Select Case Trim(pipe(0))
            Case "Symbol"
                strSymbol = Mid(tmpstr, 8)
            Case "News"
                strNews = Mid(tmpstr, 6)
            Case "Posting Date"
                dtmDate = Format(CDate(Mid(tmpstr, 15, 10)), "dd/mm/yyyy")
            Case Else
        End Select
    Else
        '/Add the record to the table
        
        Rs.AddNew
        Rs("[B]YourSymbolField[/B]") = strSymbol
        Rs("[B]YourNewsField[/B]") = strNews
        Rs("[B]YourPostingDateField[/B]") = dtmDate
        Rs.Update
    
    End If
    
Loop
'/close the text file and the recordset
Close #1
Close Rs
Set Rs = Nothing



End Function
 
DCrake, thanks for your very quick reply...........really appreciate your help, I'll try your suggestion, regards
 
DCrake, Sorry again, could not make it, copied the code in a standard module, created a table having three fields "Symbol">Text, "News">Text and "PostingDate">Date/Time and edited your code accordingly. With command button click event calling the function like this
Private Sub Command2_Click()
Call ImportMyFile
End Sub
but nothing happened :(, can you put some light on this please........thanks
 
DCrake, Sorry again, could not make it, copied the code in a standard module, created a table having three fields "Symbol">Text, "News">Text and "PostingDate">Date/Time and edited your code accordingly. With command button click event calling the function like this
Private Sub Command2_Click()
Call ImportMyFile
End Sub
but nothing happened :(, can you put some light on this please........thanks
 
Show me your revised function
Also when you say nothing happened what do you mean? did you get an error message or anything?
 
Last edited:
Thanks for your reply, here the revised function

Code: 
Option Compare Database

Public Function ImportMyFile()
'/Import a txt file into Access
'/Specially written by D Crake X-Craft Limited
'/29th March 2011

'/Define the table that will
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("ImportNews")

Dim tFile As String
Dim tmpstr As String
Dim pipe
Dim strSymbol As String
Dim strNews As String
Dim dtmDate As Date

'/Define the full path and filename of the incoming text file
tFile = "C:\SWPMS\own use\NewsUpdt.txt"

'/Open the file and loop through all the line in the file
Open tFile For Input As #1
Do Until EOF(1)
    Line Input #1, tmpstr
    '/Ignore the first pipe symbol in the line
     tmpstr = Mid(tmpstr, 2)
    '/If lin is blank then this means it is a space between the records
    If tmpstr <> "" Then
        pipe = Split(tmpstr, "|")
        '/What is the heading of the field being read
        Select Case Trim(pipe(0))
            Case "Symbol"
                strSymbol = Mid(tmpstr, 8)
            Case "News"
                strNews = Mid(tmpstr, 6)
            Case "Posting Date"
                dtmDate = Format(CDate(Mid(tmpstr, 15, 10)), "dd/mm/yyyy")
            Case Else
        End Select
    Else
        '/Add the record to the table
        
        Rs.AddNew
        Rs("Symbol") = strSymbol
        Rs("News") = strNews
        Rs("PostingDate") = dtmDate
        Rs.Update
    
    End If
    
Loop
'/close the text file and the recordset
Close #1
Close Rs
Set Rs = Nothing



End Function
and here is the event procedure in command button
code:
Code: 
Private Sub Command2_Click()
Call ImportMyFile
End Sub

also, the file that I want to import...........
I forgot to mention that in the text file top three lines I want to delete automatically during import process
|Last 7 days news
|From : 2011-03-23 To: 2011-03-30
|
Click to expand...
......during testing your code I manually deleted those text and tried to execute the function
once I click the command button nothing happens no error msg or anything.........looking forward for your further instruction............
 

Attachments

Using the sample you sent me I ran the following revised code

Code: 
Public Function ImportMyFile()
'/Import a txt file into Access
'/Specially written by D Crake X-Craft Limited
'/29th March 2011

'/Define the table that will
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("[B]TblNews[/B]")

Dim tFile As String
Dim tmpstr As String
Dim pipe
Dim strSymbol As String
Dim strNews As String
Dim dtmDate As Date

'/Define the full path and filename of the incoming text file
tFile = "[B]C:\Temp\NewsUpd.txt[/B]"

'/Open the file and loop through all the line in the file
Open tFile For Input As #1
Do Until EOF(1)
    Line Input #1, tmpstr
    '/Ignore the first pipe symbol in the line
    tmpstr = Mid(tmpstr, 2)
    '/If lin is blank then this means it is a space between the records
    If tmpstr <> "" Then
        pipe = Split(tmpstr, "|")
        '/What is the heading of the field being read
        Select Case Trim(pipe(0))
            Case "Symbol"
                strSymbol = Mid(tmpstr, 8)
            Case "News"
                strNews = Mid(tmpstr, 6)
            Case "Posting Date"
                dtmDate = Format(CDate(Mid(tmpstr, 15, 10)), "dd/mm/yyyy")
            Case Else
        End Select
    Else
        '/Add the record to the table
        If strSymbol <> "" Then
            Rs.AddNew
            Rs("[B]Symbol[/B]") = strSymbol
            Rs("[B]News[/B]") = strNews
            Rs("[B]PostingDate[/B]") = dtmDate
            Rs.Update
        End If
        
    
    End If
    
Loop
'/close the text file and the recordset
Close #1
Rs.Close

Set Rs = Nothing



End Function

Remember you need to set the news field to be a memo field.
Mak surre the bolded items match yours.
 
I appreciate ur help again, I'll do as u told ………regards
 
Thank you very much for effort, it was a great help for me and I am really grateful to you................things are working perfectly the way I wanted, thanks
 
DCrake, there is a problem I am facing that is the last record set of the text file is not imported to the database. The text file has 212 record set but in database table there are 211 records..........I am attaching both a sample DB and the text file, can you please help.............thanks
 

Attachments

Amendment to function

After the Line Close #1

copy and paste the code, save and retry.
Code: 
            Rs.AddNew
            Rs("Symbol") = strSymbol
            Rs("News") = strNews
            Rs("PostingDate") = dtmDate
            Rs.Update
 
DCrake, giving you trouble again............what should I do if the text is tab delimited? What should I change in the code.......thanks
 

Attachments

Go back to the people who send you the data and tell them that next time is chargeable. Surely the information I gave you last time will enable you to convert it to suit you new requirement.
 
You must log in or register to reply here.

Similar threads

Rodeo Clown
Access import function converts text field to scientific notation
Replies
9
Views
698
cheekybuddha
cheekybuddha
A
Solved How to change date portion at beginning
Replies
13
Views
1,221
Ashfaque
A
B
Import Excel CSV Specific Cells to MS Access Table
Replies
13
Views
1,398
CJ_London
C
W
CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag)
Replies
14
Views
1,915
Cotswold
Cotswold
Isaac
Safest way to backup a table - thinking of delimiters, quoted text, with unpredictable data
Replies
12
Views
1,145
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom