|
An Excel sheet I was given to import to SQL Server today has some header rows (four of them) before the column header row. I did this with ADO.net and the OleDb classes. I was hoping that there was some Extended Property that would allow me to skip the first four rows, but I saw none. connectionstrings.com and other sources didn't show anything either, so I eventually bit the proverbial bullet and simply skipped the first four rows from the DataReader and used the Fxx column names.
It worked, but I figured I'd ask whether or not anyone here knows of such an Extended Property.
|
|
|
|
|
None I know about, but you can select a range from the sheet like: SELECT * FROM [Sheet1$A4:F1243] Which could solve your problem.
But you would need to know, or find out, how many rows there are before doing the select.
|
|
|
|
|
But could it then use the first row as column headers?
|
|
|
|
|
|
OK, I'm just looking at this again, mainly for future reference (though I'll likely forget).
Thank you, yes, that works as described -- or very nearly. The problem is that the name of the sheet contains a SPACE and therefore has to be within apostrophes e.g. ['sheet 1$'] and I have been unable to get that to work with a range.
System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database engine could not find the object ''Report 1$B5:AE5737''.
System.Data.OleDb.OleDbException (0x80040E37): The Microsoft Access database engine could not find the object ''Report 1$'B5:AE5737'.
Just for testing I renamed the sheet to remove the SPACE and it works. 
Do you have a work around for this?
|
|
|
|
|
I never knew about the space problem, thanks for the feedback.
I can't think of any workaround at the moment, but I'm having a look at it tomorrow as I'm having a potential bug in one of my programs.
|
|
|
|
|
Hello,
I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50.
The table may not have all the Sequence number. For example following is the data in my table..
NAME SEQUENCE
------------------------
Jonh| 5
Amy| 1
Suresh| 3
I need a SQL to get the data like
NAME SEQUENCE
------------------------
Amy| 1
| 2
Suresh| 3
| 4
Jonh | 5
|6
|7
..
..
..
|50
Basically I want to have rows for the sequence numbers which are not present i my table.
I thought of using the following query to generate a sequence and then join, but does not help
select rownum from dual connect by rownum <=50
I tried something like
with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num
|
|
|
|
|
I guess you're using Oracle, since you select from 'dual'.
You may:
1. Declare a temporary table:
CREATE GLOBAL TEMPORARY TABLE temp_number
( number_column NUMBER( 10, 0 )
)
ON COMMIT DELETE ROWS;
2. Fill it with numbers from 1 to 50.
INSERT INTO temp_number
SELECT rownum FROM ALL_OBJECTS
WHERE rownum BETWEEN 1 AND 50
You may prefer a for loop.
3. Outer-join it to your table.
SELECT number_column, NAME
FROM TEMP_NUMBER
LEFT OUTER JOIN ABC
ON TEMP_NUMBER.number_column = ABC.SEQUENCE
ORDER BY number_column
Hope this helps,
Pablo.
Pablo.
"Accident: An inevitable occurrence due to the action of immutable natural laws." (Ambrose Bierce, circa 1899).
|
|
|
|
|
|
I got another solution
select * from ABC t,( select level R from dual connect by level <= 6) t1
where t.SEQUENCE(+) = t1.r
order by r
|
|
|
|
|
Hi all
I am using .net FW 3.5, its desktop applicaiton.
MyApplication is fetching data from sybase adoptive server and populating in it. Below connection, code.
connectionString=DRIVER=MERANT 3.60 32-BIT Sybase;UID=test;DB=db1;SRVR=XYZSERVER;PWD=*****
using (OdbcConnection connection = new OdbcConnection(connStringFromConfig)
{
OdbcDataAdapter adapter = new OdbcDataAdapter(query, onnection);
connection.Open();
adapter.Fill(dt);
connection.Close();
}
above code works fine in windows xp. where as same code is not working in windows 7. I am executing in 64bit machine.
i got error while opening connection.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Anything i am missing ? or do i need to configure anything?
appriciated u r quick response.
Ramana
modified 17-May-12 0:21am.
|
|
|
|
|
The coding part is completely correct, file connection is open and closed also. I guess window 7 does not support this function.
best face cleanser
|
|
|
|
|
I have designed my database with a one to many relationship where one user can have many statistics, but now I need to display the data in a single row format.
The UserStatistics table has a foreign key to the User table (user_id in UserStatistics)
Statistics might be height, weight, etc...
The table should look like this:
Name | Height | Weight
Joe 6' 150
And so on. The header will always be the same (Name, Height, Weight or whatever) but any time the user does not have that particular statistic I need a blank column. Do I need to rejoin against the UserStatistics table over and over again to get the results I need or what?
|
|
|
|
|
Assuming your statistics table looks like this:
| userid | property | value | | 1 | height | 180 | | 1 | width | 100 | | 2 | height | 170 | | 2 | width | 110 |
This query:
Select userid
,max(case when property = 'height' then value else null end) as height
,max(case when property = 'width' then value else null end) as width
from properties
group by useridWould get this result:
| userid | height | width | | 1 | 180 | 100 | | 2 | 170 | 110 |
Depending on what database you're using, the pivot function might already be included and in that case it's easy enough to google how to use it.
|
|
|
|
|
This is MySql. Thank you for the suggestion, between you and Mycroft I will get it sorted out either using pivot or when ... then.
Cheers, --EA
|
|
|
|
|
MySQL doesn't have a specific PIVOT functionality AFAIK.
|
|
|
|
|
It does not have a native pivot feature, but your aggregate / group by solution will work perfectly.
Cheers, --EA
|
|
|
|
|
Alternatively you can use a pivot [^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
eddieangel wrote: rejoin against the UserStatistics table over and over again
That's what I would do.
|
|
|
|
|
You can do this by using simple Join on these two tables..
Taking selected column in SELECT statement..
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
How to export query results into text file ?
|
|
|
|
|
For That You Have To Activate Few Services In SQL Server ::
Execute This Command Set;
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
Now To Get Output of any SQL query in a text file write this query:
EXEC xp_cmdshell 'bcp "YOUR QUERY HERE" queryout "Location Of TextFile Here" -T -c -t,'
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
|
Thanks For Reply .
I have use this code :
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC xp_cmdshell 'bcp "SELECT * FROM Silver.tblGroups" queryout "C:\bcptest2.txt" -T -S ALIJOONASUS\wintapsqlexpress -c -t,'
But i wanna to have <big>UTF-8</big> text file.
and Secondly :
How to have <big>Columns Name</big> also
modified 16-May-12 4:30am.
|
|
|
|
|
we have written program in C#.
now when we change the database(in sql express 2008) sp OR view OR add new table OR delete table OR ....
how can update the old customer DB within the newest one without changing the data in customer DB.
in other word :
the customers have previous application and database (full with their specific data). Now a new version is ready and the customer gets the update. In the mean time we made some modification on DB (new table, columns, maybe an old column deleted, or whatever). I’m pretty new in Linq and also SQL databases and my first solution can be: I check the applications/databases version and implement all the changes step by step comparing all tables, columns, keys, constrains, etc. (all this new information I have in my dbml and the old I asked from the existing DB). And I’ll do this each time the version changed. But somehow I feel, this is NOT a smart solution so I look for a general solution of this problem.
modified 16-May-12 4:30am.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
