How can I parse comma delimited string into columns
-
Hi!
I did locate on this site a user defined function that will take a comma delimited string and return one row in a constructed table for each parsed string (between the commas). I have a slightly different problem, though. I need to parse rows of comma delimited strings into columns.
I've created a table called ImportedData to receive the data with 30 columns for the data, a column for the row number, and a column for the number of actual columns in that row. The table looks like this:
RowNum int
NumCols int
Col1 varchar(60)
Col2 varchar(60)
Col3 varchar(60)
. . .
Col30 varchar(60)
This is a download from our bank. Each row of the imported text is actually fixed length - 80 characters. But, within each line of the text, the data items are separated by commas and there is a forward slash at the logical end of the text (followed by as many spaces as necessary to make 80 columns). Each row can have a different number of comma-separated text items. It can look something like this (though I can't show the spaces here that fill each line out to 80 characters):
01,090404,293072,Daily Summary/
873,702984,F87RE,Credit,TransferFunds,49800/
92,7898,03,Jan04 balance,,,80-5,78,35,983/
Right now, what I get back from the UDF I found for the first row is four rows that look like:
01
090404
293072
Daily Summary
The result I need (though I can't show every resulting column here) is:
Row Num Col1 Col2 Col3 Col4 Col5
num Cols
--- ---- ---- ------ ------- ------------- ------------
1 4 01 090404 293072 Daily Summary
2 6 873 702984 F87RE Credit Transfer...
3 10 92 7898 03 Jan04 balance (NULL)...
Right now, I'm trying to use the UDFwhich returns a row for each comma delimited string, and then cycle through the rows it returns, and then feed each row into the columns of the final destination table one at a time, but that seems like a really hard way to do this.
Does anyone know of a routine like the one I'm using that will return a single row but with the data in columns instead of returning multiple rows? Or is there a really easy way to pivot the rows I get back from the UDF into columns?
Thanks
Karen Grube
-
-
Hi!
Thank you for trying! But This isn't truly a comma delimited file (like a CSV). The number of commas varies for each row and there's that slash mark in there that indicates the logical end of the record (not the physical end), so using DTS or Bulk Insert isn't an option. I thought about that, though!
So, I'm still looking for suggestionsif anyone has any!
Thanks,
Karen
-
Another thing you could do is create a temporary table that has only one field: x char(80). Then you load your file into this table, one line in the file giving you one row in the table with all the commas and the slash in one field. Then you'd have to write a procedure that parses these strings, takes them apart and then does an INSERT on your real table. It's also brute force, but maybe a little less ugly than having multiple rows in a table for each line in the file.
Here is a quickly written and untested chunk of code that you could put in a stored proc to parse your temp table. (Indenting with blanks doesn't seem to work here, so I used dots.)
DECLARE inpStr CHAR(80)
DECLARE field1 VARCHAR(60)
DECLARE field3 VARCHAR(60)
DECLARE field3 VARCHAR(60)
DECLARE fNo INT
DECLARE i INT
DECLARE c CHAR(1)
DECLARE cur CURSOR FOR SELECT x FROM theLittleTable
OPEN cur
FETCH FIRST FROM cur INTO @inpStr
WHILE @@FTECH_STATUS = 0
..BEGIN
....
....SET @field1 = ""
....SET @field3 = ""
....SET @field2 = ""
....SET @fNo = 1
....SET @i = 1
....SET @C = SUBSTRING(@inpStr, @i, 1)
....
....WHILE (@i <= 80) AND (@c "/")
......BEGIN
........IF @C = ","
..........SET @fNo = @fNo + 1
........ELSE IF @fNo = 1
..........SET @field1 = @field1 + c
........ELSE IF @fNo = 2
..........SET @field2 = @field2 + c
........ELSE IF @fNo = 3
..........SET @field3 = @field3 + c
........SET @i = @i + 1
........SET @C = SUBSTRING(@inpStr, @i, 1)
......END
....
....INSERT INTO theBigTable VALUES (@field1, @field2, @field3)
....
....FETCH NEXT FROM cur INTO @inpStr
....
..END
CLOSE cur
DEALLOCATE cur
-
Hi,
If you're willing to use Excel then have your conglomerated data set in column A and under the Data menu select Text to Columns and use comma as the field separator.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply