Query to split string (1 Viewer)

[Stefano919191]

Good Morning to all,
my name is Stefano and I write from Italy.

I'm not an expert about Access but I'm developing a new program/database for my company.

I have a DB (DB1) fixed in a folder that I choose. In this db there is just a table with records wrote by a external machines.
In this DB I have a string field (example string: a;b;c;d;e;f;g and I need to split this string in a different table and in more field in an other db (DB2). (first text of string into first field of Table2 and DB2)

How can I do?

I'm sorry for my bad language and thanks in advance to all.
Bye

 

[CJ_London]

not enough information to provide a detailed solution but two possible solutions come to mind

one is to look at using the split function, the other is to extract the string field to a text file then use transfertext to import the data to your other table. It may be that is how your data was supplied originally so you may already have the text file.

If you want a more detailed solution, provide the following:

1. explain how the table with the string field is currently populated - imported from a file? if so provide details of the file - text? excel? something else? i.e. what does 'with records wrote by a external machines' mean?
2. provide the field names and types for the table in table2
3. expand on this '(first text of string into first field of Table2 and DB2)' what about the second, third, etc? or do you only want the first?
4. Is this a one off exercise or one to be run on a regular basis? If the latter, do you need to ignore values that have already been imported?

5. It always helps if your provide some realistic example data - example string: a;b;c;d;e;f;g doesn't really cover it. Perhaps the data contains special characters, or one time there are 7 elements of your string, another time there are 6 or 10. Perhaps some of the elements are null. I would hate to spend time helping you for your to then come back and say something like this also needs to be catered for.
6. Also helps if you can illustrate the outcome you require from that example data.

If language is an issue, try using google translate to convert your italian to english

 

[The_Doc_Man]

If your field has a common delimiter (for instance, the semicolon - as you showed us a;b;c;d ...) then perhaps you can look at the Split function

VBA Split Function - Split String of Text into Array - Automate Excel

In this ArticleVBA Split FunctionUsing the Split Function with a Delimiter CharacterUsing a Limit Parameter in a Split FunctionUsing the Compare Parameter in a Split FunctionUsing Non-Printable Characters as the Delimiter CharacterUsing the Join Function to Reverse a SplitUsing the Split...

www.automateexcel.com

That was actually an Excel reference but Split works well in Access too. You might write a loop that goes through one record at a time from your source (you called it DB1) to read that string, split it into an array, and deposit each element of the array into each field of a record in our desired output table. You probably do NOT want to initially have this in two databases. You could export the output table as a separate step.

If your field delimiter is NOT consistent, this idea might be more trouble than it is worth.