Importing Textfiles into SQL Server 2000

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hello,

Hi,

I have been unable to import a Text file into SQL Server 2000.

I have a Text file - Position.txt. When I open the file in textpad, here is what I see:

1. Each row is on a new line.
2. Each field is separated by a semi-colon.
3. The first line has the headers for the data.
4. At the end of the last field of the header column, I see a semi-colon.
5. All other rows have no commas at the end, just a new line separation. (except a good part of lines)

When I try to import the data from the textfile through a SQL Code (shown below), I get a message - "0 row(s) affected"

The data gets imported into Excel 2007 perfectly. I even tried converting the text file into an Excel file (.xls) and then importing it into SQL Server 2000. It worked fine. On looking into the file closely, some of the rows have semi-colon at the end of the row while some does not have. It seems that the data is truly not formatted in the right way.

How can I import the file into SQL Server 2000?

My table structure is as follows:

CREATE TABLE [Quota].[dbo].[Position] (
[# Position Name] nvarchar (255) NULL,
[Effective Start Date] datetime NULL,
[Description] nvarchar (255) NULL,
[Incentive Start Date] datetime NULL,
[Incentive End Date] datetime NULL,
[Employee ID] nvarchar (255) NULL,
[Title] nvarchar (255) NULL,
[Person Name] nvarchar (255) NULL
)

--Import data into the Position Table from the Position.txt file
BULK INSERT Position
FROM 'C:\Sales_Ranking\Position.txt'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)

Any ideas,
Thanks.

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorials/aspnet/8a2ea78b-f1e3-45b4-93ef-32b2d802ae17/wpf-datagrid-custom-pagin.aspx
.

Relevant Pages

  • Re: Im Getting : "syntax error converting datetime from character
    ... What type is ToDate of? ... It should be DateTime. ... >> Why do you define both date parameters as nvarchar? ... >>> I have a .NET application that is accessing a SQL Server database. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: JDBC - Cant get unicode with ResultSet.getString()
    ... The column is a nvarchar and I use ResultSet.getStringto retrieve it. ... >> The data is correct in Sql Server - I viewed it there. ... >> And it is the jdbc getString() that returns it wrong, ...
    (microsoft.public.sqlserver.odbc)
  • Re: Severe performance hit with NCHAR queries
    ... Response time the first couple of times was upwards of 20+ seconds, ... When two different data types meet, SQL Server applies a strict data-type ... The data type of a string literal with a preceeding N is nvarchar. ...
    (comp.databases.ms-sqlserver)
  • Re: slow stored procedure
    ... portion of the table much a condition like WHERE EVT_UPDATED < @CutOFfDate ... WHERE (CAST(ACS_EVENT AS NVARCHAR) + ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • nvarchar Data type conversion to Datetime
    ... I am using SQL Server. ... I have a field that has date data in nvarchar() type. ... I would like to convert the field and data to datetime type. ... and cast but got an error message saying there was an arithmetic overflow. ...
    (microsoft.public.data.oledb)