February 27, 2020 at 1:37 pm
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
February 27, 2020 at 2:08 pm
Please provide sample data & DDL, so that we can see more clearly what you are trying to achieve.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 27, 2020 at 2:16 pm
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
February 27, 2020 at 3:14 pm
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
February 27, 2020 at 3:35 pm
'BSNC001481905191912080000001 PEE5P01 124650005 EMU 090D P'
my initial cut and paste got squeezed
February 27, 2020 at 3:40 pm
'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
February 27, 2020 at 3:45 pm
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
February 27, 2020 at 3:53 pm
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
February 27, 2020 at 4:10 pm
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 ...
February 27, 2020 at 4:40 pm
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;
February 27, 2020 at 4:54 pm
hi,
Thanks for that certainlytime saving pulling column names in and constructing the sql code 🙂
regards
Andrew
February 27, 2020 at 4:56 pm
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.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 27, 2020 at 4:57 pm
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
February 27, 2020 at 5:04 pm
Yes, I totally agree unfortunately that is not possible to have the format changed ...
Sometimes we have to work with what we are given ....
🙂
February 27, 2020 at 5:36 pm
Is this coming to you in a file?
Viewing 15 posts - 1 through 15 (of 73 total)
You must be logged in to reply to this topic. Login to reply