Taking a single 80 character piece of Text and converting to columns in a table.

  • Hi,

     

    I have a 80 character piece of text that I wish to convert to a table structure that represents that data.

    Is there a simple way to take those 80 characters and basically insert them into the table structure without doing multiple substring commands for the individual columns.

     

    Thanks in advance

    Andrew

  • Please provide sample data & DDL, so that we can see more clearly what you are trying to achieve.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • BSNC001481905191912080000001 PEE5P01 124650005 EMU 090D P

    and for that to be translated to this table structure

    [recordidentity] [nchar](2) NOT NULL,

    [transactiontype] [nchar](1) NULL,

    [trainuid] [nvarchar](6) NULL,

    [daterunsfrom] [nvarchar](6) NULL,

    [daterunsto] [nvarchar](6) NULL,

    [daysrun] [nvarchar](7) NULL,

    [bankholiday running] [nchar](1) NULL,

    [trainstatus] [nchar](1) NULL,

    [traincategory] [nchar](2) NULL,

    [trainidentity] [nvarchar](4) NULL,

    [headcode] [nvarchar](4) NULL,

    [courseindicator] [nchar](1) NULL,

    [trainservicecode] [nvarchar](8) NULL,

    [portionid] [nchar](1) NULL,

    [powertype] [nvarchar](3) NULL,

    [timingload] [nvarchar](4) NULL,

    [speed] [nvarchar](3) NULL,

    [operatingcharacteristics] [nvarchar](6) NULL,

    [seatingclass] [nchar](1) NULL,

    [sleepers] [nchar](1) NULL,

    [reservations] [nchar](1) NULL,

    [connectionindicator] [nchar](1) NULL,

    [cateringcode] [nvarchar](4) NULL,

    [servicebranding] [nvarchar](4) NULL,

    [spare] [nchar](1) NULL,

    [stpindicator] [nchar](1) NULL

    and current using mulitple substring commands to get data into the correct columns.

     

    Regards

    Andrew

  • How to you get 26 different columns from the string 'BSNC001481905191912080000001 PEE5P01 124650005 EMU 090D P'? I see 6 at most. Where are the other 20 coming from?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • 'BSNC001481905191912080000001 PEE5P01 124650005 EMU 090D                  P'

     

    my initial cut and paste got squeezed

  • Andrew Goffin-302397 wrote:

    'BSNC001481905191912080000001 PEE5P01 124650005 EMU 090D                  P'

    my initial cut and paste got squeezed

    That still looks like 6/7 columns to me:

    Col1                         |Col2    |Col3      |Col4 |Col5 |<-White space?-> |Col6                     
    -----------------------------|--------|----------|-----|-----|-----------------|----
    BSNC001481905191912080000001 |PEE5P01 |124650005 |EMU |090D |                 |P

    You haven't explained how you get 26 columns out of that string.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • you have assumed that the spaces are delimeters and there are none its 80 characters long and the spaces are empty fields/columns

     

    the data is from a fixed width data stream, hence the number of columns

  • Andrew Goffin-302397 wrote:

    you have assumed that the spaces are delimeters and there are none its 80 characters long and the spaces are empty fields/columns

    the data is from a fixed width data stream, hence the number of columns

    But you haven't told us the data is fixed width, you just asked how to you turn the data into columns. We don't know your data so you need to explain it to us; I've asked twice and you haven't so of course I'm guessing because I don't have the information I need to do anything else.

    If it's fixed width, what's wrong with SUBSTRING though?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • nothing wrong with substring but if you have a lot of records, its a lot of string manipulation just strikes me, if you have a string in one format if would be handy to  get it in the second format which is also all character based?  just a shame you can't overlay into the new format.

     

    I did some timing tests and it will process these records  at a rate of 140777 per minute, I was just wondering whether it could make it quicker and not using substring ...

     

  • You can generate SUBSTRINGs with something like:

    WITH StringOffSets
    AS
    (
    SELECT
    COALESCE
    (
    SUM(CHARACTER_MAXIMUM_LENGTH) OVER(ORDER BY ORDINAL_POSITION ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    ,0
    ) + 1 AS StartPos
    ,CHARACTER_MAXIMUM_LENGTH AS ColLen
    ,COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'YourTable'
    )
    SELECT ',SUBSTRING(@YourString,' + CAST(StartPos AS varchar(20)) + ', ' + CAST(ColLen AS varchar(20)) + ') AS [' + COLUMN_NAME + ']'
    FROM StringOffSets;

    • This reply was modified 1 year, 6 months ago by  Ken McKelvey.
  • hi,

     

    Thanks for that certainlytime saving pulling column names in and constructing the sql code 🙂

     

    regards

    Andrew

  • Andrew Goffin-302397 wrote:

    I did some timing tests and it will process these records  at a rate of 140777 per minute, I was just wondering whether it could make it quicker and not using substring ...

    Honestly, that sounds pretty slow. I just took a 40 character string, and used SUBSTRING to split it into 1 character strings. I left the query running for 5 minutes, and it had returned 13,630,083 rows to the presentation layer; that's over 2.7 million rows a minute; way faster that the 140,000~ you have.

    If I INSERT the rows into a (temporary) table, then 25,283,211 rows were insert int  3 minutes 2 seconds, that's a little under 8.5M rows a minute.

    • This reply was modified 1 year, 6 months ago by  Thom A.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • my advice - fix the source file, put a delimiter in there and then have whoever designed that file format go and work for someone else.

    In my experience it's a lot easier if you get good data in  (The GIGO principal). plus it takes the liability for a failed import off your shoulders.

    MVDBA

  • Yes, I totally agree unfortunately that is not possible to have the format changed ...

     

    Sometimes we have to work with what we are given ....

     

    🙂

  • Is this coming to you in a file?

     

Viewing 15 posts - 1 through 15 (of 74 total)

You must be logged in to reply to this topic. Login to reply