Before starting, it would help if we had some raw data, example outputs, and what you've tried. Without these I'm taking a (hopefully decent) guess as to data and data structures.
For this answer, I'm assuming you have the raw data in a single table, with each row representing what you want as one row in your answer (e.g., a spatial line). Let's call the flat file table 'LoaderTable' with a single column 'txtField' with your data.
Now, on to the answer.
Do you need a loop?
No.
There is definitely no need for a loop to go through line-by-line.
Imagine you have a function or some sort of processing that
- Gets one line (txtField) from LoaderTable and keeps it as a variable (say, @CurrentTxtField)
- Calculates the four fields from the @CurrentTxtField
- Discards the line matching @CurrentTxtField
- Gets the next line from LoaderTable into @CurrentTxtField
Not a massive amount of code, but the alternative is
- Calculates the four fields from all the lines in LoaderTable
You still need the same logic to split the line into four new fields; just that in first option (the loop) you need to run it many times, and have a lot of code around it to support the loop. In the second option, it runs once, and doesn't need all the extra code.
How to split the single field into four columns
There are many ways to do this - you can search here for approaches e.g., How to split a comma-separated value to columns (but particularly look at @Shnugo's comment on the accepted answer) or Split column based on ascii value
If you don't understand those and want a simpler answer, you can do it with some simple code. Basically,
- Copy the source data into a temporary table (just in case) with the four additional fields
- Calculate the first field, and remove that from the initial field
- Calculate the second field, and remove that from the initial field
- Do same for fields 3 and 4
e.g.,
CREATE TABLE #TempLoaderTable (
txtField nvarchar(100),
[Lat] nvarchar(25),
[Lon] nvarchar(25),
[Bearing] nvarchar(25),
[Distance] nvarchar(25)
)
INSERT INTO #TempLoaderTable (txtField)
SELECT txtField
FROM LoaderTable
-- Split data assuming commas separate values in txtField
-- Note that the STUFF on txtField is used to remove the saved text from txtField
UPDATE #TempLoaderTable
SET [Lat] = LEFT(txtField, CHARINDEX(',', txtField) - 1),
[txtField] = STUFF(txtfield,1,CHARINDEX(',', txtField),'')
FROM #TempLoaderTable
UPDATE #TempLoaderTable
SET [Lon] = LEFT(txtField, CHARINDEX(',', txtField) - 1),
[txtField] = STUFF(txtfield,1,CHARINDEX(',', txtField),'')
FROM #TempLoaderTable
UPDATE #TempLoaderTable
SET [Bearing] = LEFT(txtField, CHARINDEX(',', txtField) - 1),
[txtField] = STUFF(txtfield,1,CHARINDEX(',', txtField),'')
FROM #TempLoaderTable
UPDATE #TempLoaderTable
SET [Distance] = [txtField],
[txtField] = NULL
FROM #TempLoaderTable
Here's a DB<>fiddle showing results of each step.
Note that the above is written for simplicity and is not the most efficient as it does 4 updates rather than 1. Usually 4 of these would not be an issue (probably in the terms of milliseconds). If you do need it faster, the linked posts above can point you in the right direction.
If the data is actually in a fixed format (e.g., the values are all the same length for every row - so the commas would be in the same column number) you can use just a single UPDATE statement using SUBSTRING where you set the start point and length appropriately e.g. if,
- Lat and Lon are 15 characters each
- Bearing is 10 characters
- Distance is 7 characters
- all fields separated by 1 character
... you can use one update in the above e.g.,
UPDATE #TempLoaderTable
SET [Lat] = LEFT(txtField, 15),
[Lon] = SUBSTRING(txtField, 17, 15),
[Bearing] = SUBSTRING(txtField, 33, 10),
[Distance] = SUBSTRING(txtField, 44, 7)
(Obviously, you need to tweak the exact values to the actual lengths).
This would run very efficiently.
