MS SQL Server 2000: automatically rows inter changed in database

[InnoSol]

Hi all,

I import MS Excel 2003 spread sheet in MS SQL Server 2000 through MS SQL Server 2000 Enterprise Manager. In excel two sheets (sheet1 and sheet2) of data is there. I imported first sheet data in first time in relevant table of database and done second sheet of data in next time. My excel file have 2000 rows and 100 columns of data. All the data are imported in relevant attributes cells in good manner. But the first 16 rows are sorted automatically. I am trying to say that first row data is match with my excel file. But second row data have gone to 7th row and 7th row have gone to 5th row like that. Except that 16 rows all other data are matched with my MS Excel 2003 file. I need the data sequence what I have in my excel file. What is the problem occurred? How can I solve this?
Actually the problem occurred in first some rows so I found the mistake. If it comes after 1000 rows how can I predict and rectify?
Please help me. I don't have more knowledge in MS SQL Server 2000.
Thanks,

With Regards,
bala.

[ck9663]

Hi all,

I import MS Excel 2003 spread sheet in MS SQL Server 2000 through MS SQL Server 2000 Enterprise Manager. In excel two sheets (sheet1 and sheet2) of data is there. I imported first sheet data in first time in relevant table of database and done second sheet of data in next time. My excel file have 2000 rows and 100 columns of data. All the data are imported in relevant attributes cells in good manner. But the first 16 rows are sorted automatically. I am trying to say that first row data is match with my excel file. But second row data have gone to 7th row and 7th row have gone to 5th row like that. Except that 16 rows all other data are matched with my MS Excel 2003 file. I need the data sequence what I have in my excel file. What is the problem occurred? How can I solve this?
Actually the problem occurred in first some rows so I found the mistake. If it comes after 1000 rows how can I predict and rectify?
Please help me. I don't have more knowledge in MS SQL Server 2000.
Thanks,

With Regards,
bala.

to solvem try inserting a new column. fill it out with numeric row counter...this waty even if the sequence is mess up when you upload it, you can still sort it...

-- ck

[InnoSol]

to solvem try inserting a new column. fill it out with numeric row counter...this waty even if the sequence is mess up when you upload it, you can still sort it...

-- ck

Hi,

Sorry ck9663. I couldn't follow your answer. You said that numeric row counter, is it in excel?

Now I did the same function as I said in my actual question again. Import MS Excel 2003 file to MS SQL Server 2000. This time I changed the data. Only 20 rows of data.
I check the database after imported, the total rows are interchanged automatically. First gone to last 5th come to first like that.
Anything I should do in my MS Excel file?
Otherwise can I solve it in MS SQL Server 2000 itself?
thanks,

[ck9663]

yes. am suggesting you insert a column on your excel. maybe on the leftmost (A). populate it with number that could represent a row number. then import it to your sql server

-- ck

[InnoSol]

yes. am suggesting you insert a column on your excel. maybe on the leftmost (A). populate it with number that could represent a row number. then import it to your sql server

-- ck

thanks for the reply.

I did what you said. But that particular column not came in database file. I give row no. heading to that new column (A1) and give no from 1, 2, 3... But that data not comes in my SQL Server database file. Anything I should do for that newly inserted column come in my SQL server database file?

[ck9663]

it's probably because your table is already existing. either drop your table first or import it into a new/temp table...

-- ck

[InnoSol]

it's probably because your table is already existing. either drop your table first or import it into a new/temp table...

-- ck

Hi

Any possibility available to export data to SQL Server database from MS Excel file by query?

If yes please mention the query and where should I run that query?

[PreethiParkavi]

Hi,
You can import data from Excel through the query.for example

SELECT * INTO #tmp  FROM
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\mathi\sample.xls', 'SELECT ID,Name FROM [sheet1$] order by name')
select * from #tmp
INSERT INTO SAMPLE (ID,NAME) select ID,NAME from #tmp
select * from sample
drop table #tmp 
 
 

Here , 2 columns are imported from excel sheet while is having three columns.It is inserted into a temporary table and then it is transfered into original table..for more , refer link http://support.microsoft.com/kb/321686

The only condition is, The excel file should be in the system where we are going to export.

Thanks,
Preethi