Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cbeganesh
Posting Yak Master
105 Posts |
 Posted - 2011-04-14 : 17:15:19
|
| hello i have a txt file with this info, the values are dont have any delimiter betwen themCODE NAME CODE NAME CODE NAME---- ---- ---- ---- ---- ---- 001 Autauga 051 Elmore 101 Montgomery003 Baldwin 053 Escambia 103 Morgani need to load this to a table which had code and name columns and the data should be loaded as follows, ANy idea how to do this either using DTS or sqlCODE NAME 001 Autauga 051 Elmore 101 Montgomery003 Baldwin 053 Escambia 103 Morgan |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-14 : 20:12:27
|
You can import the file into the database into a staging table. If it is a one-time thing, simply use import wizard from SSMS. Then, the data can be parsed to put it in the right form. But, couple of questions about the data:1. There is a delimiter, but it is a space. Given that, would you have data like 001 Autauga 002 New York 003 That is, the alpha part with spaces in them? If so that makes it harder to parse it.2. Will the numeric part always be 3 digits?3. Would each row always have 3 pairs of number+name combinations, or could that be more/less/unknown? |
 |
|
|
cbeganesh
Posting Yak Master
105 Posts |
Posted - 2011-04-15 : 11:43:38
|
| thanks for your reply. The problem is the space. there could be cities with space in them. The numeric part is always 3. also each row could have 1 to 3 paairs . So it becomes really hard to do anything. I was thiking of parsing in it dot net. then the issue with the city comes/. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-15 : 12:33:17
|
| We could still try to do it in SQL, as long as there are no cities with 3 digits in their name. I haven't been to one, although there very well may one in North Korea. However, it would be easier to do it in C#/.Net because of the wonderful System.Text.RegularExpressions.Regex class. |
|
|
|
|
|
|
|
|
converting columns