Tag Info

Hot answers tagged

5

A Fast Method A probably faster way to do this is the following: Provide the data in a database table Execute a MERGE statement that merges the data from this table into the source table To provide the data in a database table you can use sql loader or [external tables]. http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606 create ...


4

This can easily be done through an SSIS package and scheduled with SQL Server Agent. See this link for a tutorial/demonstration on the task: http://www.codeproject.com/Articles/402958/SSIS-Package-Export-Data-from-Database-Daily-to-Ne


3

You'll need to configure SSIS to run in 32-bit runtime as Excel does not support 64-bit Go to the Property page for the Solution, select Debugging and change Run64BitRuntime to False Running 32-bit SSIS in a 64-bit Environment


3

For something simple like what you have, I would highly recommend Google Docs or one of variants. It's basically a spreadsheet where everyone that you give permissions to can edit at the same time. Everyone can see what everyone else is doing, so there aren't many conflicts. A SQL database would certainly support simultaneous accesses, but I think it's ...


3

Great question! Especially the "monster query" part ;)The answer is this guy here, enjoy: Excel Data Source as Linked Server


3

The columns that are aligned to the right are deemed as numeric fields by Microsoft Excel, anything with a letter in it is deemed an alphanumeric field. I do not believe there is an export method in SQL Management that would have Excel change the alignment of the cells, any change in formatting would need to be done in Excel post-export. Alternatively, if ...


3

Thanks to @ConcernedOfTunbridgeWells for pointing me in the right direction on this. The issue was with the status expression. Since it ALWAYS returns a 1, 0 or -1 it didn't account for NULLs. I added a check at the beginning of the expression: CASE WHEN IsEmpty(KpiValue("MyKPI")) THEN NULL ...which correctly leaves those fields empty.


2

You have the 3 ways posted in the previous answer, or you can do it in plain sql, using OPENROWSET or OPENDATASOURCE. Some examples: SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ; SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel ...


2

Couple of ways that I know of Run procedure in ssms, then copy results into excel (make sure to turn on the option to copy column headers in tools,options,query results) Create a SQL server integration services (SSIS) package to run the procedure and write the results to a CDC or excel file. If you have SQL server reporting services (SSRS) you can create a ...


2

Assuming you have inserted the data into Oracle perhaps using an external table, you could use the connect by clause of a hierarchical query to generate the additional rows you need. Setup to simulate the source data: DROP TABLE t1; DROP TABLE t2; CREATE TABLE t1 (Zip Varchar2(7), Ground Varchar2(3), Air Varchar2(3)); INSERT INTO t1 VALUES ...


2

I only learned this in the past year. You can query an excel sheet as if it's a table. As long as your keys are also in excel you can join the excel sheet to the user table. You can have a look at this Microsoft Article that discusses options for importing from Excel to SQL Server. http://support.microsoft.com/kb/321686


2

Full disclosure.. I am the author of the following program. Give it a try and let me know what you think. I have tried many applications including the excel add-in from MySQL, but was not satisfied with them, so I wrote my own. The current version of my program is slow (just like the MySQL add-in), doesn't support international characters, and doesn't ...


2

Save it as .csv and then use the mySQL CLI [or other client-side utility] to issue a LOAD DATA LOCAL INFILE statement.


2

I haven't tried any of the followings. But just sharing with the hope that this may help you... http://blog.mclaughlinsoftware.com/2011/02/27/excel-2011-query-mysql/ http://blog.mclaughlinsoftware.com/microsoft-excel/excel-2011-mysql-config/ Source: http://forums.mysql.com/read.php?171,561930,561930 FYI, MySQL officially introduced a tool namely MySQL for ...


2

The simplest way I can think of is probably writing some VBA code in the Excel workbook. How much effort that is depends on factors like how many columns you have in each sheet, what format the data is in, how similar each sheet is, and how often you are going to have to do this. Does all the data from each sheet go in to a one table or multiple tables in ...


2

Your option 1 is good as you can just create a staging table called "Excel_Data". Then once you import data in sql server, you can just write a query to just report the data that is both in the database and in the excel spreadsheet. The data loading can be automated by Bulk Insert or using SSIS. Also, you can use Linked server to directly query the excel ...


2

You can use Excel to save the file as a .CSV (comma-separated-values), and use SQL Server Management Studio to import the file either into an existing table, or potentially a new table. In SQL Server Management Studio, you select the desired target database, right-click the database name, then click 'Import Data', and go through the wizard step-by-step.


2

I can speak for Postgres. PostgreSQL is really fast to copy to if you are a super user. Postgresql has a copy to command. Merely save the file as a csv (which can also be opened in excel) and use: COPY table FROM 'fpath' CSV HEADER DELIMITER ';' You can copy anything with COPY (SELECT STATEMENT) TO 'fpath' as well. If you must use excel formats like ...


2

Step1: Create Function create Function [dbo].[fnSplit] ( @List varchar(8000), @Delimiter char(1) = ',' ) Returns @Temp1 Table ( ItemId int Identity(1, 1) NOT NULL PRIMARY KEY, Item varchar(8000) NULL ) As Begin Declare @item varchar(4000), @iPos int Set @Delimiter = ...


1

I have just had the same problem yesterday, and here is how I was able solve it: Although the "Disallow adhoc access" is not enabled at the Provider Options page (SQL Server Management Studio), the Registry does not have the DisallowadHocAccess = 0 key, and for some reason it is required to have it there. After I added the key to the registry, I was ...


1

After some careful thought and some experimentation, the AccessDatabaseEngine.exe installed on the server is probably part of the problem. There are actually two versions 32 bit and 64 bit. On my computer 64 bit, I have the 32 bit version of Office installed. The SQL Server on my computer is 64 bit. When I attempt to use openrowset with the same OLEDB ...


1

We finally resolved the issue. It turns out that SSIS calculates the length based on the first handful of rows in the excel file. When we moved the rows with the longer data to the top the columns changed to unicode text (allowing for the extra length).


1

I tested this in SSIS from a 2008 R2 install and Excel 2010. Using the Excel 97-2003 output format in the connection manager, I was able to reproduce the case where there was the header name in the first row, then a blank, then the data. But changing the "First row has column names" had a different effect for me: I still got the header row, but no blank. ...


1

Check Windows Event Viewer. Look in the application logs, security logs, and system logs. If you've nailed down all of the obvious stuff (and it sounds like you have), then it could be anything. The file's folder could have Windows domain security on it, and your machine might not be able to authenticate against the DC, for example.


1

I would Export the Excel file to a Text file (e.g. tab-delimited) and load it into SSIS in that format. It will be much quicker and less subject to odd errors which sometimes come up when reading Excel files. I would expect any attempt at parallel Excel imports to be slower than a single import - Excel is not a server.


1

Since MySQL 5.1, there has been a native tool called MySQL for Excel. It is bundled along with MySQL for Visual Studio, a bunch of connectors, sample databases and so forth. The MySQL Documentation has a webpage for MSI Installation Editing Importing Appending Exporting There is also a forum for users of this product (As of this posting, there are 5 ...


1

To use an Excel file as a source you need to define a named range containing the cells with data (that's Step 1 of the instruction you referenced in the question). All the named ranges found in the file will be shown in the Select tables pane. After you select a table (i.e. a range) and click OK, a new source will be created. You can also consider ...


1

You might want to use simple SSIS package to do that. Just create a new project in Business Intelligence Development Studio (VS2008) - it goes along with yur SQL Server installation and very easy to start.


1

Not sure about modifying the excel file (have you tried deleting rows?). As far as modifying files on the filesystem... if you enable xp_cmdshell you can run any command line commands on the system (like copy or delete).


1

I have got one very useful link which i have not tried. But, its a great stuff by author...



Only top voted, non community-wiki answers of a minimum length are eligible