Multiple delimitations in column to import or export

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sfleck - 29 May 2007 19:43 GMT

I have a file that contains a colum that has data in it that has multiple
delimitations. (Item#, Quantiity) then each of those are delimitated by a
semicolon
I would like to parse out the items, and quantity to gather the amount of
each item sold

Reply to this Message

Jerry Whittle - 29 May 2007 20:14 GMT

If it isn't already, save the file as text with a TXT extension. Next in
Access go to File, Get External Data, Import. Change the Files of Type
dropdown to Text.

The import text wizard should show up and you can change the delimiter to a
semi-colon.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I have a file that contains a colum that has data in it that has multiple
> delimitations. (Item#, Quantiity) then each of those are delimitated by a
> semicolon
> I would like to parse out the items, and quantity to gather the amount of
> each item sold

Reply to this Message

sfleck - 29 May 2007 20:42 GMT

Thank you but I still have issues

my data comes as follows

275775,1;271835,1;83243,1;111917,1;268947,
265116,1;283513,1;292077,1;256902,1;273133,1;287661,1;269027,3;267700,1;282587,1;284014,1;292595,1;281032,1;285473,1;272033,1;291407,1;75473,1;268879,1

each row is tab deliminated
then that data is then (;) delimated
Then that data is (,) deliminated

each the item numbers are not a consistant length nor the quantity

I tried to take each cell and concatenate it in ( excell) to make one big
entry no (Tab) deliminator but it said I errored the the cell

> If it isn't already, save the file as text with a TXT extension. Next in
> Access go to File, Get External Data, Import. Change the Files of Type
[quoted text clipped - 8 lines]
> > I would like to parse out the items, and quantity to gather the amount of
> > each item sold

Reply to this Message

Jerry Whittle - 30 May 2007 00:22 GMT

Aha! Open the file in Word. Go to Edit, Replace.

In the Find What box put the following: ^t (finds tabs)
In the Replace with box put the following: ^p (replaces them with
paragraphs)

Replace all. Next the Find What box put a semi-colon ( ; ).
In the Replace with box put the following: ^p

The ^ is Shift+6.

After replacining them all save the file as text under a slightly different
name.
Then you should be able to import the file in Access as a csv (Comma
Seperated Value).

It's possible that it's really not tab deliminated. Instead it could be a
line feed.
In that case use ^l (lower case L for line-feed) instead of the ^t.

Also make sure that there aren't any blank lines. If so you might need to
replace ^p^p with a single ^p.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Thank you but I still have issues
>
[quoted text clipped - 24 lines]
> > > I would like to parse out the items, and quantity to gather the amount of
> > > each item sold

Reply to this Message