Importing Data from txt file

[Sameer]

Hi
I receive list through MS Outlook in one single format from various users.
The list carry information for ex. like following:

Name: Dave Smith
Department: TR1
Date of Entry: Jan,03,03
Amount Adjusted: $300
Date Due:Feb, 28,03


and so on.There are around 20 fields like this but not necessary all the fiels are filled or applicable to all.
I can save this list from Outlook as Txt file.
Now my problem is:
I want to invite this list to MS Access as a record in a table to analyze various email received for this purpose.

Name,Department,Date of Entry ....etc. will be column heading for the table which is common for all email received and Dave Smith,TR1, Jan,03,03....etc. will be the record....
Please help me in setting up query for this purpose in MS Access or guide me how it can done????
Thanks a lot....

 

[crosmill]

Are the multiple records stored like this:

Name: Dave Smith
Department: TR1
Date of Entry: Jan,03,03
Amount Adjusted: $300
Date Due:Feb, 28,03

Name: Dave Smith
Department: TR1
Date of Entry: Jan,03,03
Amount Adjusted: $300
Date Due:Feb, 28,03

etc..

If so I would advise, with there only being 20 records, that you import it into Access as a dlimited txt file and tell access that ":" is the field seperator.
Then export it into excel where you can use the copy and paste special to transpose the data, so...

Name Department
Dave IT
Name Department
Dave IT

Then you can bring it back into access and delete all the records where Name = "Name"

It's very long winded, but it should do the trick??

Let me know how you get on and post back if need any more help

hth

 

[Sameer]

Hi hth
Thanks for the help.But I am looking for little more automated solution.I am getting atleast 100 txt file like this everyday.And following this long procedure would rather be difficult.
Let's see how it works out or if anybody else have any better idea.
Thanks once again
Sameer

 

[crosmill]

hth = Happy To Help


If your doing that many then I would suggest you use a vbscript to format the file useing the fileSystemObject.

Heres an example of a script I used to split up a database that was populated in just one field. It counts along text for x number of characters then inserts column and line breaks in the appropriate places.

maybe you can adapt it??

Option Explicit

Dim strConn ' As String
Dim strBasePath ' As String
Dim strFile


Dim fso, fso2, fSource, fDestination, strMessage
Dim strSource, strDestination, strInFileText, strLineBreaker, intLineLength

Dim ForReading, ForWriting

ForReading = 1
ForWriting = 2


'strSource = "merch.txt"
'intLineLength = 277

'strSource = "merchcon.txt"
'intLineLength = 83

'strSource = "cont.txt"
'intLineLength = 277

'strSource = "contcont.txt"
'intLineLength = 83

'strSource = "hip.txt"
'intLineLength = 274

'strSource = "hipc.txt"
'intLineLength = 82

strSource = "special.txt"
intLineLength = 295




strLineBreaker = Chr(13) & Chr(10)

strBasePath = ".\"
strDestination = "lf_" & strSource

strMessage = ""


Set fso = CreateObject("Scripting.FileSystemObject")
Set fso2 = CreateObject("Scripting.FileSystemObject")

Set fSource = fspenTextFile(strBasePath & strSource, ForReading)
Set fDestination = fso2.CreateTextFile(strBasePath & strDestination, True)



strInFileText = fSource.ReadAll

Dim intLenSource, intPosition
intLenSource = Len(strInFileText)
intPosition = 1

Do While intPosition < intLenSource
fDestination.write Mid(strInFileText,intPosition,intLineLength) & strLineBreaker
intPosition=intPosition + intLineLength
loop



fDestination.close
fSource.close





strMessage = "Finished creating file"

MsgBox strMessage