Help Please 
Author Message
 Help Please

Hi all

I have a text file which has details of all my books and the associated
information, the files is comma delimited but there is nothing to signify
end of record. I want to convert this into a table but word puts all the
information in the first cell probably because there is nothing to signify
that it has reached end of row.

Is there a way to write some sort of VBA code such that it will help me make
this text data into a structured data.

The test file looks something like this

"Mr","James","Rafter","26-11-71","76 New Road","London","Mrs","Silvia",
"Stallion","7-9-1978","7 Longford
Cr","Manchester","Ms","Jenny","Smith","9-9-45","Westworld","London"

I want the code to loop through the above text and write as

Mr     James     Rafter     26-11-71     76 New Road     London
Mrs   Silvia     Srallion     7-9-1978     7 Longford Rd    Manchester
Ms    Jenny     Smith        9-9-45         Westworld         London

Please help
Regards



Sat, 17 Jan 2004 23:44:09 GMT  
 Help Please

Hi Jas,

If every record contains exactly 6 fields, you could probably use
basic file I/O commands to read the file, 6 fields at a time, and
then write them into a new file (untested code);

Dim fld(5, 999) As String, i As Long, j As Long, k As Long
Open "C:\Test\source.txt" For Input As #1
For i = 0 To 999
   For j = 0 To 5
      If EOF(1) Then Exit For
      Input #1, fld(i, j)
   Next j
   If EOF(1) Then Exit For
Next i
Close #1
Open "C:\Test\results.txt" For Output As #2
For k = 0 To i
   Print #2, fld(k, 0) & vbTab & fld(k, 1) & vbTab & fld(k, 2) & _
      vbTab & fld(k, 3) & vbTab & fld(k, 4) & vbTab & fld(k, 5)
Next k
Close #2

This assumes the file is 1000 records or fewer.  If longer, change
the "999" accordingly.  

I get somewhat inconsistent results for fields that span line breaks.
Maybe, to keep them together, you could do a global replace first
that changes all spaces to underscores, then change them all back
in the results file after the macro runs.

BTW, you'll get faster response if you use a more descriptive subject
line, one that tells a bit about what your question is about.


------ WWW: http://www.speakeasy.org/~mtangard ----------------------
------------- "Life is nothing if you aren't obsessed." --John Waters
---------------------------------------------------------------------

Quote:

> Hi all

> I have a text file which has details of all my books and the associated
> information, the files is comma delimited but there is nothing to signify
> end of record. I want to convert this into a table but word puts all the
> information in the first cell probably because there is nothing to signify
> that it has reached end of row.

> Is there a way to write some sort of VBA code such that it will help me make
> this text data into a structured data.

> The test file looks something like this

> "Mr","James","Rafter","26-11-71","76 New Road","London","Mrs","Silvia",
> "Stallion","7-9-1978","7 Longford
> Cr","Manchester","Ms","Jenny","Smith","9-9-45","Westworld","London"

> I want the code to loop through the above text and write as

> Mr     James     Rafter     26-11-71     76 New Road     London
> Mrs   Silvia     Srallion     7-9-1978     7 Longford Rd    Manchester
> Ms    Jenny     Smith        9-9-45         Westworld         London

> Please help
> Regards



Sat, 17 Jan 2004 04:37:07 GMT  
 Help Please
Hi Jas,

Assuming Word 2K, you could also try following:
Create a 6 columned table, to fit in the information.
Read textfile (assuming not tenthousands of records) in one
go, parse the delimited fields, and store them in the table.

Should go something like:
(note: NOT thoroughly tested)

Dim myVar, arrText As Variant
Dim iCelCount As Integer

'Read text file in one go, and store contents in MyVar
Open "d:\MyDocs\test.txt" For Input As #1
    myVar = Input(LOF(1), 1)
Close #1

'Parse/split the values
arrText = Split(myVar, ",", Len(myVar))

'Creating the 6 columned table
ActiveDocument.Tables.Add Selection.Range, 1, 6

'Transferring array values in table (this part is not duely tested)
Do Until iCelCount = UBound(arrText)
    With Selection
    .Range.Text = arrText(iCelCount)    'Transfer array value
    .MoveRight wdCell                            'Next cell
    iCelCount = iCelCount + 1
    End With
Loop

Kind regards,
Perry



Quote:
> Hi all

> I have a text file which has details of all my books and the associated
> information, the files is comma delimited but there is nothing to signify
> end of record. I want to convert this into a table but word puts all the
> information in the first cell probably because there is nothing to signify
> that it has reached end of row.

> Is there a way to write some sort of VBA code such that it will help me
make
> this text data into a structured data.

> The test file looks something like this

> "Mr","James","Rafter","26-11-71","76 New Road","London","Mrs","Silvia",
> "Stallion","7-9-1978","7 Longford
> Cr","Manchester","Ms","Jenny","Smith","9-9-45","Westworld","London"

> I want the code to loop through the above text and write as

> Mr     James     Rafter     26-11-71     76 New Road     London
> Mrs   Silvia     Srallion     7-9-1978     7 Longford Rd    Manchester
> Ms    Jenny     Smith        9-9-45         Westworld         London

> Please help
> Regards



Sat, 17 Jan 2004 07:33:23 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP,

2. PLEASE HELP PLEASE HELP PLEASE HELP

3. Design Help PLEASE PLEASE PLEASE!

4. URGENT HELP PLEASE PLEASE PLEASE???

5. Help Please Please Please

6. Help, please please please!!!! Inputing a Hexadecimal Number

7. DataSource Help Please, Please, Please

8. Help Please Please Please

9. PLEASE HELP ME..HELP....PLEASE

10. PLEASE HELP ME...HELP...PLEASE

11. Any ADO help please please help sir

12. *Please Help - Please?* Experiencing problems w/Access 2000

 

 
Powered by phpBB® Forum Software