Import Excel to SQL using Access Project

Sorry if this is the wrong forum for this, but was as close as I could get. I am an amateur stumbling thru this to "learn by doing". I am trying to set up an access front end for a SQLExpress database, currently all local for experimenting. Trying to import Excel data to a table in the database, using Access "get external data" function. I get errors and the data wont import. Error message won't give me a clue to what is wrong. All fields are named the same. If I convert the excel to a csv or tab delineated, it imports correctly. Just not the excel. Any ideas?

Take a look at this article

http://support.microsoft.com/kb/321686

sql

import excel file into dropdownlist then export to sql server 2005

i am handling a project where user can choose the excel file and the field in the excel file to export into sql server 2005. which mean there will be dropdownlist where the user can choose the field and so on. anyone know how to do this?I'm a bit uncertain how this question is Asp.Net related. Please clarify how it is.|||which mean i will need to import the excel file into a dataset and then bind the dataset to the drop down list and at last import it to the sql server 2005. it can be in vb.net or asp.net

Import database from access

Hi
I am develop a project with .net in windows application.my source of the
database set in access I want to working with WMSDE and I need to import the
database under the access to database in WMSDE first Is it passible?
If yes, how can I do this.
Thanks for who refer to my question
doesn't access have an upsizing wizard in it ?
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:91853F12-5194-4D31-891F-0C5EAFC16E94@.microsoft.com...
> Hi
> I am develop a project with .net in windows application.my source of the
> database set in access I want to working with WMSDE and I need to import
> the
> database under the access to database in WMSDE first Is it passible?
> If yes, how can I do this.
> Thanks for who refer to my question

Import database from access

Hi
I am develop a project with .net in windows application.my source of the
database set in access I want to working with WMSDE and I need to import the
database under the access to database in WMSDE first Is it passible?
If yes, how can I do this.
Thanks for who refer to my questiondoesn't access have an upsizing wizard in it ?
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:91853F12-5194-4D31-891F-0C5EAFC16E94@.microsoft.com...
> Hi
> I am develop a project with .net in windows application.my source of the
> database set in access I want to working with WMSDE and I need to import
> the
> database under the access to database in WMSDE first Is it passible?
> If yes, how can I do this.
> Thanks for who refer to my question|||you can import the access via SQL Servers import function in the EMC.

Import database from access

Hi
I am develop a project with .net in windows application.my source of the
database set in access I want to working with WMSDE and I need to import th
e
database under the access to database in WMSDE first Is it passible?
If yes, how can I do this.
Thanks for who refer to my questiondoesn't access have an upsizing wizard in it ?
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:91853F12-5194-4D31-891F-0C5EAFC16E94@.microsoft.com...
> Hi
> I am develop a project with .net in windows application.my source of the
> database set in access I want to working with WMSDE and I need to import
> the
> database under the access to database in WMSDE first Is it passible?
> If yes, how can I do this.
> Thanks for who refer to my question

Import Data to SQL server from Excel spreadsheet

Hi all,

Firstly, i'm new to integration services and have only done a little with DTS jobs.

I'm trying to create an integration services project which will import data from an two worksheets in an Excel spreadsheet to two different tables in a database. I'm looking at only one table at present to make things a little more understandable.

One stipulation i have is that i need to be able to specify a variable value and insert that as an additional column in the database. I have and Excel source and a SQL destination both of which have been set up with there specific connection managers. I also have a variable which i add in using the derived column task.

When i try to debug this i am getting a few problems. I think these may be to do with the fact that although the worksheet in Excel has 20 rows (1st column shows these numbers) i only want those rows with data in them. If i preview the excel table it shows all the rows including those with null columns. Is there some sort of way that i can only get the rows that have data in the columns after the row number. I.e. can i select rows that do not have a second column value = to NULL.

I hope this makes sense and that someone can help me out with this problem.

All help is greatly appreciated.

Cheers,

Grant

P.S.

Apologies. I have this resolved now. I didn't see the option to use a SQL command as apposed to a table or view when setting up the Excel source.

I am still however getting the following errors which i'd appreciate some help on:

Error: 0xC0202009 at Data Flow Task, Excel Source [1]: An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0208265 at Data Flow Task, Excel Source [1]: Failed to retrieve long data for column "Rework Entry Information (BE SPECIFIC)".
Error: 0xC020901C at Data Flow Task, Excel Source [1]: There was an error with output column "Rework Entry Information" (170) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Data Flow Task, Excel Source [1]: The "output column "Rework Entry Information" (170)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Rework Entry Information" (170)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Excel Source" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Any help on this would be greatly appreciated.

GrantI'd also like to know how to go about specifying a variable as the datasource for my Excel connection. This is so that at runtime i can specify a number of different files to process.

Thank you,

Grant|||

You can use a ForEach loop container in your control flow to go through all files in a specific file system folder; then inside of that ForEach container add a dataflow task that does what you want. You may need to use an expression to change the connection string of your Excel Connection manager for every iteration (using the variable that has the collection value). I have never tried that before; this is just an idea.

good luck!

Rafael Salas

|||I had a similar problem importing into a SQL Server 2000 database with SQL Managment Studio. If you're using SQL 2000, try using the appropriate version of Enterprise Manager.|||I had a similar problem importing into a SQL Server 2000 database with SQL Managment Studio for SQL Server 2005. If you're using SQL Server 2000, try using the appropriate version of Enterprise Manager.

import AS to BID project - how to change cube storage location

After import of AS database to a new project I need to change the cube storage location because it probably points to a location on the server that is not valid in dev.

How can I change the cube storage location in the project (not from management studio)?

Regards,

Right click on the project in the solutions explorer window. Select properties and later deployment.

Regards

Thomas Ivarsson

|||

Thanks for your ansver.

In solution explorer properties I can change the project path - but - I can't see how I can change the storage location of the cube?

My problem is that I can't deploy my project after import of AS database to a new project.

Any ideas are welcome.

Regards,

|||

You can change the name of the database under server in the deployment option?

Thomas Ivarsson

|||

Yes - but it is the storage location of the cube - not the name.

|||

I understand. In the management studio you can right click the cube(properties), after deployment, and change the storage location property. You also have a datadir property for the whole SSAS2005.

I have not changed these properties myself so I do not know about any problems. I have not found any settings for this in the BI-Dev Studio.

Edit:I have. Right click the cube in BI-Dev and choose properties. Change the storage location for the cube.

Regards

Thomas Ivarsson

|||

Well - no.

I have tried that - it does not help.

My problem is that I can't deploy and I suppose it is because my project (after import) can't see my dev AS db.

In prod SQL server is installed at E:\...

in dev SQL server is installed at C:\... on 2 different boxes.

Do you know if you can create a project with the import AS template from the server and then move then project to my laptop to do dev?

Regards,

|||

Sorry. Back from the golf course.

I have tried the same thing without any problems. You say that you are unable to deploy the solution, it is not about file directories. You cannot deploy with standard settings?

Do you have security roles in your imported project that does not match the location /server that you would like to deploy to?

Next, are you using a network share for your data folder, like \\server\logicaldisk\program files ....?

Regards

Thomas Ivarsson

import an xml-file to sql2005

Dear all,
I'm trying to import an xml-file by passing the path i.e.
"D:\sql\project\bill_count_1.xml" to an db-procedure as in-param.
As a result whole the file-content should be saved in one column.
using SQL-2005.
The table looks like
create table test
(
id identity
, doc xml
)
You can solve the problem with openxml, openrowset, bulk insert and bcp
if you use the path as a string directly in the code but I could'nt
manage the import by sending the file-path as in-param to a db-proc
Thanks,
Every kind of help appr.
BR
AnwarHello anwar,
There's isn't any magic proc that does this for you. it can use dynamic SQL
inside of your own proc and openrowset to make this happen.
Cheers,
Kent Tegels
http://staff.develop.com/ktegels/

import an xml-file to sql2005

Dear all,
I'm trying to import an xml-file by passing the path i.e.
"D:\sql\project\bill_count_1.xml" to an db-procedure as in-param.
As a result whole the file-content should be saved in one column.
using SQL-2005.
The table looks like
create table test
(
id identity
, doc xml
)
You can solve the problem with openxml, openrowset, bulk insert and bcp
if you use the path as a string directly in the code but I could'nt
manage the import by sending the file-path as in-param to a db-proc
Thanks,
Every kind of help appr.
BR
AnwarHello anwar,
There's isn't any magic proc that does this for you. it can use dynamic SQL
inside of your own proc and openrowset to make this happen.
Cheers,
Kent Tegels
http://staff.develop.com/ktegels/

import an xml-file to sql2005

Dear all,
I'm trying to import an xml-file by passing the path i.e.
"D:\sql\project\bill_count_1.xml" to an db-procedure as in-param.
As a result whole the file-content should be saved in one column.
using SQL-2005.
The table looks like
create table test
(
id identity
, doc xml
)
You can solve the problem with openxml, openrowset, bulk insert and bcp
if you use the path as a string directly in the code but I could'nt
manage the import by sending the file-path as in-param to a db-proc
Thanks,
Every kind of help appr.
BR
Anwar
Hello anwar,
There's isn't any magic proc that does this for you. it can use dynamic SQL
inside of your own proc and openrowset to make this happen.
Cheers,
Kent Tegels
http://staff.develop.com/ktegels/

Import Access Reports

I had been successfully importing Access reports into SQL Reporting Services.
Now all of sudden when I go to the Project menu to select the Import option,
it's no longer there. I can't find it anywhere now. The only thing that I've
done since I was able to import is, load XPsp2. Am I missing something? Any
suggestions?Please see the following KB article:
Some programs seem to stop working after you install Windows XP Service Pack
2
http://support.microsoft.com/default.aspx?kbid=842242
You may have encountered this situation.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Huey" <Huey@.discussions.microsoft.com> wrote in message
news:A712782F-CC81-453C-92A3-E4FA99FEC197@.microsoft.com...
> I had been successfully importing Access reports into SQL Reporting
Services.
> Now all of sudden when I go to the Project menu to select the Import
option,
> it's no longer there. I can't find it anywhere now. The only thing that
I've
> done since I was able to import is, load XPsp2. Am I missing something?
Any
> suggestions?

Import access report

Hello,
I want to import access report into sql reporting, but i dont have the
option in the project menu "Import Reports "-->"Microsoft Access...".
I'm working on windows 2000 and i have installed Sql reporting server SP1.
Any Idea ?
Thank you for your help !James,
It's not in the Report menu, it's in the solution explorer.
In your report project, rightclick Reports folder and choose Import
Reports..
Jon Jahren
"James" <James@.discussions.microsoft.com> wrote in message
news:8E3F7A8A-4940-4B91-8337-D537CFF928FF@.microsoft.com...
> Hello,
> I want to import access report into sql reporting, but i dont have the
> option in the project menu "Import Reports "-->"Microsoft Access...".
> I'm working on windows 2000 and i have installed Sql reporting server SP1.
> Any Idea ?
> Thank you for your help !
>|||Thank you for your answer Jon, but the problem was that i had Access 2000 and
the option to import Access Report is available only with at least access
2002 !!!
I did an update and now it's working fine.