Help Split data of 1 field into 4 different fields

[jdawg_1989]

I have a table that has the following content in it.

Field1.
.1/1/1/1
.1/1/2/1
.1/1/3/1
.1/1/4/1

and so on...

I want to split the records across 4 new fields so it becomes.
Field 2, Field 3, Field 4, Field 5
.1, 1, 1, 1
.1, 1, 2, 1
.1, 1, 3, 1
.1, 1, 4, 1

I import this field from a .csv file on a regular basis so I want to write some code that I can run after the import.

Thanks in advance for you help.

 

[DCrake]

Is the backslash the delimiter that is constant thoughout the file?
Are all the values numeric?
Does the file contain column headings?
Can you supply a sample file?

 

[jdawg_1989]

Is the backslash the delimiter that is constant thoughout the file?
Are all the values numeric?
Does the file contain column headings?
Can you supply a sample file?

Thanks for the prompt reply!

1) Yes, the backslash is the constant delimiter through that field for every record. But not the file.
2) Yes, they are all numeric values
3) Yes the files contains colum headings.
4) attached is a spreadsheet which shows an example of what I'm trying to achieve, I'm hoping to link some VBA code to a button on a form.

Thanks

 

[DCrake]

Here Goes

Step 1:Link the spreadsheet to the database or import - your preference

Step 2:Copy the following function into a standard module

Public Function ParsePorts(AnyString As String, Element As Integer) As Integer

Dim MyArray
'/Drop any leading dots
AnyString = Replace(AnyString, ".", "")

'/Expunge to an array
MyArray = Split(AnyString, "/")

'/return the port requested
ParsePorts = MyArray(Element - 1)

End Function

Step 3:Create a new query and construct as shown below.

Step 4:
Run Query in datasheet view

As you can see ther is no need to parse the data into different fields as the function extracts the elements for you.

 

[jdawg_1989]

Hi,

Thanks for the code.

Step 3 is missing, I'm still in the dark in implementing the code, I've been playing around with no result.

I'd just like to point out that the .csv file is already in the DB and gets imported to the database from a sever via VB code linked to a button on a form. If this changes anything??

Appologies, but I am currently self teaching!

 

[DCrake]

When you say Step 3 is missing is there no image?

Here is the SQL for the query I created

SELECT [Equipment Number], [Port No], ParsePorts([Port No],1) AS Port1, ParsePorts([Port No],2) AS Port2, ParsePorts([Port No],3) AS Port3, ParsePorts([Port No],4) AS Port4
FROM [B]Sheet1[/B];

Where Sheet1 is the name of the table I linked. In your case you need to refer to the table that has been imported.

Basically all you need is the function and the sql for the query usage.

 

[jdawg_1989]

I think I need 10 Posts before I can see attached and embedded images

I've copied and pasted the SQL into my query but I am getting a "Undefined function 'ParsePorts' in expression. I have checked for typos and haven't found any.

The code is definately in a module and saved. Any ideas?

Thanks.

 

[DCrake]

Have you saved the module the same name as the function. If so rename the module.

I have attached images to this post.

 

[jdawg_1989]

Thats exactly what I did! Renamed it and it's all working now!

Thanks ever so much for all your help! Much appreciated.

 

[DCrake]

Glad I could help.

 

[jdawg_1989]

I'm back again,

if i change the query from a select query to make table I get the following Error "Run-time error 9: Subscript out of range"

Public Function ParsePorts(AnyString As String, Element As Integer) As Integer

Dim MyArray
'/Drop any leading dots
AnyString = Replace(AnyString, ".", "")

'/Expunge to an array
MyArray = Split(AnyString, "/")

'/return the port requested
ParsePorts = MyArray(Element - 1)

End Function

VB is highlighting the following line in yellow;

ParsePorts = MyArray(Element - 1)

 

[DCrake]

This implies you have a rouge record that does not contain 3 /'s check you source data.

 

[jdawg_1989]

I've found the problem, there was some rogue data in there such as '.A/Mgt/1'

As I am not intestested in this data I have filtered it out before running the query,

Thanks again! Was driving me crazy trying to figure it out, you nailed it in one!