[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6602
  • Last Modified:

split comma delimited values into seperate columns

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
krisred
Asked:
krisred
  • 6
  • 5
  • 5
  • +2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
krisredAuthor Commented:
how would I write it so it picks first comma, then second comma, etc.
0
 
Rey Obrero (Capricorn1)Commented:
krisred,
can you pls answer the question first?
is this a field in a table that contains  Apple 1,4,5,6 ?


   

0
Technology Partners: 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!

 
krisredAuthor Commented:
sorry, yes it is a field in a table.

Field Name: strMisc

This field contains comma seperated values 1,2,3,8 etc
0
 
Rey Obrero (Capricorn1)Commented:
what is etc ? no fixed length ?
0
 
krisredAuthor Commented:
please ignore etc,there could be more than 4 comma separated values

0
 
Arthur_WoodCommented:
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
 
Arthur_WoodCommented:
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
 
Rey Obrero (Capricorn1)Commented:
and you want to separate each value to  different fields ?
4 values (1,2,3,4) to four different fields ?
0
 
twintaiCommented:
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
 
krisredAuthor Commented:
split comma delimited values in excel into column
0
 
krisredAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Arthur_WoodCommented:
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
 
krisredAuthor Commented:
Thanks Arthur_Wood. Its messy, but works for what I need.

0
 
Arthur_WoodCommented:
glad to be of assistance.

AW
0
 
mobile018Commented:
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
 
Arthur_WoodCommented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 6
  • 5
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now