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.
