Importing a Flat File with records importing sequentially

[cfw2]

I am trying to import a flat file into a new table and I need the records to insert into the table in the same order they appear in the file. There are about 30,000 records total, and I think they start getting out of order in the 20,000 or so. I am importing the file into a 1 column table [i.e. create table tmp ( col001 varchar(133) ) ]. I have tried using both BCP and Bulk Insert. Does anyone know how I can eliminate the records from becoming disordered?

Thank You,
Charles

 

[SQLDenis]

order of rows storage in a table is irrelevant
to get rows back in the order that you want use ORDER BY

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[cfw2]

the problem is i have nothing to order it by. The file is not really "table ready". I need to re-structure the data once it is in my table so I can start manipulating it.

In the file, there is a transaction indicator. The following rows are all associated with that transaction, until the next transaction indicator appears. There is no way to tell that a row belongs to a particular transaction other than the fact it comes after the one it is associated with, and before the next one.

If I use order by, I will be even more messed up than I am now. I hope that makes sense.

 

[dexeloper]

Sounds like you should write some vbscript to read the file using FSO and write the records to the SQL table.

 

[SQLSister]

First thing I would do is have the provider of the data send it to you with a recordID field. Then you know the order of the records. Always go back to the provider first if they don't give it to you in the way you need.

Questions about posting. See faq183-874

 

[fredericofonseca]

Provider changes to the file is the best way to go.

If they cannot/refuse to do that then
1-
DTS package with a transformation step and VBSCRIPT coding within it may help.

2- Create a small VB program to process the data for you and insert it into the DB.

If it is a really big file I advise to write to another text file with the correct information and THEN use BCP to load it.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[cfw2]

it looks like there is not a simple command to do this. Thank you to everyone who replied.

 

[bboffin]

If you have a copy of Office Professional then Access (any version from 97 on) has excellent data import features from a wide variety of file formats that might save you a lot of work. One thing is that it will offer to add a primary key to the imported table which will simply be a sequential number thereby preserving the original sequence as you requested.

Once the data is in an Access table it is very easy to import that table to SQL Server via DTS.

In addition you can use Access Queries and VBA to manipulate the data prior to transferring it and this may be simpler than writing SPs or other code to do the same job.

I frequently have to import data from dubious sources and Access is the preferred route for many of these.



Bob Boffin