Solved

split comma delimited values into seperate columns

Posted on 2007-04-05
18
6,597 Views
Last Modified: 2012-08-14
Basically I want to the query to split comma delimited values into separate columns in access query

example:
 
Apple 1,4,5,6

Apple 1 | 4 | 5 | 6

How would I do this?
0
Comment
Question by:krisred
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 5
  • +2
18 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18859156
you need to use left,mid instr, or a function
is this a field in a table that contains  Apple 1,4,5,6
0
 

Author Comment

by:krisred
ID: 18859197
how would I write it so it picks first comma, then second comma, etc.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18859217
krisred,
can you pls answer the question first?
is this a field in a table that contains  Apple 1,4,5,6 ?


   

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:krisred
ID: 18859253
sorry, yes it is a field in a table.

Field Name: strMisc

This field contains comma seperated values 1,2,3,8 etc
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18859341
what is etc ? no fixed length ?
0
 

Author Comment

by:krisred
ID: 18859368
please ignore etc,there could be more than 4 comma separated values

0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18859407
so let me see if I understand what you want.  Do you need the Fruit Name ("Apple"), and the 4 comma delimited values, or just the values?

AW
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18859412
How is your VBA programming ability?  Do you need to do this in an SQL query.  Where are the values to be used?

AW
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18859422
and you want to separate each value to  different fields ?
4 values (1,2,3,4) to four different fields ?
0
 
LVL 6

Expert Comment

by:twintai
ID: 18859423
I would export the field to an excel file. Save the Excel file as a CSV file, which should capture the commas. Then import them into the Db again. When importing into the Db, I think there is a point that ask you if your columns are separated by commas. After importing you can recombine the data.
0
 

Author Comment

by:krisred
ID: 18859468
split comma delimited values in excel into column
0
 

Author Comment

by:krisred
ID: 18859518
Capricon1:
yes I want to separate each comma delimited value into a separate field


Arthur_Wood:
I am so so with VBA. but I want to do this in a query
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18859581
you cannot do this in a query alone.
you have to create a new table to hold the new values.
this will involve VBA codes, adding fields to the table on the fly.
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
ID: 18859973
This is VERY messy:

select Left(strMisc, InStr(strMisc, " ") - 1) as Fruit,
Left(Mid(strMisc, InStr(strMisc, " ") + 1), InStr(Mid(strMisc, InStr(strMisc, " ") + 1), ",") - 1) as Field1,
Left(Mid(Mid(strMisc, InStr(strMisc, ",") + 1), InStr(Mid(strMisc, InStr(strMisc, ",") + 1), ",") - 1), InStr(Mid(Mid(strMisc, InStr(strMisc, ",") + 1), InStr(Mid(strMisc, InStr(strMisc, ",") + 1), ",") - 1), ",") - 1) as Field2,
Left(Mid(Mid(Mid(strMisc, InStr(strMisc, ",") + 1), InStr(Mid(strMisc, InStr(strMisc, ",") + 1), ",") - 1), InStr(Mid(Mid(strMisc, InStr(strMisc, ",") + 1), InStr(Mid(strMisc, InStr(strMisc, ",") + 1), ",") - 1), ",") + 1), 1) as Field3,
Right(Mid(Mid(Mid(strMisc, InStr(strMisc, ",") + 1), InStr(Mid(strMisc, InStr(strMisc, ",") + 1), ",") - 1), InStr(Mid(Mid(strMisc, InStr(strMisc, ",") + 1), InStr(Mid(strMisc, InStr(strMisc, ",") + 1), ",") - 1), ",") + 1), 1) as Field4
From YOURTABLE

AW

0
 

Author Comment

by:krisred
ID: 18860602
Thanks Arthur_Wood. Its messy, but works for what I need.

0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18861139
glad to be of assistance.

AW
0
 

Expert Comment

by:mobile018
ID: 22384641
hi authur_wood,

may i know how about this sample  data:

8/27/2008 22:00,5,5,5,2,0,490,366,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,98,183

for your information, the length of the data is not fixed
can you guide me?thanks in advance..=)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 22386005
That is different from what you first described, since the first value (8/27/2008 22:00)  has en EMBEDDED blank, so the code I gave you will not work, since it uses the blank to demark the 'label', which preceeds the comma delimited values (Apple 1,4,5,6).  And since you have a variable number of value is the csv section, this cries out to be handled in code ( meaning VBA), not in SQL.  The SQL would be EXTREMELY convoluted, and VERY VERY error prone.
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question