Importing text files into SQL Server 2000 Database using DTS Import/Export Wizard

Importing text files into SQL Server 2000 Database using DTS Import/Export Wizard

Post by sergei che » Fri, 08 Dec 2000 04:00:00



   I'm having a problem with exporting data from text files(received
by e-mail) into SQL Server 2000 Database. These files
were created by dumping the data from SQL Server 7
tables. Each row of
these files has a fixed length and  no special delimeters
between the fields - only spaces.The Wizard detected the import file as a
file with delimited fields(Screen "Select file format"). The next screen of
the Wizard detected the column
delimeter as "other", but I couldn't detect the character. As
soon as put in the delimeter field spaces, it gave me the wrong
number of colums in the result SQL Server Table( = junk).
    If I defined the file as a file with fixed length, the next screen
of  the Wizard I got the fields with
offset(they were not left justified). When I copied this file into
the database, I got the table with the wrong number of fields
and rong data in them.
Can anybody tell me, what is the problem and is it really
possible to import the data this way ?

Any suggestions are appreciated.

Sergei Cher

 
 
 

Importing text files into SQL Server 2000 Database using DTS Import/Export Wizard

Post by Andrew J. Kell » Sat, 09 Dec 2000 22:26:19


Sometimes with the wizard you need to make sure you have all the settings
just right. Don't assume it detects any of them correctly. But if you are
going from SQL7 to 2000 then I would suggest using BCP with the native mode.
This is the fastest and there will be no mistaking the data types, columns
etc.

--
Andrew J. Kelly
Targitmail.com


Quote:>    I'm having a problem with exporting data from text files(received
> by e-mail) into SQL Server 2000 Database. These files
> were created by dumping the data from SQL Server 7
> tables. Each row of
> these files has a fixed length and  no special delimeters
> between the fields - only spaces.The Wizard detected the import file as a
> file with delimited fields(Screen "Select file format"). The next screen
of
> the Wizard detected the column
> delimeter as "other", but I couldn't detect the character. As
> soon as put in the delimeter field spaces, it gave me the wrong
> number of colums in the result SQL Server Table( = junk).
>     If I defined the file as a file with fixed length, the next screen
> of  the Wizard I got the fields with
> offset(they were not left justified). When I copied this file into
> the database, I got the table with the wrong number of fields
> and rong data in them.
> Can anybody tell me, what is the problem and is it really
> possible to import the data this way ?

> Any suggestions are appreciated.

> Sergei Cher


 
 
 

1. Access 2000 to SQL Server 2000 using Import/Export Wizard or DTS

I have been trying to migrate my company's Access 2000 database to SQL
server while maintaining the Access front-end.

When trying to import the current tables, I get an error.  The error
is due to the date fields.  They are date/time in Access 2000.  I have
tried importing them as either datetime or smalldatetime.  No luck.

They will import as text, but then I loose all sorting functionality
on the date fields.

I have scoured the Internet, all Microsoft Access public newsgroups,
and checked on IRC.  No one seems to have had this problem before.

What am I doing wrong?

Thanks,

Jonathan Roberts
Advantage Innovation

2. masking quotes within formulas

3. DTS Wizard will not import/export between SQL Server Databases

4. W2K Pro client cannot make trusted connection to SQL2000 using TCP/IP and DB-Library.

5. Importing SAS dataset via DTS Import/Export Wizard

6. Migrating from old Hierarchical Database to RDBMS

7. using DTS to import a text file in SQL Server 7.0 with VB6

8. Disconnected Vs Connceted Recordsets

9. DTS Import Export Wizard (file size bug??)

10. DTS Import/Export Wizard Error:GetSourcesRowset post SQL worm infection and SP2 SQL srvr upgrade

11. Help Importing a text file with DTS Wizard

12. DTS Export/Import Wizards: Server 7.0 to Desktop 7.0