?
Solved

split comma delimited values into seperate columns

Posted on 2007-04-05
18
Medium Priority
?
6,599 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

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.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 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