March 10, 2012 at 5:30 am
I am using BCP out to extract some 50 records into excel. In table, the records are sorted correctly (ie by datetime on which record is inserted). But when I extract to excel, the order goes haywire.
Any ideas to correct the order while extracting?
How does bcp works in such a case?
In the SQL Server itself, i extract the file as .xls. It does not have Excel installed. And, this out-of-order happens randomly.
March 10, 2012 at 6:14 am
Do you have an ORDER BY in the SELECT statement used in the BCP command?
As long as there's no specific ORDER BY in a statemewnt, the order of returned rows may vary.
March 10, 2012 at 7:05 am
Lutz,
I order by DATE field, its default GETDATE. At one time, 5 records would be inserted, so all the 5 would be having same datetime value. Example data is given below. As you can see, I cant sort by DATE field, since I need it like.. One Set (A1,B1..datetime1) followed by another Set (A2, B2...datetime2). But the sorting gets jumbled and I get A2 set followed by A1 set.
A1 datetime1
B1 datetime1
C1 datetime1 ==> one set of load
D1 datetime1
E1 datetime1
A2 datetime2
B2 datetime2
C2 datetime2 ==> 2nd set of load
D2 datetime2
E2 datetime2
March 10, 2012 at 7:12 am
March 10, 2012 at 7:38 am
Lutz,
bcp "select 'Col1', 'Col2', 'Col3','Date' union all select top 15 cast(Col1 as varchar(25)) as Col1,Col2,Col3,cast(DATE as varchar(25))as DATE from TABLENAME where SortOrder<>0" queryout D:\XXXX.xls -c -SXXXXXX -T
March 10, 2012 at 8:07 am
I can't see any ORDER BY in your query...
bcp "select 'Col1', 'Col2', 'Col3','Date' union all select top 15 cast(Col1 as varchar(25)) as Col1,Col2,Col3,cast(DATE as varchar(25))as DATE from TABLENAME where SortOrder<>0 ORDER BY 'Date'" queryout D:\XXXX.xls -c -SXXXXXX -T
March 10, 2012 at 8:18 am
Lutz,
Sorry missed it
bcp "select 'Col1', 'Col2', 'Col3','Date' union all select top 15 cast(Col1 as varchar(25)) as Col1,Col2,Col3,cast(DATE as varchar(25))as DATE from TABLENAME where SortOrder<>0 ORDER BY SortOrder asc" queryout D:\XXXX.xls -c -SXXXXXX -T
SortOrder is used like below:
A1 datetime1 1
B1 datetime1 1
C1 datetime1 1 ==> one set of load
D1 datetime1 1
E1 datetime1 1
A2 datetime2 2
B2 datetime2 2
C2 datetime2 2 ==> 2nd set of load
D2 datetime2 2
E2 datetime2 2
March 10, 2012 at 8:45 am
Is the issue resolved or do you still get unordered results?
March 10, 2012 at 8:48 am
No Lutz, its still not resolved.
Sometimes ordering becomes,
A1 datetime1
B1 datetime1
C1 datetime1
A2 datetime2
B2 datetime2
C2 datetime2
D2 datetime2
E2 datetime2
D2 datetime1
E2 datetime1
March 10, 2012 at 8:55 am
What is the Sortorder column based on? Can you add it to your output to see what values are in that column?
Just guessing here but the order might be exactly how the query is written but not what you want it to be... 😉
March 10, 2012 at 9:27 am
For one set of load (which is monthly), I give a unique number say 1, for next load it wud be 2 and so on.. and i need to retrieve last 5 load details..Since the dates for one load came like below, i could not sort on DATE field.
A1 2012-03-10 00:10:00.000
B1 2012-03-10 00:10:00.000
C1 2012-03-10 00:10:00.000 ==> one set of load
D1 2012-03-10 00:10:00.000
E1 2012-03-10 00:10:00.000
So, I used SortOrder field. But when i use that field, the data gets jumbled as i given above (i.e., last few rows of a load comes up as last in excel)
I may sound confusing. I cant put my real data here 🙁
Thanks Lutz
March 10, 2012 at 9:40 am
May I kindly ask you again to include the Sortorder column in the output of your select statement?
I know you can't post your real data. But when troubleshooting it really does help to include the column a result set is ordered by instead of assuming it will include the values you'd like to have.
Without being able to replicate the scenario based on sample data it's really hard to tell what the reason might be.
Another option would be to open the file using notepad or any text editor to make sure it's shown as exported and there's no fancy Excel-"wanna-help"-Add-On...
March 10, 2012 at 10:02 am
Thanks Lutz,
I will try to post the data maybe with some dummy values. I will get back to you after readying the dummy data.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply