Avatar of angie_angie
angie_angie asked on

how to delimit a text in one field

Hi Experts,

I have an address stored in one field as:

AddrLine_1|AddrLine_2|AddrLine_3... ...|AddrLine_n

where n is different for different records, but will not exceed 10.
The delimiter is always "|". I want to seperate the text and put the values into another table as follows:

fld_1                   fld_2                 ... ...   fld_6           ... ...      fld_10
AddrLine_1       AddrLine_2        ... ...  AddrLine_6 ... ...    AddrLine_10  <---------- one record
AddrLine_1       AddrLine_2        ... ...  empty          ... ...      empty           <---------- another record
... ..............             .........               ................                          .........

Can anyone help me with this?

I'll be using INSERT INTO to add the data.
Microsoft Access

Avatar of undefined
Last Comment
nexusnation

8/22/2022 - Mon
Jeffrey Coachman

angie_angie,

Have you just tried using the basic import wizard to do this?

JeffreyCoachman
ASKER
angie_angie

? How to do that? I just used DoCmd.acImport ... ...

Jeffrey Coachman

angie_angie,

For example:
This file came into Access just fine using the standard Text Import wizard.
(File-->Get External Data--> Import)

111 Ridge Street|Brooklyn|NY|11201|
21 Cool Club Drive|APT 99A|Coram|NY|11727
308 Liberty Ave|2nd Floor|Deer Park|NY|11729
2000 Neptune Ave|Brooklyn|NY|11224|

(Just make sure you select the Pipe symbol as the delimiter and double-check the preview.)

JeffCoachman
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
nexusnation

Hi angie_angie-

I think I get the picture at this point.  It looks like you've got quite the nightmare database on your hands, and you're trying hard to normalize it.  If this is the case, then odds are you'll only need to run this INSERT INTO statement once.  This is good, because then we can use a work-around, as trying to create an SQL statement for this would be a total mess.

In this case, copying it into a text file and running an import doesn't look like a bad idea.  Alternatively, we could set up a nice loop in visual basic to run a series of INSERT INTO statements utilizing the Split function (delimiter of |, of course) to populate arrays with the split addresses.

Make sense?
SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
angie_angie

boag2000,

I cannot use import wizard because what I ultimately aim at is automation so that no mannual data manipulation is needed.

nexusnation,

Exactly! I've got loads of messy excel files to normalize before I can extract the useful information into my template tables. But the end user doesn't want to touch on the source files so the only way out is to import the original Excel sheets and manipulate them in Access.

I don't know how to use Split function. Just now I came up with this:

start = 1, end = 1
For i = 1 to 10
  end = InStr (start, Address, "1") -1
  AddrLine(i) = Mid (Address, start, end)
  start = end +2

Will this work? And how can I add the addrLine(i) into the table using INSERT INTO command?

Thank you very much!
Jeffrey Coachman

angie_angie,

OK, posted the code steps.
http:#a21658613

Let me know if that helps

JeffCoachman
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

angie_angie,
<I don't know how to use Split function. Just now I came up with this...Will this work? And how can I add the addrLine(i) into the table using INSERT INTO command?>

*PLEASE*, lets keep this to ONE question per thread, to avoid confusion.

You can post the "Split Function" question in a new thread.
OK?

Thanks

JeffCoachman
ASKER
angie_angie

boag2000,

The source file is in Excel, not in Text. When I tried to replace TransferText with TransferSpreadSheet, a type mismatch error occurs
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

angie_angie,
<The source file is in Excel, not in Text.>
Again, you did not mention this in your original post.

OK, so use the "Text-To-Columns" function in Excel to split the field.

-Select the column you want to split
-Click: Data-->Text to Columns
-Select: "Delimited"
-Enter the Pipe symbol as the delimiter (And uncheck any others)
-Select a Destinaion cell.

This should work for you now.
If not, then please post this Excel file for us to examine.
Thanks

JeffCoachman
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
nexusnation

Jeff-

I'm about as lost as you are, and this isn't my first question I've helped angie_angie on, either.  It appears that the end user has a bunch of Excel files that she wants ingested into an Access database automatically.  What's worse, it doesn't appear to be a one-time only import, in that the end user will continue to create these horribly malformed Excel files.  With such an absolutely inept and stubborn end-user, I highly doubt any wizards in either Access or Excel that the end-user would need to run will bode well with him or her.

I could be entirely off-base, but that's how I understand it.

And angie_angie: Please, please explain your situation as clearly as possible in each question.  Inform us exactly what you can and can't do, that way we know how far we're allowed to venture when coming up with a solution, and inform us of exactly what you're starting with and exactly what you need to end up with.  Examples are always good, as are table definitions.  Back-tracking and being forced to come up with a different solution isn't fun for any of us.

Andrew
ASKER
angie_angie

Hey Jeff and Andrew,

I'm so sorry to confuse you guys. The reason I didn't explain the whole thing at the beginning is because I don't wanna make my question so complicated, so I asked one part by one part.

Ok, let me explain...

The end user wants an Access database storing the useful info. The source files of this database are 3 Excel worksheets which are very messy and the end user doesn't wanna touch them. Also, the source files will be overwriten constantly. So I wanna import the source files into Access temp Tables first, then get the useful info into my db Tables. From the db Tables, a report in Word will be generated.

Therefore, the end user will only do 2 things.
1. once the source files are overwriten, he clicks on "Update" button in Access, the useful data will flow into my db Tables;
2. he types in an ID and clicks on "Enter", a report is generated in Word.

Angie
nexusnation

Angie-

It's okay.  I know I've seen much worse from askers.  Splitting up your various parsing questions into separate question threads is perfectly fine and/or encouraged.  Just make sure you specify exactly what your parameters are, and everything will go smoothly :)

So, I think you should head back over to http:#21658780, when I demonstrated the Split function with an Insert Into inside a loop.  Now all you need to do is wrap a loop around that, which would cycle through the temp table's records.

You had a good start in http:#21658632, so I'm not going to teach you how to walk if you already can, and you're just looking for a good path to travel down. Try it out and let us know if/when you get stuck.

Andrew
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
angie_angie

Andrew,

Thanks for your understanding!  :)
nexusnation

Not a problem.  I'm glad you threw some points Jeff's way, as he took a few good cracks at this himself.
Jeffrey Coachman

angie_angie,

First of all, I am glad this issue is resolved.

As both nexusnation and I have stated, it always helps if you povide as much info as applies the the question without giong overboard.
It only saves us from proposing solutions that are not applicable.

Try to explore the posts in order as they appear to avoid "Bouncing" between Posts, Experts and/or Solutions.

One good trend I see starting to develope is that askers will attach the file in question in their initial post.

:-)

Jeff
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jeffrey Coachman

nexusnation,

Congratulations on another great solution.

BTW: what were you doing between 2004 and 2008?!
;-)

JeffCoachman
nexusnation

Jeff,
I've been waiting for someone to notice that.  E-mail is in my profile.