Database

17-May-12 11:46

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.

Jörgen Andersson17-May-12 12:39

17-May-12 12:39

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.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

PIEBALDconsult17-May-12 15:20

17-May-12 15:20

But could it then use the first row as column headers?

Jörgen Andersson17-May-12 21:36

17-May-12 21:36

First row of the range.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

PIEBALDconsult21-May-12 9:38

21-May-12 9:38

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. Thumbs Up | :thumbsup:

Do you have a work around for this?

Jörgen Andersson21-May-12 10:59

Sybase DB access in windows 7

21-May-12 10:59

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.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Serial number in SQL

draghu17-May-12 1:17

draghu

17-May-12 1:17

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

SQL

select rownum  from dual connect by rownum <=50

I tried something like

SQL

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

Re: Serial number in SQL

Pablo Aliskevicius17-May-12 3:48

Pablo Aliskevicius

17-May-12 3:48

I guess you're using Oracle, since you select from 'dual'.
You may:
1. Declare a temporary table:

SQL

CREATE GLOBAL TEMPORARY TABLE temp_number
   ( number_column   NUMBER( 10, 0 )
   )
   ON COMMIT DELETE ROWS;

2. Fill it with numbers from 1 to 50.

SQL

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.

SQL

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).

Re: Serial number in SQL

Andy_L_J17-May-12 21:51

Andy_L_J

17-May-12 21:51

This is not Oracle but may give you some useful pointers...Using User Defined Functions in Table Inserts and Seeding with Stored Procedures[^]
Might be worth a look.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Re: Serial number in SQL

draghu17-May-12 23:11

draghu

17-May-12 23:11

I got another solution

SQL

select * from ABC t,( select level R from dual connect by level <= 6) t1
where t.SEQUENCE(+) = t1.r
order by r

VenkataRamana.Gali16-May-12 18:29

VenkataRamana.Gali

16-May-12 18:29

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.

XML

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.

Re: Sybase DB access in windows 7

JohnsonLuis16-May-12 22:07

JohnsonLuis

16-May-12 22:07

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

One to Many Relationships - Tabular Display

eddieangel16-May-12 11:36

eddieangel

16-May-12 11:36

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?

Jörgen Andersson16-May-12 12:26

16-May-12 12:26

Assuming your statistics table looks like this:

userid	property	value
1	height	180
1	width	100
2	height	170
2	width	110

This query:

SQL

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 userid

Would 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.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

eddieangel16-May-12 13:53

eddieangel

16-May-12 13:53

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

Jörgen Andersson17-May-12 2:51

17-May-12 2:51

MySQL doesn't have a specific PIVOT functionality AFAIK.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

eddieangel17-May-12 7:43

eddieangel

17-May-12 7:43

It does not have a native pivot feature, but your aggregate / group by solution will work perfectly.

Cheers, --EA

Mycroft Holmes16-May-12 13:47

Mycroft Holmes

16-May-12 13:47

Alternatively you can use a pivot [^]

Never underestimate the power of human stupidity
RAH

PIEBALDconsult16-May-12 15:11

16-May-12 15:11

eddieangel wrote:
rejoin against the UserStatistics table over and over again

That's what I would do.