Tag Info

Hot answers tagged

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

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

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

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

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

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'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


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

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

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 ...


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

Ok well I found the solution or at least its one or both of the two Instead of running express with the Network Service account, I created a regular user. Then I gave that user rights to the folder with the excel spreadsheets. I had already installed AccessDatabaseEngine.exe but I hadn't restarted the computer. This time I restarted it. This is the ...


1

I would strongly recommend going the CSV route. Exporting from Excell to CSV is simply a matter of doing a "Save As...". Importing it into Access is a pretty simple matter as well. (If that link dies, there's many more like it on Google.)


1

I would use SSIS to do this. Try these steps: I would start out by creating an object variable to store your datasheets' names I would then create a VB task to pull out the names of your spread sheets and put the results into the object variable Then create a foreach loop that uses the object variable as the collection create another variable and toss in ...



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