How to import data from text file

[Vinod]

Hi All,

I've a requirement which needs to import data from a mail and store them in
a table.

The logic implemented is onfly creating a temp text file using mail body.
created text file looks like as follows:


DISP UNIT CHANGE
ITEM PART #/DESCRIPTION PRICE QTY DISC
PRICE PRICE
----------------------------------------------------------------------------------------------
1.0 W1-CQWRE-48PD-EF 29990.00 5 90% 2999.00
14995.00
Catalogs 37500 48
10/100/1000
Klt/w

1.1 CLS-STACK-1S 100.00 5 90%
10.00 50.00
Catalogs 37500 48
10/100/1000
Solt

To read each line the following code has been used:

Here strTemp is the 1st line only (1.0 W1-CQWRE-48PD-EF 29990.00
.......)
'//Replace 3 spaces with 2 spaces
Do While (InStr(strTemp, " "))
strTemp = Replace(strTemp, " ", " ")
Loop

If IsNull(strTemp) Or Trim(strTemp) = "" Then GoTo Next_Line
gstrArr = Split(strTemp, " ")

Do loop has not been executed during runtime. If Do loop is executed I can
take care from here onwards.

But Do loop is working if I manually copy the line from mail and assign it
to strTemp and by placing the following statement before Do While statement.
strTemp=" 1.0 W1-CQWRE-48PD-EF 29990.00 5 90%
2999.00 14995.00 "


By reading each item the details have to be stored in orders table as follows:

Orders table:
Item Discription Disp_price Qty Disc
Unt_Pr Chg_Pr
1.0 W1-CQWRE-48PD-EF Catalogs 29990.00 5 90% 2999.00 14995.00
37500 48 10/100/1000 Klt/w

1.1 CLS-STACK-1S Catalogs 37500 100.00 5 90% 100.00
50.00
48 10/100/1000 Solt

Please help me out in importing data from text file with above format with
successful execution of Do While Loop or any other logic to suit with my
requirement.

Advanced Thanks
Vinod

 

[Tom Wickerath]

Hi Vinod,

You might want to take a look at a sample I have available on the Seattle
Access download page:

http://www.seattleaccess.org/downloads.htm

Look for this download:
Compilation of Tools by Tom Wickerath, October 2006 --
Download (656 kb) slides

It includes a sample database that reads text files with a file extension of
..PFA one line at a time, and re-writes this data to another text file in a
rearranged format. Certain processing had to occur once a specific string was
found in the file.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________