Text to Column in Access

[Guest]

Greetings, I have a database that imports financial data and then groups it
based upon certain criteria such as accounting unit, division, department,
category, etc. Another division is now sending me there information for
processing. The information when provided has a 22 digit field that contains
data that needs to be split into four columns. This month I exported to
excel and used the text to column feature. Unfortunately there are generally
more that 150,000 records so as you can imagine that is not a really good
option going forward. I am afraid to ask them to alter how they present
their data. Any suggestions for splitting the field into four columns based
upon character spacing directly in Access? Any assistance would be greatly
appreciated. -John

 

[Douglas J Steele]

You need to provide a little more information.

Is the text delimited, or is it fixed width?

If it's delimited, you can use the Split function to break it into its
component values.

If it's fixed width, you can use a combination of the Left, Right and Mid
functions to extract the individual parts.

My advice would be to import the data into a temporary table, and then use
functions to break the 22 digit field into the four separate fields in an
INSERT INTO query.

If, for illustration purposes, the field is comma-delimited,you'd use the
Split function along the lines of:

INSERT INTO MyRealTable (Field1, Field2, Field3, Field4)
SELECT Split(BigField, ",")(0), Split(BigField, ",")(1),
Split(BigField, ",")(2), Split(BigField, ",")(3)
FROM MyTempTable

If it's fixed width, and you want the first 4 digits in one field, 5 digits
in the second field, 7 digits in the third field and 6 digits in the fourth
field, it would be something like

INSERT INTO MyRealTable (Field1, Field2, Field3, Field4)
SELECT Left(BigField, 4), Mid(BigField, 5, 5),
Mid(BigField,10, 7), Right(BigField, 6)
FROM MyTempTable

 

[Guest]

OOps, It is fixed width, I apologize for ommitting that. I appreciate the
reponse and I am going to play with your suggestions right now. Thank you
VERY VERY much. -John

 

[Guest]

If the data is consistent in it's structure you can use --

Your Column: Mid([YourField],X,Y)

Replace "Your Column" with the column name you want to use, X is the
starting position in the field, and Y is how many characters for the output
field.

 

[Guest]

I just wanted to thank you for your assistance, the query works splendidly now.

-John

If the data is consistent in it's structure you can use --

Your Column: Mid([YourField],X,Y)

Replace "Your Column" with the column name you want to use, X is the
starting position in the field, and Y is how many characters for the output
field.

Greetings, I have a database that imports financial data and then groups it
based upon certain criteria such as accounting unit, division, department,
category, etc. Another division is now sending me there information for
processing. The information when provided has a 22 digit field that contains
data that needs to be split into four columns. This month I exported to
excel and used the text to column feature. Unfortunately there are generally
more that 150,000 records so as you can imagine that is not a really good
option going forward. I am afraid to ask them to alter how they present
their data. Any suggestions for splitting the field into four columns based
upon character spacing directly in Access? Any assistance would be greatly
appreciated. -John

 

[Guest]

I just wanted to thank you for your assistance, the query works fabulously now.

-John