1

I use SQL Server wizard to import csv files, however the rows imported appears in different order. How can I say to SQL Server to import rows in correct order.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
David
  • 33
  • 2
  • 6
  • what is the "correct order" in your CSV?? SQL doesn't have an implicit order - you have to define one by specifying ORDER BY in your SQL query. – marc_s Jan 03 '10 at 16:17
  • Duplicate: http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order – OMG Ponies Jan 03 '10 at 17:52
  • He means "correct order" - oreder while importing. If I add identity column and load XLS file into it, is values of identity column will correspond rows order in XLS file? – MaratSh Sep 02 '11 at 09:46

3 Answers3

4

In databases there is no such thing as order of records, as opposed to numbered spreadsheet rows, you need a column to order by. You would use something like

SELECT * FROM myTable ORDER BY SomeColumn
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • +1, but for databases, you are really meaning "relational databases" as defined by E.F. Codd, since before and since there have been databases of other kinds. – Cade Roux Jan 03 '10 at 15:08
2

It's probably importing in the correct order, but SQL server is retrieving them in a different order. Try adding a unique column on your table and then select the rows using that as an order by.

Dayton Brown
  • 1,228
  • 3
  • 16
  • 31
0

Getting back to the original question, I'm exporting email from Outlook by first sequencing the messages as desired, then selecting multiples a month at a time from the desired folder, then doing the FILE SAVE AS option instead of the Outlook Export function which appears to keep the correct order that far. They are exported to text with no delimiters used.

I created tables in SQL Server that contain only an identity column and a varchar(4000) column. Use row delimiter CRLF and no field delimiter for the import. So far, this appears to be keeping each line in the messages in the correct order. you can create a unique index on the table OR specify ORDER BY the ident column and view emails nicely in what I THINK so far is the correct sequence.

I'm extracting character and numeric data from certain randomly placed rows within each message, so plan to do this with functions LTRIM and RTRIM, then use a pointer to each character until I find the next space, checking each group for being character or numeric, converting numeric data to decimal, and thus parsing the data from the strings to the appropriate data type variable. Working left to right, when I discover a space, I can LTRIM the string to itself and easily move the remaining data to the left. I'm working with dates, alphanumeric stock symbols, and decimal quantities and prices, so each will be identified with function ISNUMERIC before conversion to avoid errors and identify bad data.

Repeated use of the LTRIM will easily move on the next data item in the string, reducing the individual character scanning of intervening spaces.

Rick
  • 1