holemania
asked on
SQL String parse comma separated
I am reading a text file into a temp table and it's just 1 long line separated by commas. I would like to parse this string into separate fields. How can I do that with a function or sql script?
Example:
ECON,2992,10/11/2013,6:00A
MATH,29332,10/11/2013,90 T
FIN,3293,10/11/2013,8:00AM
I may have to do multiple different parse and put into a temp table since some line have more fields then others. If I can parse the above regardless of how many commas into a define table, I should be able to do the rest. Just having issue getting it to parse.
Example:
ECON,2992,10/11/2013,6:00A
MATH,29332,10/11/2013,90 T
FIN,3293,10/11/2013,8:00AM
I may have to do multiple different parse and put into a temp table since some line have more fields then others. If I can parse the above regardless of how many commas into a define table, I should be able to do the rest. Just having issue getting it to parse.
ASKER
Is there a way to parse if it's already read into 1 string field? I can filter it by dept and parse each line since it's consistent with the number of commas. From there, then I can do a union all. What's best way to parse by comma as separator if it's already 1 long string in a temp table?
I prefer to bulk insert the data from the flat file to a table with one column. So you will have all comma separated data in one row. From that table, you can parse the data into multiple fields into another table. Let me know if you are looking for a solution in these lines.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And the number of fields or columns in each row should be consistent for bcp to work, so just incase if you dont need to insert anything for note you need place an empty commad (,) as shown below
ECON,2992,10/11/2013,6:00A
MATH,29332,10/11/2013,90 T,
FIN,3293,10/11/2013,8:00AM