Importing data using import service/DTS

Hi,

I'm using Sql server 2005 and I need to import about 15000 records from a txt file which is delimated using " | " character. Part of the problem is that the file has about 109 fields. The other part of the problem is that there aren't any headers defined. The field name and their respective data types are defined in a sepearte excel sheet. When I'm trying to import it using Sql Server 2005 import wizard, I would have to modify the names and the length manually and change the data types which is a time consuming job. My boss has claimed that he did it in Sql Server 2000 without using BCP utility and he got the table columns from the excel file (he didn't have to modify it manually somehow he import it) but he can't really remember !!!!!!!!

Now i'm really confused and a bit ashamed as well as he is around 62 years old.

Any idea of how it could be done using Sql Server 2005 or Sql Server 2000?

Thanks in advance.

Hi,

Can somebody please reply to this thread .......

Thanks

|||

Using the Bulk Insert task, you can specify a format file. This could be easily generated from the Execl sheet, and would be quicker than configuring the text file connection manager. Note this is not using teh Wizard, but the full designer.

The DTS Wizard had no way of using an Excel file or other format file either.

|||

DarrenSQLIS wrote:

Using the Bulk Insert task, you can specify a format file. This could be easily generated from the Execl sheet, and would be quicker than configuring the text file connection manager. Note this is not using teh Wizard, but the full designer.

The DTS Wizard had no way of using an Excel file or other format file either.

Thanks Darren. Can you please specify on how can i do it and which full designer. I'm sorry but i'm not use to Sql Server 2005.

|||

The designer, aka Business Intelligence Development Studio (BIDS) aka Visual Studio (VS).

Create a new Integration Services project type. This is the SSIS package designer.

Drag tasks from the toolbox, and I suggest you start with the Bulk Insert Task.

There is a tutorials section in the SQL help, Books Online, that may be worth a look as well.

Importing data from a text file with quotes

Dear all,

I trying to import a set of datas from a text file to a table call Movement.

Table structure for Movement is as following:

1, JobNo, Character (10)
2, Date, smalldatetime
3, ItemDesc, Character (100)
4, StaffID, Character (5)
5, Quantity, Decimal(10,4)
6, Completed,Bit

text file sample contains:

"T200601100";1/10/2006;"A3 papers, Plastic covers","T1001",500,1
"T200601101";1/11/2006;"Ink Refiller for MF0012/3","T1001",5.5,1
"T200601102";1/12/2006;"1' roller","T1012",50,1
"T200601103";1/13/2006;"A1 Papers White, A3 Paper Black","T1022",500,1
"T200601104";1/13/2006;"Folders","T1022",10,1

Now the problem is when i use bulk insert statement i put in the double quote (""") into the fields too. How can i solve this problem out?

Thanks in advance.You can use a format file with bcp/bulk insert to import data that contains field delimiters. You have to basically create dummy columns in the format file for the delimiter which you will ignore. See books online for more details on how to use format file with more columns than that of the table.