Hello,
I connect to an Excel file with ADO and I get the sheet names and after that I read the table. if the column is all integers or strings there's no problem but if the column is composed of integers and strings then only the strings are returned,others are returned empty.
What's the reason?
Thanks
Hi,did you try this here:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q194124
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
I need some pointers to help me import some data from an excel sheet into a SQL Server 2005 DB. Here is the scenario:
Each row in my excel sheet maps to three tables in the DB. Lets say there are 6 columns: Col1, Col2...Col6.
Col1 and Col2 map to DBTableA.
Col3 and Col4 map to DBTableB.
Col4 and Col6 map to DBTableC.
DBTableA's primary key is used as a foreign key in DBTableB and DBTableC.
Now what would be the best way to do this using SSIS?
I have gone through transforms and data flow tasks etc, so I am not exactly a newbie in SSIS. So it will be ok to use any SSIS specific terms in your response.
Thanks.
-Faisal
I guess the post was redundant.
After searching the forums for similar questions, I found the solution.
SSIS Nugget: Splitting order detail and order header information from one file into multiple tables
http://blogs.conchango.com/jamiethomson/archive/2006/05/22/3974.aspx
Thanks Jamie.
-Faisal
Name Mark1 Mark2
Ram 87 76
somu 45 65
james 65 98
When converting this excel to sql it is working properly..
See the problem below..
Name Mark1 Mark2
Ram 87 76
somu 45 AB
james 65 98
when i am giving 'AB' to mark it shows null value at that specified column(ie,blank space on grid for the data 'AB').
This is my problem...How can i rectify this...Any alternative solutions...
You need to check your database column's datatype. The problem you are running into is most likely the target column is a numeric column.
Let me know whether you can fix the problem by change the data type of the column, keep the data type consistent in your code too.
|||May i know,the way to convert excel t sqlserver database table|||You can simply use Import/Export Wizard in SQL Server
Hi All
Thnks for the time
I was trying to figure out how to insert the data that I read from an excel sheet into multiple tables in SQL Database 2005.
// Connection string to the excel file
string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source= D:\\UserData.xls;" +
"Extended Properties=Excel 8.0;";
//Command to read data is
SELECT ID,ProductName,ProductDesc,CategoryName FROM [Products$]
//Connection string for sql 2005 database
string sqlConnectionString = "DataSource=MyServer\\MSSQLEXPRESS;Initial Catalog=TestExcel;Integrated Security=True";
And what is your question ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
What is the syntax of storing the content of an Excelsheet in a predefined
table
with a stored prcedure.
Arno de Jong, The Netherlands.A.M. de Jong (arnojo@.wxs.nl) writes:
> What is the syntax of storing the content of an Excelsheet in a predefined
> table
> with a stored prcedure.
As I don't use Excel much with SQL Server, I went to http://www.google.com
and entered:
Excel INSERT "SQL Server"
and god a couple of hits. The first one is a blog entry from SQL Server
MVP Dejan Sarka:
http://solidqualitylearning.com/blo.../03/12/441.aspx.
This should get you started.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Excel sheet ::
ColName1 ColName 2 ........ColName30
ColData11 ColData12..........ColData130
.....
..... ..... .. ...
..... ..... .. ...
Temp table ::
Thanks
Clayton
Hi,
In SSIS, when you are transferring data from one Excel File to Another File you have to create an Excel Connection Manager for Source and Destination and in that you have an option as "First Row has Column Names".
But I am not sure about the validation at this point of time. Will verify and let you know that.
Thanks,
Prakash Srinivasan
Excel sheet ::
ColName1 ColName 2 ........ColName30
ColData11 ColData12..........ColData130
.....
..... ..... .. ...
..... ..... .. ...
Temp table ::
Thanks
Clayton
Hi,
In SSIS, when you are transferring data from one Excel File to Another File you have to create an Excel Connection Manager for Source and Destination and in that you have an option as "First Row has Column Names".
But I am not sure about the validation at this point of time. Will verify and let you know that.
Thanks,
Prakash Srinivasan
this is thiru from India, hope i shall get answers here for my questions.
1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.
Import Process:
a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database
Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").
please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru
hi,
you can directly import excel
to sql server import table
do the processing from the import table
to the staging table.
then from the staging table load it directly to the destination table.
i suggest you use SSIS or DTS or you can linked server
to excell and do the processing with sql server.
the problem with your approach is that you might encounter a dataset limitation.
there is no restartability feature. if your application breaks in the middle of the load process
where half of the data has been loaded it would be a great nightmare.
the first above mentioned approach is used widely in BI and datawarehousing.
here's some explanation on how it works:
1. you import the data to an import table. the import table shall be used
for processing the data .this liberates the datasource from heavy processing
and migrates the proceessing to SQL. if the import fails you can truncate the import table
and re import the data. this can done easily without harming the production.
the idea here is to first load everything into sql server.
2. stage the data to the staging table from the imports. here you do data cleansing
the burden nows reside on the sql server which is not a burden at all but a big problem to asp
from your previous design. Resatrtability. truncate the stage table and do the processing from either
step 1 or 2.
3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data
with great speed.
|||Hello,
please let me find some help.
For a couple of weeks i am struggling with an excel import and export application to sqlserver as i have to do it through an external application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is
CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall any one suggest me how shall i get values from excel sheet with datatype varchar and import into the above said table as varchar values (with out any exponential types).|||
I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:
Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Here are some other tutorials that may be of interest:
Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory
Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
The spreadsheet has a schema and you want to make sure it is formatted properly.
Regards,
Dave
|||David Hayden wrote:
I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:
Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Here are some other tutorials that may be of interest:
Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory
Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
The spreadsheet has a schema and you want to make sure it is formatted properly.
Regards,
Dave
I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?
|||hi thiru
how r u ?
iam facing same problem if have solution please suggest me
thanking u
with regards
purushotham.T
|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605this is thiru from India, hope i shall get answers here for my questions.
1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.
Import Process:
a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database
Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").
please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru
hi,
you can directly import excel
to sql server import table
do the processing from the import table
to the staging table.
then from the staging table load it directly to the destination table.
i suggest you use SSIS or DTS or you can linked server
to excell and do the processing with sql server.
the problem with your approach is that you might encounter a dataset limitation.
there is no restartability feature. if your application breaks in the middle of the load process
where half of the data has been loaded it would be a great nightmare.
the first above mentioned approach is used widely in BI and datawarehousing.
here's some explanation on how it works:
1. you import the data to an import table. the import table shall be used
for processing the data .this liberates the datasource from heavy processing
and migrates the proceessing to SQL. if the import fails you can truncate the import table
and re import the data. this can done easily without harming the production.
the idea here is to first load everything into sql server.
2. stage the data to the staging table from the imports. here you do data cleansing
the burden nows reside on the sql server which is not a burden at all but a big problem to asp
from your previous design. Resatrtability. truncate the stage table and do the processing from either
step 1 or 2.
3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data
with great speed.
|||
Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:
please let me find some help.
For
a couple of weeks i am struggling with an excel import and export
application to sqlserver as i have to do it through an external
application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is
CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall
any one suggest me how shall i get values from excel sheet with
datatype varchar and import into the above said table as varchar values
(with out any exponential types).|||
I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:
Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Here are some other tutorials that may be of interest:
Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory
Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
The spreadsheet has a schema and you want to make sure it is formatted properly.
Regards,
Dave
|||David Hayden wrote:
I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:
Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Here are some other tutorials that may be of interest:
Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory
Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
The spreadsheet has a schema and you want to make sure it is formatted properly.
Regards,
Dave
I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?
|||hi thiru
how r u ?
iam facing same problem if have solution please suggest me
thanking u
with regards
purushotham.T
|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605this is thiru from India, hope i shall get answers here for my questions.
1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.
Import Process:
a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database
Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").
please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru
hi,
you can directly import excel
to sql server import table
do the processing from the import table
to the staging table.
then from the staging table load it directly to the destination table.
i suggest you use SSIS or DTS or you can linked server
to excell and do the processing with sql server.
the problem with your approach is that you might encounter a dataset limitation.
there is no restartability feature. if your application breaks in the middle of the load process
where half of the data has been loaded it would be a great nightmare.
the first above mentioned approach is used widely in BI and datawarehousing.
here's some explanation on how it works:
1. you import the data to an import table. the import table shall be used
for processing the data .this liberates the datasource from heavy processing
and migrates the proceessing to SQL. if the import fails you can truncate the import table
and re import the data. this can done easily without harming the production.
the idea here is to first load everything into sql server.
2. stage the data to the staging table from the imports. here you do data cleansing
the burden nows reside on the sql server which is not a burden at all but a big problem to asp
from your previous design. Resatrtability. truncate the stage table and do the processing from either
step 1 or 2.
3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data
with great speed.
|||Hello,
please let me find some help.
For a couple of weeks i am struggling with an excel import and export application to sqlserver as i have to do it through an external application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is
CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall any one suggest me how shall i get values from excel sheet with datatype varchar and import into the above said table as varchar values (with out any exponential types).|||
I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:
Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Here are some other tutorials that may be of interest:
Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory
Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
The spreadsheet has a schema and you want to make sure it is formatted properly.
Regards,
Dave
|||I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:
Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Here are some other tutorials that may be of interest:
Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory
Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
The spreadsheet has a schema and you want to make sure it is formatted properly.
Regards,
Dave
I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?
|||hi thiru
how r u ?
iam facing same problem if have solution please suggest me
thanking u
with regards
purushotham.T
|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605this is thiru from India, hope i shall get answers here for my questions.
1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.
Import Process:
a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database
Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").
please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru
hi,
you can directly import excel
to sql server import table
do the processing from the import table
to the staging table.
then from the staging table load it directly to the destination table.
i suggest you use SSIS or DTS or you can linked server
to excell and do the processing with sql server.
the problem with your approach is that you might encounter a dataset limitation.
there is no restartability feature. if your application breaks in the middle of the load process
where half of the data has been loaded it would be a great nightmare.
the first above mentioned approach is used widely in BI and datawarehousing.
here's some explanation on how it works:
1. you import the data to an import table. the import table shall be used
for processing the data .this liberates the datasource from heavy processing
and migrates the proceessing to SQL. if the import fails you can truncate the import table
and re import the data. this can done easily without harming the production.
the idea here is to first load everything into sql server.
2. stage the data to the staging table from the imports. here you do data cleansing
the burden nows reside on the sql server which is not a burden at all but a big problem to asp
from your previous design. Resatrtability. truncate the stage table and do the processing from either
step 1 or 2.
3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data
with great speed.
|||Hello,
please let me find some help.
For a couple of weeks i am struggling with an excel import and export application to sqlserver as i have to do it through an external application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is
CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall any one suggest me how shall i get values from excel sheet with datatype varchar and import into the above said table as varchar values (with out any exponential types).|||
I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:
Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Here are some other tutorials that may be of interest:
Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory
Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
The spreadsheet has a schema and you want to make sure it is formatted properly.
Regards,
Dave
|||I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:
Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Here are some other tutorials that may be of interest:
Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory
Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
The spreadsheet has a schema and you want to make sure it is formatted properly.
Regards,
Dave
I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?
|||hi thiru
how r u ?
iam facing same problem if have solution please suggest me
thanking u
with regards
purushotham.T
|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605this is thiru from India, hope i shall get answers here for my questions.
1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.
Import Process:
a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database
Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").
please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru
hi,
you can directly import excel
to sql server import table
do the processing from the import table
to the staging table.
then from the staging table load it directly to the destination table.
i suggest you use SSIS or DTS or you can linked server
to excell and do the processing with sql server.
the problem with your approach is that you might encounter a dataset limitation.
there is no restartability feature. if your application breaks in the middle of the load process
where half of the data has been loaded it would be a great nightmare.
the first above mentioned approach is used widely in BI and datawarehousing.
here's some explanation on how it works:
1. you import the data to an import table. the import table shall be used
for processing the data .this liberates the datasource from heavy processing
and migrates the proceessing to SQL. if the import fails you can truncate the import table
and re import the data. this can done easily without harming the production.
the idea here is to first load everything into sql server.
2. stage the data to the staging table from the imports. here you do data cleansing
the burden nows reside on the sql server which is not a burden at all but a big problem to asp
from your previous design. Resatrtability. truncate the stage table and do the processing from either
step 1 or 2.
3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data
with great speed.
|||
Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:
please let me find some help.
For
a couple of weeks i am struggling with an excel import and export
application to sqlserver as i have to do it through an external
application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is
CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall
any one suggest me how shall i get values from excel sheet with
datatype varchar and import into the above said table as varchar values
(with out any exponential types).|||
I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:
Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Here are some other tutorials that may be of interest:
Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory
Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
The spreadsheet has a schema and you want to make sure it is formatted properly.
Regards,
Dave
|||I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:
Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
Here are some other tutorials that may be of interest:
Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory
Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema
The spreadsheet has a schema and you want to make sure it is formatted properly.
Regards,
Dave
I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?
|||hi thiru
how r u ?
iam facing same problem if have solution please suggest me
thanking u
with regards
purushotham.T
|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605