UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Comma Separated Values, Access 2016    
 
   
InfoHound
post Jun 1 2019, 05:57 PM
Post#1



Posts: 1,160
Joined: 1-December 12



Is there a way to retrieve date from a csv list by just using built-in functions to do the job?

For example I'd like to retrieve 7 values from a list of numbers.


"12,345,2,356,678,89,1"

I'm only successful in getting results from a list contain 3 values. After that I fail.

Thanks


Go to the top of the page
 
RJD
post Jun 1 2019, 06:17 PM
Post#2


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


Hi: I assume you mean "Retrieve Data" rather than "Retrieve Date" - right?

Have you tried the Split function approach in a UDF function you write?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
GroverParkGeorge
post Jun 1 2019, 06:22 PM
Post#3


UA Admin
Posts: 36,051
Joined: 20-June 02
From: Newcastle, WA


Maybe you can help by describing you are able to get the first 3 strings? And what does it mean to say, "After that I fail." Errors? Wrong values? Other problems?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
RJD
post Jun 1 2019, 06:32 PM
Post#4


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


Hi again: I quickly threw together a function to get whatever value you want from the string, using the Split function. Here's the function ...

Public Function SplitCSV(TheString As String, FNo As Long) As Long
Dim FieldWithCommas As Variant
FieldWithCommas = Split(TheString, ",")
SplitCSV = FieldWithCommas(FNo)
End Function

This will pick whatever value you want by identifying the field and which value you want. Note that the value positions are zero based.

SELECT ID, MyField, SplitCSV([MyField],0) AS Val01, SplitCSV([MyField],1) AS Val02, SplitCSV([MyField],2) AS Val03, SplitCSV([MyField],3) AS Val04
FROM tblMyRecords;

This gets the first 4 values, but you can add the rest.

HTH
Joe
Attached File(s)
Attached File  CSV_Split.zip ( 19.51K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
InfoHound
post Jun 1 2019, 07:03 PM
Post#5



Posts: 1,160
Joined: 1-December 12



Thanks all, and yes I meant to print data not date.

would you recommend doing something like this:

instr((instr(openargs,",")+1),openargs,",")


Go to the top of the page
 
RJD
post Jun 1 2019, 07:26 PM
Post#6


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


Without seeing your whole db and process, it would be hard to comment on the method you just showed. But, while I use Instr a good bit, it is not something I would use to find all components (or a particular component) of a string with many (perhaps variable number of) values separated by commas.

Did you try the approach I posted? I notice the attachment has not been downloaded. The UDF is very simple, and simple to use in a query. And the function can be easily modified to check if you are asking for a value position beyond those available in a particular string, in case of a variable number of positions.

Your use of OpenArgs in your example also tells me something is going on in what you are doing to which we have no visibility.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
InfoHound
post Jun 1 2019, 07:40 PM
Post#7



Posts: 1,160
Joined: 1-December 12



Yes I did try it and it works great.

Thank you very much Joe
Go to the top of the page
 
RJD
post Jun 1 2019, 07:49 PM
Post#8


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Happy to help. thumbup.gif

Do you ever have a variable number of components in the string? The function can be changed so as not to error if a position not there is requested. If so, you can give it a shot, and we'll be happy to assist if you should need any guidance on that (I suspect you can deal with this just fine yourself!).

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th November 2019 - 03:18 AM