Hi Maxi
Yes you can do this with a couple of queries but be aware that it is very very sloooooooow. VB may be quicker but I'm no good with it. You might want to do this on a speedy PC and definitely don't do this over a network. I tested this with the sample data you provided and whilst it worked ok, it was painfully slow.
You would first need to create a new table with the 9 fields formatted as numbers (plus I included the original csv value also). For the purposes of my append query I used the table name "tblMaxiValues".
Query One
The first query calculates the positions of the comma within the csv field (the POSx columns) and strips out the values based on the positions of the commas (the Valuex columns).
The SQL for the first query follows (please note I used the field name [csv] and the table name tblMaxi - you will need to change these to your actual table and field names) :
SELECT tblMaxi.csv, InStr([csv],",") AS Pos1, [Pos1]+InStr(Right([csv],Len([csv])-[Pos1]),",") AS Pos2, [Pos2]+InStr(Right([csv],Len([csv])-[Pos2]),",") AS Pos3, [Pos3]+InStr(Right([csv],Len([csv])-[Pos3]),",") AS Pos4, [Pos4]+InStr(Right([csv],Len([csv])-[Pos4]),",") AS Pos5, [Pos5]+InStr(Right([csv],Len([csv])-[Pos5]),",") AS Pos6, [Pos6]+InStr(Right([csv],Len([csv])-[Pos6]),",") AS Pos7, [Pos7]+InStr(Right([csv],Len([csv])-[Pos7]),",") AS Pos8, Left([csv],[Pos1]-1) AS Value1, Mid([csv],[Pos1]+2,[Pos2]-[Pos1]-2) AS Value2, Mid([csv],[Pos2]+2,[Pos3]-[Pos2]-2) AS Value3, Mid([csv],[Pos3]+2,[Pos4]-[Pos3]-2) AS Value4, Mid([csv],[Pos4]+2,[Pos5]-[Pos4]-2) AS Value5, Mid([csv],[Pos5]+2,[Pos6]-[Pos5]-2) AS Value6, Mid([csv],[Pos6]+2,[Pos7]-[Pos6]-2) AS Value7, Mid([csv],[Pos7]+2,[Pos8]-[Pos7]-2) AS Value8, Right([csv],Len([csv])-[Pos8]-1) AS Value9
FROM tblMaxi;
I named the query "qryMaxiCommas" - if you save the query as another name then it will impact the SQL for the 2nd query which appends the values from the first query into the new table.
Query Two
The 2nd query appends the values into your new table (I used the variable names 1 through 9 and I included the original csv variable too) I used the table name "tblMaxiValues" - there is probably no reason why you can't create the new fields in the original table and append them back there but I didn't try that. The SQL for the 2nd query follows :
INSERT INTO tblMaxiValues ( csv, 1, 2, 3, 4, 5, 6, 7, 8, 9 )
SELECT qryMaxiCommas.csv, qryMaxiCommas.Value1, qryMaxiCommas.Value2, qryMaxiCommas.Value3, qryMaxiCommas.Value4, qryMaxiCommas.Value5, qryMaxiCommas.Value6, qryMaxiCommas.Value7, qryMaxiCommas.Value8, qryMaxiCommas.Value9
FROM qryMaxiCommas;
If you have any questions or issues with this, please ask.
HTH, Andrew.
