Comma separating text in field in MS Access 2016 Query

Question

I have a csv file (updated 3 times a week. I have no control over its format, so cannot normalise it) that I have created an OBDC-link to a MS Access 2016 database.(I have chosen MS Access to refresh my skills with it, otherwise would complete in SQL). I have tried various permutations in setting up the OBDC link for the linked table however none give the optimum structure for the other fields.

The CSV file looks like this:

Fecha de Sorteo,Numero de sorteo,Numero de Juego,Nombre,Valores Principales,Comodines,DRAWNAME,Ganadores de Premio Mayor,Premio Mayor Garantizado
"8/25/2002 12:00:00 AM","1714","1","main","31,34,26,1,2,28","16","Loto","0",

I am trying to create a query to comma separate Field 3 into its 6 component parts. I have seen many examples that separate either 2 or 3 components (but never more than that) using InStr and the Mid functions such as seen here.

Do I have to create multiple expressions to separate this field into its components or is their an alternate solution?

see if this helps https://msdn.microsoft.com/en-us/library/6x627e5f(v=vs.90).aspx — Don George, Nov 24 '15 at 16:21

score 0 · Answer 1 · answered Nov 24 '15 at 16:29

I would suggest writing the data to a local table using the Split function. It will allow you to split that field into an array. Then use VBA to write the whole record into Access.

So, something like:

Dim db as Database
Dim rec as Recordset
Dim rec2 as Recordset

Set db = CurrentDB
Set rec = db.OpenRecordset("SELECT * FROM MyLinkedTable")
Set rec2 = db.OpenRecordset("SELECT * FROM MyLocalTable")

Do while rec.EOF = False
  rec2.AddNew
  rec2("Field1") = rec("Field1")
  rec2("Field2" = rec("Field2")
  strArray = Split(rec("Field3"), ",")
  rec2("Part1") = strArray(0)
  rec2("Part2") = strArray(1)
  etc...
  rec2.Update
rec.MoveNext

The above is "aircode" and completely untested, but it's probably pretty accurate and should get you started.

I just want to note that my answer was submitted before your CSV sample change, so while Field3's position is no longer accurate it's still a valid way to handle your issue with a little extra code tweaking. — Johnny Bones, Nov 24 '15 at 18:29

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

So your file seems to follow CSV specs, where values can be put between brackets ...

You should then be able to open it directly as an ADODB recordset(*). The column corresponding to the "Valores Principales" data will contain a plain text string, being your 6 values, separated by comas.

(*) don't know if it works with DAO recordsets ... For an example, check this link (it's excel but it's the same logic): return csv file as recordset

edited May 23 '17 at 12:23

Community

1
1

answered Nov 24 '15 at 16:33

Philippe Grondier

10,900
3
33
72

Thanks. I have updated the question to include a clip off the actual csv. (--- was just my own bad markup). – Nov 24 '15 at 16:35
And is 'field3' a multivalued field, or just one plain standard field? – Philippe Grondier Nov 24 '15 at 16:38
A standard field with six values comma seperated. – Nov 24 '15 at 16:39
What do you want to do with the components after you *"separate this field into its components"*? – HansUp Nov 24 '15 at 17:00
Add each one of the six into its own field. – Nov 24 '15 at 17:35

Comma separating text in field in MS Access 2016 Query

2 Answers2