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
Solved

split comma delimited values into seperate columns

Posted on 2007-04-05
18
6,594 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

807 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