Multiple delimitations in column to import or export

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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.
 
Back
Top