text file manipulation

[qureshi]

I have a text file which looks like this:

Listing prepared by ABC PVT LTD
Address of the Company
Group No 1
"Item : ADL0907 - Some Description" 6,25.8
"Item : BRO987553 - Some Description" 6,25.8
"Item : TEKL0907 - Some Description" 6,25.8
"Item : SUV9786 - Some Description" 6,25.8
Total for Group 012

Group total ends

Group No 2
and so on.

Above ADL0907 is a code which i need and 6 is the quantity i also need. I need to do this for the whole length of the file.
How can i extract this information into a table and then use in with other Access97 tables?

Thanks in advance

 

[RobertMottram]

How often are you going to need to do this. If it is just once I would probably convert the file in to a CSV file. If this is possible depends on how clean your data is.
To do this I would use a text editor or even word (just make sure you save the file as text when you have finished). Here you can do a find and replace to replace some of there parts of your file with commas. For the data you have given above this will work

Replace 'Item : ' with blank
Replace ' - ' with ","
Replace '" ' with ,

This will give a CSV file like this
Group No 1
ADL0907,Some Description,6,25.8
BRO987553,Some Description,6,25.8
TEKL0907,Some Description,6,25.8
SUV9786,Some Description,6,25.8
Total for Group 012

You will get a problem of you have ' - ' in any of the descriptions.

The only other problem is the groups, do you need any of the group information, because if not. Open the CSV file in EXCEL, sort by the first colum and all the lines starting GROUP will come first so you can delete them.

This is then a CSV file which can be imported into Access. You do also have some extra columns that you do not need so you may want to delete these when in access.

This method is fine if you only do it once or not very often, but automating it is not so easy. It is possible by using a text editor which can take macros. I have managed this using VIM before where you can write a script which you run on a file using VIM and it does all the changes and creates the file in the for you want.

A better way if you are doign this a low would be to write a small program wo read the file in, but this will take longer.

Using VB, you can open the file and read it line by line, then using split, instring, left$ and right$ you would be able to achive this.

You also make a connection from VB to access to put the records in as you go through each line of the file. (there are a number of FAQ on this forum which can show this)

I will not go into detail of how to do each of these steps as I think I am making the post quite long already and I don't know how much you already know, but I can explaine some of these steps if you don't already know how to do it.

I hope that this might give you some help

ROb

 

[THOMASNG]

If you want, it can all be done in VB!

If "ts" is the name of the text stream, you can use
"ts.ReadLine" to read each line. Then you can parse it
(= pick it apart) by using InStr(), to locate the fields,
then Mid(), to copy all characters that occur between
the field separators.
You can probably find enough information via VB's HELP.
A more detailed explanation, with examples, is given in
TEACH YOURSEKF VISUAL BASIC 6, by Scott Warner. (Available
via amazon.com)

 

[qureshi]

Thanks for the input
I'll try this out on Monday.
By the way i have to do this once every month, so its going to be an on-going process. I havent tried the Split command. Lets see what happens.

 

[THOMASNG]

In that case, you will really need to get comfortable
with manipulating text files! Get the book from amazon.com.

 

[CajunCenturion]

You may want to consider using Regular Expressions. They take some time getting used to, but they are worth the effort.

===========================================================

Dim lReg_RegExp As RegExp
Dim lInt_Hand As Integer
Dim lStr_InLine As String
Dim lStr_Product As String
Dim lStr_Code As String

Set lReg_RegExp = New RegExp

' /^.*:\s(\S+).*"\s(\d{1}).*/
lReg_RegExp.Pattern = "^.*:\s(\S+).*" & Chr(34) & "\s(\d{1}).*"

lInt_Hand = FreeFile
Open txtFileName.Text For Input As #lInt_Hand
Do While EOF(lInt_Hand) = False
Line Input #lInt_Hand, lStr_InLine
txtInputLine.Text = lStr_InLine
lStr_Product = lReg_RegExp.Replace(lStr_InLine, "$1&quot
lStr_Code = lReg_RegExp.Replace(lStr_InLine, "$2&quot
txtResults.Text = lStr_Product & " -- " & lStr_Code
DoEvents
Loop
Close lInt_Hand

An explanation of the pattern is
^.*:\s(\S+).*"\s(\d{1}).* - From Beginning of Line - evertying up thru the colon space
^.*:\s(\S+).*"\s(\d{1}).* - The next work - all non space chars, and save it $1 - first save
^.*:\s(\S+).*"\s(\d{1}).* - Everything up thru the quote space
^.*:\s(\S+).*"\s(\d{1}).* - A single digit - and save it $2 - second save
^.*:\s(\S+).*"\s(\d{1}).* - The rest of the line
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein