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

Quote:

Originally Posted by 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.

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

Quote:

Originally Posted by ck9663

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

Quote:

Originally Posted by 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

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

Quote:

Originally Posted by ck9663

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

Expand|Select|Wrap|Line Numbers

 
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

MS SQL Server 2000: automatically rows inter changed in database

re: MS SQL Server 2000: automatically rows inter changed in database

re: MS SQL Server 2000: automatically rows inter changed in database

re: MS SQL Server 2000: automatically rows inter changed in database

re: MS SQL Server 2000: automatically rows inter changed in database

re: MS SQL Server 2000: automatically rows inter changed in database

re: MS SQL Server 2000: automatically rows inter changed in database

re: MS SQL Server 2000: automatically rows inter changed in database

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about