[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

split comma delimited values into seperate columns

Posted on 2007-04-05
18
Medium Priority
?
6,600 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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: …

656 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