Import Space Delimited, but only certain spaces?

briananderson

Registered User.
Local time
Today, 12:10
Joined
May 8, 2008
Messages
17
I'm trying to figure out if its possible to import a text file thats space delimited in to access. See the example text file below. I can import it via space delimited, but the titles of the movies that have spaces gets parsed up too. Is there a way to just parse the first few spaces and then group everything else into one field?

I have 3 fields: BoxType, BoxNumber, and MovieTitle

BOXTYPE-A 1 TITLE OF MOVIE1
BOXTYPE-A 1 TITLE OF MOVIE2
BOXTYPE-A 2 TITLE OF MOVIE3
BOXTYPE-A 2 TITLE OF MOVIE4
BOXTYPE-A 3 TITLE OF MOVIE5
BOXTYPE-A 4 TITLE OF MOVIE6

So I'd like it to look like this:
BoxType: BOXTYPE-A
BoxNumber: 1
MovieTitle: TITLE OF MOVIE1

Is this possible? Thanks!
 
Is it space delimited or really TAB delimited. Have you tried importing with Tab as the delimiter?
 
Unfortunately, I have tried Tab as a delimiter. Its definitely space delimited.
 
Where does this text file come from? How is it created?
 
This list comes from a pdf report document that I've converted to a text file. This report came from a database that I do not have access to. I'm trying to find the easiest route to get this data into a database of my own. Its about 300 pages.
 
Well, unfortunately, I don't think there is an easy way around this one.
 
Drat! Thats what I was thinking. So right now, I've done a fixed width import, and am trimming all the remaining fields that didn't import correctly.

Thanks for the help, anyway!
 
From the data you have supplied the following looks possible: -

First field…
Contiguous Left of first space.

Second field…
Contiguous Is Numeric

Third field…
Contiguous Mid from second space.
 
Sounds promising. I'm not as versed in MS Access as I'd like to be. How would I implement this?
 
Look in help for the following…

Line Input

InString

Left

IsNumeric (may not be needed)

Mid

Open your text file and get one line at a time.
With that line input, use the above to populate three variables.
To a table, AddNew the three variables, and Update the new record.
 

Users who are viewing this thread

Back
Top Bottom