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 this
> 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 this
>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