Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Text to Column in Access

Discussion in 'Information Technology' started by H0MELY, Nov 2, 2005.

  1. H0MELY

    H0MELY
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    Malaysia Hindus, Sikhs In Malaysia Appalled With University Textbook Ridiculing Their Faiths Breaking News Jun 14, 2016
    Hard Talk Taking One Liners Out Of Context From Gurbani To Allow Intellectual Independence? Hard Talk Apr 23, 2016
    India Protests in India Over the Desecration of a Holy Text Leave Two Dead Breaking News Oct 16, 2015
    Learn Punjabi Convert Punjabi text Language, Arts & Culture Oct 11, 2013
    Sikh Coalition Harmeet Kaur Took the Lead ! First to Respond to Textbook Challenge ! Sikh Organisations May 25, 2013

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "H0MELY" <H0MELY@discussions.microsoft.com> wrote in message
    news:DB965D91-57C4-4951-B0FC-EEC41FD52B35@microsoft.com...
    > 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
     
  4. H0MELY

    H0MELY
    Expand Collapse
    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

    "Douglas J Steele" wrote:

    > 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
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "H0MELY" <H0MELY@discussions.microsoft.com> wrote in message
    > news:DB965D91-57C4-4951-B0FC-EEC41FD52B35@microsoft.com...
    > > 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

    >
    >
    >
     
  5. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    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.

    "H0MELY" wrote:

    > 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
     
  6. H0MELY

    H0MELY
    Expand Collapse
    Guest

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

    -John

    "KARL DEWEY" wrote:

    > 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.
    >
    > "H0MELY" wrote:
    >
    > > 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
     
  7. H0MELY

    H0MELY
    Expand Collapse
    Guest

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

    -John

    "Douglas J Steele" wrote:

    > 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
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "H0MELY" <H0MELY@discussions.microsoft.com> wrote in message
    > news:DB965D91-57C4-4951-B0FC-EEC41FD52B35@microsoft.com...
    > > 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

    >
    >
    >
     

Share This Page