How To Prepare Data in Text Files for DTS to Import?

Sometimes you have data organized in rows and columns and stored in text files. You can import data from text files to SQL Server database tables using the DTS wizard. But you need to prepare data text files with the following questions in mind:

• What is the row delimiter? If the file has one row per line, the delimiter is Carriage-Return and Line-Feed: {CR}{LF}.
• What is the column (field) delimiter? This could be the tab character or comma.
• Are values enclosed with quoting characters? If comma is used as the column delimiter, values are most likely enclosed in double quotes ("). Quoting is need to differentiate comma in string values and comma as delimiters.
• Are column names included in the file as the first row? If column names are included, SQL Server will use them when creating new tables to store transformed data.

Here is a sample text file, C:\temp\fyi_articles.tab, containing the following rows and columns:

id	title	author	rank	submitted
1001	PHP Cookies	John King	936	01-Jun-2005
1002	File Upload	Larry Gate	341	07-Jul-2006
1003	SQL Server	FYIcenter.com	7003	01-Jul-2007

Values are not quoted, because tab characters are used as column delimiters, and tab characters are not appearing in any data values.

⇒ Defining Text Files as the DTS Data Source

⇐ Copying a Table from Source to Destination

⇑ SQL Server FAQs - Introduction to DTS (Data Transformation Services)

⇑⇑ SQL Server Connection Tutorials