import data from 1 row to multiple rows

[Guest]

I need to import a text file from an external database that contains data all
in 1 row separated by comma and space. (It is a dictionary that an external
program uses and I need to clean it up). I want to import all words from
file into access into 1 field. Problem is data exceeds 255 field limit - so
I can't import into columns first, and then transpose to rows. In wordpad,
don't know how to replace "," with ", and carriage return" to create a normal
data file.

Any ideas? I appreciate any help!!!!

 

[EeOr]

Hi, dont know how to do this using Access or notepad however if you open the
file in Word, you could use the foillowing code:

Sub replace()
With ActiveDocument.Content.Find
..Execute findtext:=", ", replacewith:=vbCr, Format:=False,
replace:=wdReplaceAll
End With
End Sub

take a copy of your file before trying this as you may need to amend the
actual search strings

Hth
Jon

 

[louisjohnphillips]

I need to import a text file from an external database that contains data all
in 1 row separated by comma and space. (It is a dictionary that an external
program uses and I need to clean it up). I want to import all words from
file into access into 1 field. Problem is data exceeds 255 field limit - so
I can't import into columns first, and then transpose to rows. In wordpad,
don't know how to replace "," with ", and carriage return" to create a normal
data file.

Any ideas? I appreciate any help!!!!

Have you considered doing something along these lines?

Start with a comma-separated file named "C:\temp\FoxJump.txt" with a
text of:

The, Quick, Brown, Fox, Jumped, Over, The, Lazy, Dog

Create a VBA module as follows:

Sub main()

Dim sStr As String

Open "C:\temp\FoxJump.txt" For Input As #1
Open "C:\temp\diction.dat" For Output As #2

While Not EOF(1)
Input #1, sStr
Print #2, sStr
Wend

Close #1
Close #2

End Sub

When this has run, it will create a file in "C:\temp\diction.dat" with
the following format:

The
Quick
Brown
Fox
Jumped
Over
The
Lazy
Dog

Use the Get External Data file menu option to import the text file.


Lou Phillips