Importing text files - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Importing text files

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-28 : 08:18:39

Paul writes "I have a file:

name=John Smith
Address=123 main street
city=boston
state=MA
zip=12345
name=Mary Smith
Address=456 main street
city=boston
state=MA
zip=12345
name=Ed Smith
Address=789 main street
city=boston
state=MA
zip=54321

Etc.

How can I import this to a table that contains fields of name, Address, City, State, Zip? Nothing to delimit by.

Any help would be appreciated."

samrat
Yak Posting Veteran

94 Posts

Posted - 2002-02-28 : 21:24:12

Use DTS to make a package to convert your existing structure and database into SQL server. This shouldn't be a big job.

Cheers,

Samrat

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-01 : 00:00:00

Intriguing! I'm assuming it's one row per field. And the data just repeats on down.

How about ...

1. Build a table with four fields: an identity column (int), an input column (big varchar), a key column (char?) and a value column (char?). On second though add another column: NameID int.

2. Import the date into the input column. The identity column is used to keep everything in the proper order.

3. Parse the key=value pairs into the key column and the value column. i.e. key="name", value="John Smith", etc.

4. Update the NameID field as follows. Where Key = "name", NameID = the identity column. For all others, NameID should be the highest identity value that's associated with a key of "name" value but is less that the identity of the record your update. Basically your trying to get all the rows that make up a logical "record" to have the same NameID. Another way would be NameID = identity -1 for all key = "address", NameID = identity - 2 for all key = "city", etc. I'm sure there are better ways to do this. Bonus points if you can do it in one UPDATE statement.

5. Build your target table with fields NameID, Name, Address, etc.

6. Insert the NameID and Name value into your target table.

7. Update the table to pull all the other values. Probably have to do this one at a time.

8. Find the person that gave you this format and smack them back into the stone age! It's so darn close to XML but not close enough to be usable. Oh well. HTH.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources