Extra character

Hi,

I used excel to import data to my database, I found out a problem, my program is linked with the database, when the program show data from the database, it has an extra '@.' symbol, In order to remove it, I need to go to the database to press space bar and backspace at the field. How could I use SQL instead of using space bar and backspace?

Thanks
FrenkHi,

I used excel to import data to my database, I found out a problem, my program is linked with the database, when the program show data from the database, it has an extra '@.' symbol, In order to remove it, I need to go to the database to press space bar and backspace at the field. How could I use SQL instead of using space bar and backspace?

Thanks
Frenk

If this is not a display problem, you can use replace() function to clean up the text.

e.g.
select replace(col,'@.',space(0))
from tb|||but the @. can not be seen in the database, and sometime it shows # instead could you please let me know what's happen|||Do those characters occur as the first character?|||no, it occurs at last character

that I normally do, first go to the last character, then press space bar and backspace, the @. or # will be disappeared|||Run this query and see if you see those characters

Select SubString(yourCol,1,len(yourCol)-1) from yourTable|||I can not see those character, the last character of each reocrd is missing.|||Well. Use that query to display in your application
If you want to remove those from table, then

Update yourTable Set yourCol=SubString(yourCol,1,len(yourCol)-1) where SubString(yourCol,1,len(yourCol)-1) in ('@.','#')

Externally triggered DTS to import excel data to SQL server

I need to extract data from an excel file to my SQL Server 2000 database. Users used to do this themselves through an ASP script I developed but some data in certain cells are invariably lost, NULL value instead is recorded (according to Microsoft this is the problem w/ using excel as a data source).

To get around this problem I asked my users to send me their excel files so I could import the data manualy using SQL Server's Import Data facility. But, this is not acceptable. They should be able to do this themselves w/o my intervention.

There is already an "upload file to server" facility that they can use. And after uploading I was thinking of using DTS to automatically import the data from excel. But the DTS package is normaly executed based on a set schedule. What I need is for users to upload the excel file to the server, then for them to trigger the DTS package w/o directly accessing the SQL server database.

Is this possible? Can I create a stored procedure that will execute the DTS package? I'm not quite familiar w/ stored procedures although I'm trying to learn more about it right now.

Here's a sample excel data source, info.xls:
Name Age State
John Smith 30 NY
Anne Collins 25 CA
Mike Peterson 22 TX

Destination db and table: dbUser, tblInfo
Fields: tName(nvarchar, 50), iAge(numeric, 3), tState(nvarchar, 2)

Any assistance on this will be highly appreciated. Thanks!You could get a stored procedure to start the scheduled job which is running the DTS package. A basic stored procedure to run the job would be:

CREATE PROCEDURE sp_StartDTS

AS

BEGIN

EXEC msdb..sp_start_job @.job_name = 'The DTS job name'

END

You can also use the job id etc... do a search in the Books Online for sp_start_job and you'll get the syntax. There is also a success/fail return code which you could use in the ASP page|||Originally posted by jasper627
I need to extract data from an excel file to my SQL Server 2000 database. Users used to do this themselves through an ASP script I developed but some data in certain cells are invariably lost, NULL value instead is recorded (according to Microsoft this is the problem w/ using excel as a data source).

To get around this problem I asked my users to send me their excel files so I could import the data manualy using SQL Server's Import Data facility. But, this is not acceptable. They should be able to do this themselves w/o my intervention.

There is already an "upload file to server" facility that they can use. And after uploading I was thinking of using DTS to automatically import the data from excel. But the DTS package is normaly executed based on a set schedule. What I need is for users to upload the excel file to the server, then for them to trigger the DTS package w/o directly accessing the SQL server database.

Is this possible? Can I create a stored procedure that will execute the DTS package? I'm not quite familiar w/ stored procedures although I'm trying to learn more about it right now.

Here's a sample excel data source, info.xls:
Name Age State
John Smith 30 NY
Anne Collins 25 CA
Mike Peterson 22 TX

Destination db and table: dbUser, tblInfo
Fields: tName(nvarchar, 50), iAge(numeric, 3), tState(nvarchar, 2)

Any assistance on this will be highly appreciated. Thanks!

to overcome bad data in the Excel spreadsheet you could import the data into a holding table that will allow nulls or other bad data, then run some SQL over the table identitfying good records by updating a bit field in the table. If the types of data errors are known and can be fixed automatically eg NULL should be 0 then you could fix that either in the DTS package with a VB script or later with SQL.

External table is not in the expected format

Hi,

I am trying to import an excel spreadsheet to the sql server database, I have 7 spreadsheets. in that 6 of them work fine, but when i try to import the 7th i am getting and error called

External Table is not in the expected format

System.Data.OleDb.OleDbException: External table is not in the expected format

Any help will be appreciated.

Regards,

Karen

Try to save the Excel file in cvs format before the importing. There can be something in the excel document that mess up the import.

|||

Thanks for ur answer.. but the other 6 files work fine.|||

There might be something in the 7th file that messes up things. Try CSV and see if it helps. If it doesn't then something else is wrong...

|||

Johram,

I tried converting it to a csv file and getting the same error

|||

OK, you gotta make sure all values in a column is in the same format. Upon import, the first row is scanned in order to determine the datatype of each column. If he find a number in the first column, then he assumes that this is a numerical column. If there is character data in the column somewhere then there will be an error. Maybe it's not character data that's your problem, but such a thing as an empty value or a decimal value when an integer value is expected.

If you open up the 7th file and scan through the rows, then you might spot the deviation?

See this article:http://blog.lab49.com/?p=196

Good luck!

|||

Johram,

Thanks a lot for your help.. the reason i was getting that was i had given

flSubAdvisor.PostedFile.SaveAs(location6)

instead of

flGrowth10K.PostedFile.SaveAs(location6)

and

flSubAdvisor.PostedFile.SaveAs(location6) was equal nothing or it was empty..

any way thanks a lot for ur help.

Regards

Karen

Express ... DTS or SSIS

Chris Marsh wrote:
> Is there a download or way that DTS or SSIS can work with SQL Express 2005
.
> I need to import data at a client site on a live database.
> Thanks
http://msdn2.microsoft.com/en-us/library/ms143706.aspxThanks Tracy however I am confused about something after reading the KB you
pointed me to. It states "The Import and Export Utility that is included
with SQL Server 2005 Express Edition is not the SQL Server Import and Export
Wizard". So, where do I find the Import Export Utility to see if it will
resolve my needs? I just don't see it anywhere.
Again, thank you.
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1150207527.154929.271070@.u72g2000cwu.googlegroups.com...
> Chris Marsh wrote:
>
> http://msdn2.microsoft.com/en-us/library/ms143706.aspx
>|||Chris Marsh wrote:
> Thanks Tracy however I am confused about something after reading the KB yo
u
> pointed me to. It states "The Import and Export Utility that is included
> with SQL Server 2005 Express Edition is not the SQL Server Import and Expo
rt
> Wizard". So, where do I find the Import Export Utility to see if it will
> resolve my needs? I just don't see it anywhere.
> Again, thank you.
Sorry, I don't have Express installed anywhere. I did search Google
for "sqlserver express import", and turned up lots of stuff. You might
find something useful there.|||Thanks...I'm blind I just cannot find it
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1150212092.326676.276420@.f6g2000cwb.googlegroups.com...
> Chris Marsh wrote:
> Sorry, I don't have Express installed anywhere. I did search Google
> for "sqlserver express import", and turned up lots of stuff. You might
> find something useful there.
>|||Is there a download or way that DTS or SSIS can work with SQL Express 2005.
I need to import data at a client site on a live database.
Thanks|||Chris Marsh wrote:
> Is there a download or way that DTS or SSIS can work with SQL Express 2005
.
> I need to import data at a client site on a live database.
> Thanks
http://msdn2.microsoft.com/en-us/library/ms143706.aspx|||Thanks Tracy however I am confused about something after reading the KB you
pointed me to. It states "The Import and Export Utility that is included
with SQL Server 2005 Express Edition is not the SQL Server Import and Export
Wizard". So, where do I find the Import Export Utility to see if it will
resolve my needs? I just don't see it anywhere.
Again, thank you.
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1150207527.154929.271070@.u72g2000cwu.googlegroups.com...
> Chris Marsh wrote:
>
> http://msdn2.microsoft.com/en-us/library/ms143706.aspx
>|||Chris Marsh wrote:
> Thanks Tracy however I am confused about something after reading the KB yo
u
> pointed me to. It states "The Import and Export Utility that is included
> with SQL Server 2005 Express Edition is not the SQL Server Import and Expo
rt
> Wizard". So, where do I find the Import Export Utility to see if it will
> resolve my needs? I just don't see it anywhere.
> Again, thank you.
Sorry, I don't have Express installed anywhere. I did search Google
for "sqlserver express import", and turned up lots of stuff. You might
find something useful there.|||Thanks...I'm blind I just cannot find it
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1150212092.326676.276420@.f6g2000cwb.googlegroups.com...
> Chris Marsh wrote:
> Sorry, I don't have Express installed anywhere. I did search Google
> for "sqlserver express import", and turned up lots of stuff. You might
> find something useful there.
>

Express ... DTS or SSIS

Is there a download or way that DTS or SSIS can work with SQL Express 2005.
I need to import data at a client site on a live database.
ThanksChris Marsh wrote:
> Is there a download or way that DTS or SSIS can work with SQL Express 2005.
> I need to import data at a client site on a live database.
> Thanks
http://msdn2.microsoft.com/en-us/library/ms143706.aspx|||Thanks Tracy however I am confused about something after reading the KB you
pointed me to. It states "The Import and Export Utility that is included
with SQL Server 2005 Express Edition is not the SQL Server Import and Export
Wizard". So, where do I find the Import Export Utility to see if it will
resolve my needs? I just don't see it anywhere.
Again, thank you.
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1150207527.154929.271070@.u72g2000cwu.googlegroups.com...
> Chris Marsh wrote:
>> Is there a download or way that DTS or SSIS can work with SQL Express
>> 2005.
>> I need to import data at a client site on a live database.
>> Thanks
>
> http://msdn2.microsoft.com/en-us/library/ms143706.aspx
>|||Chris Marsh wrote:
> Thanks Tracy however I am confused about something after reading the KB you
> pointed me to. It states "The Import and Export Utility that is included
> with SQL Server 2005 Express Edition is not the SQL Server Import and Export
> Wizard". So, where do I find the Import Export Utility to see if it will
> resolve my needs? I just don't see it anywhere.
> Again, thank you.
Sorry, I don't have Express installed anywhere. I did search Google
for "sqlserver express import", and turned up lots of stuff. You might
find something useful there.|||Thanks...I'm blind I just cannot find it:(
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1150212092.326676.276420@.f6g2000cwb.googlegroups.com...
> Chris Marsh wrote:
>> Thanks Tracy however I am confused about something after reading the KB
>> you
>> pointed me to. It states "The Import and Export Utility that is included
>> with SQL Server 2005 Express Edition is not the SQL Server Import and
>> Export
>> Wizard". So, where do I find the Import Export Utility to see if it will
>> resolve my needs? I just don't see it anywhere.
>> Again, thank you.
> Sorry, I don't have Express installed anywhere. I did search Google
> for "sqlserver express import", and turned up lots of stuff. You might
> find something useful there.
>

exporting/importing tables

Hi there.
What is the best way to export/import a SQL table from one SQL server on a
computer to another computer.
I am a contractor, working on my laptop, with a company, that will not allow
me to hook my laptop up to their network.
I keep a 'copy' of what is on their SQL server, and am constantly updating,
back and forth between my laptop and their server.
Because I didn't know a better way, I have been exporting to an access file
on disk, then importing back into SQL on the other computer.
Generally this works okay, but lately I have been running into some problems
with datatypes being converted etc.
I'm assuming there is a much better way to do this.
Am I right?
Thanks in advance!
amberSimplest is to use a SQL Backup file... If this is production system, then
there are probably backup files already been done regularly that you should
be able to get acces to... If it's a development dataabse, then just do the
backup yourself, to a disk file. on a network UNC of your choice, where you
can copy the file to whatever medium you are using to get it to your laptop,
then just "restore" the backup to the SQL Server Database on your laptop.
Two gotchas to be aware of, When you select Restore in the SQL Enterprise
Mgr GUI, go to the options tab, and check the "Force Restore over existing
database" option checkbox, and type over the file locations on the far right
column, with the physical file locations on your laptop.
"amber" wrote:

> Hi there.
> What is the best way to export/import a SQL table from one SQL server on a
> computer to another computer.
> I am a contractor, working on my laptop, with a company, that will not all
ow
> me to hook my laptop up to their network.
> I keep a 'copy' of what is on their SQL server, and am constantly updating
,
> back and forth between my laptop and their server.
> Because I didn't know a better way, I have been exporting to an access fil
e
> on disk, then importing back into SQL on the other computer.
> Generally this works okay, but lately I have been running into some proble
ms
> with datatypes being converted etc.
> I'm assuming there is a much better way to do this.
> Am I right?
> Thanks in advance!
> amber
>|||If you are talking about data, then take a look to BCP utility or DTS, to
create csv files.
AMB
"amber" wrote:

> Hi there.
> What is the best way to export/import a SQL table from one SQL server on a
> computer to another computer.
> I am a contractor, working on my laptop, with a company, that will not all
ow
> me to hook my laptop up to their network.
> I keep a 'copy' of what is on their SQL server, and am constantly updating
,
> back and forth between my laptop and their server.
> Because I didn't know a better way, I have been exporting to an access fil
e
> on disk, then importing back into SQL on the other computer.
> Generally this works okay, but lately I have been running into some proble
ms
> with datatypes being converted etc.
> I'm assuming there is a much better way to do this.
> Am I right?
> Thanks in advance!
> amber
>|||You can script out the table. Create insert statements for the data using th
e
following script:
http://vyaskn.tripod.com/code.htm#inserts
Then run the table create script and data insert script on your destination
computer.
HTH
ZULFIQAR SYED
"Alejandro Mesa" wrote:
> If you are talking about data, then take a look to BCP utility or DTS, to
> create csv files.
>
> AMB
> "amber" wrote:
>|||Script your data with www.sqlscripter.com
There's an option like "Insert new, update existing" records ...
Thomas
"amber" wrote:

> Hi there.
> What is the best way to export/import a SQL table from one SQL server on a
> computer to another computer.
> I am a contractor, working on my laptop, with a company, that will not all
ow
> me to hook my laptop up to their network.
> I keep a 'copy' of what is on their SQL server, and am constantly updating
,
> back and forth between my laptop and their server.
> Because I didn't know a better way, I have been exporting to an access fil
e
> on disk, then importing back into SQL on the other computer.
> Generally this works okay, but lately I have been running into some proble
ms
> with datatypes being converted etc.
> I'm assuming there is a much better way to do this.
> Am I right?
> Thanks in advance!
> amber
>