Need Some Advise On Loading Data From Excel Sheet

Oct 25, 2005

I insert/update thousands of line items daily to my MS SQL 2k db each day from multiple excel sheets that are uploaded. In Microsoft's infinite wisdom Excel and MS SQL is not "Fully" compatible and some characters throw off the uploads, cause errors in loading, etc. Each Excel sheet has from a few rows up to 50,000 rows or more. We load around 100 of these Excel sheets each day depending on what our users upload.

Our main problem appears to be with "Special Characters", anything that is not a number or letter seems to be an issue in loads. We have written our scripts to ignore a certain set of characters such as #,!, -, ', ", [, ], {, }, +, =, *, %, ~, `, <,>, etc. But we still get errors. This has become a frustrating nightmare. Any help in the right direction would be greatly appreciated.


I have tried ASP scripts, VB created exe's, converting the Excel sheet to a text file, then uploading, and other various means to get this process error free. Some files never have issues loading, some excel files will error out and not at the same point each time. We can run the same file 5 times in a row and it will stop/error at a different point each time without any rhyme or reason.

Now we are not just doing an "Insert", there are several variables that are at work when loading the data, like combining exact items into one row, associating data with ID's in another table, etc. It is not just a simple, take this data and place it here scenerio which makes this a serious headache to figure out how to make this error free and troubleshoot.

Is there some information or a direction I should look to consider a solid solution to importing data from Excel sheets to a MS SQL 2k db? These files are loaded into a specific folder and on upload they are also recorded in a table marked ready for update in the db. Our scheduler runs the exe associated with that users ID and loads their data, overwriting their previous data load, then marks the file as done.

Is there a proven method, some external program that can be used to make this a solid process, or any direction you can provide for me to research?

View 3 Replies


ADVERTISEMENT

[SQL Serevr 2005 Express] Loading Data From An Excel Sheet

Apr 20, 2006

Hi,

View 3 Replies View Related

Loading Data From Multiple Rows Into Single Row In Excel Sheet

Jan 9, 2008


Hi,

I want to load data into Excel file with following format,





Country

State

Total

Location


ABC

A

20

X1


30

Y1


C

100




XYZ

X

40



Basically I want to insert records from multiple rows into a single row; how can I achieve this using SSIS.
I am using Excel as a data source.

Any help is appreciated.

Regards,
Omkar.

View 8 Replies View Related

C# Code Or Document For Loading Excel Sheet Into An Sql Table

Dec 3, 2004

Hi,

I am trying to find some document or code that will load an excel spreadsheet into an sqlserver database.

Can anyone please point me in the right direction.

View 2 Replies View Related

Loading An Excel Sheet That Gets Populated With Value After Opening In Ssis

Nov 6, 2007



I have an excel sheet that has a bunch of columns. Some of these columns have static data but there are a few of the columns that retrieve data by making calls to servers and returning values that populate these columns. Usually about 30 mins before the columns are populated.

I then need to to load the excel sheet into a table.

I was wondering if there was anyway by which we can make the connection to the excel sheet and then force a delay of about 30 mins before it starts retriving the data?

Is this possible in ssis? Or can I achieve it by some other means?

Thanks for any help in advance

View 6 Replies View Related

Integration Services :: For-each Loop Container For Loading Excel Sheet

Aug 10, 2015

I have used for-each loop container for loading excel sheet contains multiple sheets with same structure. It is loading data into SQL table even there is no data in sheets.

View 3 Replies View Related

Transform Data To Excel Sheet Or Xml

Apr 21, 2006

Does SQL 2005 Express have standard functionality on board to export data as an excel sheet? or as XML in a specified format?

View 4 Replies View Related

Importing Data From Excel Sheet

Oct 19, 2007

i using the select command

select * from [excel sheet name$];

i also using open file dialoug to specify the excel book this book should be pass to the select command at run time
as a parameter

so plz help me with suitable example

View 1 Replies View Related

Import Data From Excel-Sheet Via OleDb In VB.Net - How To Get A Columns Data As String?

Oct 25, 2007

Hello,

i want to import data from an excel sheet into a database. While reading from the excel sheet OleDb automatically guesses the Datatype of each column. My Problem is the first A Column which contains ~240 Lines. 210 Lines are Numbers, the latter 30 do contain strings. When i use this code:







Code BlockDim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & conf_path_current & file_to_import & ";Extended Properties=""Excel 8.0;HDR=NO"""
Dim oConn As New OleDb.OleDbConnection(sConn)
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Table$]", oConn)
Dim rdr As OleDb.OleDbDataReader = cmd1.ExecuteReader
Do While rdr.Read()
Console.WriteLine(rdr.Item(0)) 'or rdr(0).ToString
Next




it will continue to read the stuff till the String-Lines are coming.
when using Item(0), it just crashes for trying to convert a DBNull to a String, when using rdr(0).ToString() it just gives me no value.

So my question is how to tell OleDB that i want that column to be completly read as String/Varchar?

Thanks for Reading

- Pierre from Berlin


[seems i got redirected into the wrong forum, please move into the correct one]

View 1 Replies View Related

Transfer Data From An Excel Sheet To Table In Sql Data Base

Nov 15, 2006

I have an excel sheet that contain colummns as in a table in a sql database i want to transfer this data from the sheet to the table frombusiness logic code layer not from the enterprise manager by wizardwhat can i do?? ...please urgent

View 1 Replies View Related

Import Data From Excel Sheet To Sql Database-asp.net 2.0

Jul 5, 2006

In admin tool of my application,i want to give facility  to administrator that he can import data from the Excel Sheet and can insert in sql database. for example...user id and password that from excel sheet to user table in sql database.
how can i do this..please help me. it's urgent.
thanks
raj

View 1 Replies View Related

How To Export Data From DB Table To Excel Sheet

Dec 5, 2005

Hi .,
  Can any one guide me in exporting data from DB table to excel sheet .
 
Thanks,
vijay
 

View 3 Replies View Related

Transfer SQL Data Results To Excel Sheet

May 16, 2007

Code:

-- (1) Number of calls received for each priority of call [for a specified date range]

declare @startdate datetime,
@finishdate datetime

select RM.fldPriorityCode as 'Priority',
count(RM.fldRequestID) as 'Calls'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '03-05-2007'
and RM.fldRequestFlag like 'D'
group by RM.fldPriorityCode
union
select
'Total' as 'Priority',
count(RM.fldRequestID) as 'Calls'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '03-05-2007'
and RM.fldRequestFlag like 'D'
order by RM.fldPriorityCode asc


Results:

PriorityCalls
120
22912
3152
4571
54
Total3659




I would like to transfer these results to an excel sheet. For instance when the user opens up the excel worksheet and types in for a example a start date: 01-01-2007 and an end date: 03-05-2007 (into textboxes) then clicks a button say called 'Get stats' and then the results appear on the sheet.


How can this be done?

View 1 Replies View Related

Transfer SQL Data Results To Excel Sheet

May 16, 2007

-- (1) Number of calls received for each priority of call [for a specified date range]

declare @startdate datetime,
@finishdate datetime

select RM.fldPriorityCode as 'Priority',
count(RM.fldRequestID) as 'Calls'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '03-05-2007'
and RM.fldRequestFlag like 'D'
group by RM.fldPriorityCode
union
select
'Total' as 'Priority',
count(RM.fldRequestID) as 'Calls'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '03-05-2007'
and RM.fldRequestFlag like 'D'
order by RM.fldPriorityCode asc


Results:

PriorityCalls
120
22912
3152
4571
54
Total3659


I would like to transfer these results to an excel sheet. For instance when the user opens up the excel worksheet and types in for a example a start date: 01-01-2007 and an end date: 03-05-2007 (into textboxes) then clicks a button say called 'Get stats' and then the results appear on the sheet.


How can this be done?

View 2 Replies View Related

Empty Rows In Excel Data Sheet Used In DTS

Mar 1, 2004

Hi Everyone,

I am using a DTS package where one of the inputs is an Excel Sheet. Actually this sheet is updated manually whenever required i.e once a week or sometimes once a month, but the DTS package runs everyday.

Whenever new rows are added or deleted manually in the excel sheet, empty rows are showed in the sheet after the last row of data. This hinders the DTS package, because the destination table to which the data in the Excel sheet is sent has Primary keys in it.

Can anyone suggest me how to avoid getting the empty spaces in the excel sheet.

Thanks in advance.

Regards,
kalyan

View 3 Replies View Related

T-SQL (SS2K8) :: Inserting Data From Excel Sheet

Dec 9, 2014

I am trying to insert data into a table from an excel sheet using bulk insert statement.

This excel sheet has number of tabs.

How can I mention a specific tab in bulk insert statement.

View 5 Replies View Related

Hw To Import Data From Excel Sheet To Sql Server

Apr 7, 2008

Hello All,

Can any one tell me how to import data from excel sheet to sql server 2005?

I have made table ready bt i dont know what i have to do for the above purpose.

Thanks
Prashant Hirani

View 3 Replies View Related

Writing Data In To Excel Spread Sheet

May 14, 2008

Hi All,

Here is the scenario:

I am inserting data into a spread sheet from user interface(power builder). But at the same time some one can open that excel spread sheet to read the data. Then the process was going to fail(it won't able to write the data in to the spread sheet). How to avoid this situation? I really appreciate if anyone can shed some light.

Thanks in advance!!

View 2 Replies View Related

Import Excel Data Sheet To SQL Table - How To ??

Mar 18, 2006

I need to make a script in SQL 2005 to import data from an Excel sheet into a SQL table.
I am using the wizard to import now.
Import from Excel 2000. First row of the excel sheet has column names.
Excel file name is: EXL.xls, sheet name is: Sheet1
Destination sql database name is: NM, table name is: Sht1
I use SQL Server Authentication to access the database.
User name: ABC and password: DEF
Database name is: DB
I am using the following setting when importing now:
- Delete rows in destination table
- Enable identity insert

View 3 Replies View Related

Exporting Data From Excel Sheet To Db Table

Oct 9, 2007

i have to export the data from excel sheet to database table. for that i created linked server in sql 2000. after creating i get the SQLOLEDB error when i expand the linked server in enterprise manager.Can anyone help me in solving this issue.

View 5 Replies View Related

Import Data From Excel Sheet Dynamically.

Feb 26, 2008

Hi,

Curently I am using a DTS package which is used to import data from Excel sheet into sql dollar table.

Now, the no. of Excel sheets is more than one and everytime the DTS package and VB Code has to be updated and sql dollar tables has to be increased to the no. of Excel sheets available.

The DTS package being executed by VB Code(.EXE).

How can I modify the DTS package and VB Code so that the import can be done dynamically irrespective of no. of Excel Sheets.

If anybody have a solution for this ,

Kindly let me know.

Regards,
Srinivas Alwala

View 3 Replies View Related

Change The Sheet Name While Exporting Sql Data To Excel

Mar 15, 2007

i have designed a rdl form which contains 3 tables .. i gave page break for each table. when exporting the file to excel it generates 3 sheets .. three sheets name comes like sheet 1, sheet2 ,sheet3.. i dont want it to be like these.. instead of that i have to give my own name while generating reports from rdl form like this (s1,s2,s3)...

View 1 Replies View Related

Change The Sheet Name While Exporting Sql Data To Excel

Mar 15, 2007

i have designed a rdl form which contains 3 tables .. i gave page break for each table. when exporting the file to excel it generates 3 sheets .. three sheets name comes like sheet 1, sheet2 ,sheet3.. i dont want it to be like these.. instead of that i have to give my own name while generating reports from rdl form like this (s1,s2,s3)...

View 4 Replies View Related

Data Not Shown, When Report Exported To Excel-Sheet

Feb 21, 2008

Hi everybody,

I'm quite new in Reporting Services and have the following problem:

I created a report which is getting its data from 5 different databases. For every database I made a subreport to get the data. In the preview and on the Reporting Server all the data is displayed find. Now I want to export the report to an Excel sheet which does work. But where the data from the subreports is to be shown it just says that subreports in table-cells has been ignored.

Another problem is that the way the report looks in the preview is fine - on the reporting server the columns are far to wide. I allready marked that the width should be fixed.

Some more information: in the main report some cells are merged. In these merged cells is the data from the subreport to be shown. The subreport displays the data in a table which has exact the same amount of colums (and the same size) the cells in the main report had before merging.

Thanks in advance,

Steph

View 5 Replies View Related

Export Data From Sql Server Tables Into Excel Sheet

Jan 23, 2008



I have a predefined excel sheet template.I want to create excel sheet report by exporting data from sql server tables into this excel sheet template.

This package i will be calling from C#.net windows form application.

I want to know is this is possible through Sql server integration service (SSIS) ?

Thanks,
Sumit

View 3 Replies View Related

(urgent)Insert Data From A Excel Spread Sheet To SQl Server

Oct 4, 2007

Hi,
 
  I have a spread sheet which has 4 columns called cusip, Chartheader, growthdates and NAV.. and i also have the same number of columns in the Sql server... and I want to add another column called Rownumber and set it as int indentity... and when i try to import the data to sql server i am getting this error called
Received an invalid column length from the bcp client for colid 1.
 
How should i fix it.
Regards
Karen

View 7 Replies View Related

VB Script To Copy A Range Of Data From One Excel Sheet To New Excelsheet

Jun 11, 2007

Hi can anybody let me know the script to copy a range of data from one excel sheet to new excelsheet.

thanks,

Prash

View 3 Replies View Related

Exporting Data To Excel Sheet Through DTS Package In SqlServer 2000

Feb 11, 2008

Hi,

Can any one tell me how to export data to excel sheet through Data Transformation Services(DTS) package in sql server 2000?


Thanks in advance.

View 3 Replies View Related

Getting Null Values While Importing Data From Excel Sheet To Sqql Server

Aug 16, 2007

Hai Friends,
Actually i want to import excel sheet in to sql server tables. I am writing the following query                                                                                           SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:Book1.xls','SELECT * FROM [Sheet1$]') and able to retireve the data but the problem is the data present in the excel sheet is





Username
password

hello
12345

12345
hello

hai-123
haihai

haihello
hello-123
Once i run this query i am getting it as                                                                                                                                                              
Username Password                                                                                                                                                                                           hello NULLNULL hellohai-123 haihaihaihello hello-123can anybody please help me out in this case and how to solve this problem and if you have any piece of code please pass it on to me and i am in very urgent need waiting for your replies thanks in advance.

View 2 Replies View Related

Integration Services :: Can't Preview Data In Excel Source Editor Unless Sheet Is Open?

Nov 10, 2010

I have a package which has an Excel source with the 'Data access mode' set to SQL command and then a sql select statement.  When I try and hit the 'Preview...' button below the 'SQL command text' window I get the following error:

 "Error at Standard Data Flow Tasks [source tasks name]: No column information was returned by the SQL command"
 
Ordinarily this would be down to the fact that my SQL is shocking, I hit the 'Preview...' button whilst the workbook the source is pointing at was open and it works fine??
 
I can't figure this out, but needless to say the package errors with a NEEDSNEWMETADATA when I try and run it.

View 17 Replies View Related

Integration Services :: Data Import From Excel Multiple Sheets With A Pattern Of Sheet Names

Aug 20, 2015

I have an excel file which contains lots of sheets. Some of them are named as DW-<day>-<month> (for e.g; DW-1-July). Like this I have sheets for the whole month. I have other sheets too with a different name. I would like to import data from these sheets only (DW ones). Upon my research I have found that this can be achieved via For Each Loop Container (I guess!). 

Post data import, I have a set of T-SQL query that I plan to execute via Execute SQL Task. 

View 5 Replies View Related

Loading Data From Excel

May 20, 2008

Can this be done in SSIS?

I have an xls spreadsheet with multiple worksheets. From each worksheet I need to load the data into sql server. The data I need to retrieve from each worksheet is not row by row, but cell by cell. For example, I need to load data from cells C44, G65, I23, A78, etc. in all the worksheets. Is this possible using SSIS?

View 3 Replies View Related

Excel Destination Data Flow Component Shows No Sheet Name Or Output Column Names For Mappings

Mar 8, 2008



I have a data flow that consists of

OLE DB source which calls a stored proc that returns a result set

data conversion

Excel destination
I am in design mode in Business Intelligence studio. My excel destination (with an Excel Connection) shows no sheet name though I have an execute SQL task before the data flow to create the excel table called SHEET1. Needless to say, there are no output columns visible to do any mappings. I did go to the ExcelConnection to set the OpenRowset Property to SHEET1 but it seems to have no effect.

I can do the export in SQL Server Management studio and that works fine, but it is basic and does not meet my requirements. I have to customize the package to allow dynamic Excel filenames based on account names and have to split my result set into multiple excel sheets because excel 2003 has a max of 65536 rows per sheet. Also when I use the export wizard, I have the source as a table and eventually the source has to be a stored proc with input parms.

What am I missing or doing wrong? Thanks in advance

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved