Importing a Text File

Hi,
I'm trying to parse a text file in SQL Server but am having problems
importing the file.
When I try to import the text file using Enterprise Manager it doesn't
preserve the row order. Currently I'm importing the text file into Access
(which preserves the order) and creating a primary key there. Then I can
import it into SQL Server and order by the key. Is there a way to skip this
ACCESS step and directly import into SQL server with the order intact?
Thank you very much.Use ORDER BY when selecting the data.
--
TIA,
ChrisR
"Irving" wrote:

> Hi,
> I'm trying to parse a text file in SQL Server but am having problems
> importing the file.
> When I try to import the text file using Enterprise Manager it doesn't
> preserve the row order. Currently I'm importing the text file into Access
> (which preserves the order) and creating a primary key there. Then I can
> import it into SQL Server and order by the key. Is there a way to skip th
is
> ACCESS step and directly import into SQL server with the order intact?
> Thank you very much.|||Thank you for responding.
The problem is there's nothing to order by. That's why I import into Access
first to create a primary key.
"ChrisR" wrote:
[vbcol=seagreen]
> Use ORDER BY when selecting the data.
> --
> TIA,
> ChrisR
>
> "Irving" wrote:
>|||Might try using DTS to import the file into a table that contains a PK with
the IDENITY property. The SELECT ...ORDER BY PK column.
HTH
Jerry
"Irving" <Irving@.discussions.microsoft.com> wrote in message
news:AFD2116A-3615-4680-966C-8DCC17588C09@.microsoft.com...[vbcol=seagreen]
> Thank you for responding.
> The problem is there's nothing to order by. That's why I import into
> Access
> first to create a primary key.
> "ChrisR" wrote:
>|||Irving,
One possibility is to add line numbers with
C:\> find /V /N "" c:\data\yourfile.txt > c:\data\yourfile2.txt
or write a short program in C or C++ or C# to do something similar.
(Or use a Unix shell.)
This DOS find command will add bracketed line numbers at the beginning
of each line, along with a blank line and a line with the file name at
the top, these first two without any line numbers. You could then
import this file into a table with one varchar(270) column and retrieve
both the line number and the contents with another query:
select
cast(substring(onlyColumn,2,charindex(']
',onlyColumn)-1) as int) as
linenumber,
substring(onlyColumn, charindex(']',onlyColumn)+1,8000) as linecontent
from importedTable
where substring(onlyColumn,1,1) = '['
-- Steve Kass
-- Drew University
-- Ref: E434E144-6F3D-4A3A-9F00-4642ED84B33F
Irving wrote:

>Hi,
>I'm trying to parse a text file in SQL Server but am having problems
>importing the file.
>When I try to import the text file using Enterprise Manager it doesn't
>preserve the row order. Currently I'm importing the text file into Access
>(which preserves the order) and creating a primary key there. Then I can
>import it into SQL Server and order by the key. Is there a way to skip thi
s
>ACCESS step and directly import into SQL server with the order intact?
>Thank you very much.
>|||Thank you very much Steve.
It looks like this will work.
"Steve Kass" wrote:

> Irving,
>
> One possibility is to add line numbers with
>
> C:\> find /V /N "" c:\data\yourfile.txt > c:\data\yourfile2.txt
> or write a short program in C or C++ or C# to do something similar.
> (Or use a Unix shell.)
> This DOS find command will add bracketed line numbers at the beginning
> of each line, along with a blank line and a line with the file name at
> the top, these first two without any line numbers. You could then
> import this file into a table with one varchar(270) column and retrieve
> both the line number and the contents with another query:
> select
> cast(substring(onlyColumn,2,charindex(']
',onlyColumn)-1) as int) as
> linenumber,
> substring(onlyColumn, charindex(']',onlyColumn)+1,8000) as linecontent
> from importedTable
> where substring(onlyColumn,1,1) = '['
>
> -- Steve Kass
> -- Drew University
> -- Ref: E434E144-6F3D-4A3A-9F00-4642ED84B33F