Hot answers tagged

8

Unless the data you are importing is very simplistic using SSMS (Sql Server Management Studio) tends to produce a lot of errors and not a lot of actual data. I find that if my spreadsheet (and/or csv, txt file) is very large or has large text fields, or date/time fields I almost always end up using SQL Server Integration Services (SSIS). The first time you ...


7

I see this question is tagged both Excel and SQL Server. In Excel you could use the approach from here The formula in cell F2 above is =IF(SUM(IF(FREQUENCY(IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""), IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""))>0,1))>1,"MisMatch","Match") Pay attention to the note in the linked article The formulas in this ...


6

In SMSS you can right click a database and select Tasks -> Import data.


5

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


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

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.


4

The SQL Server Import and Export Wizard offers the simplest method to create a Integration Services package that copies data from a source to a destination. This will create a package for you, which you can schedule via SQL agent job as explained in Scheduling SSIS Package Execution in SQL Server Agent, as per the desired refresh frequency. Also, refer to ...


4

While you could schedule an JET OLEDB type query as a SQL Agent job with something like the code below: SELECT * FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\PathToExcelFile\ExcelFile.xls;Extended Properties=Excel 8.0')...[Sheet1$] I would only recommend this approach if you have not, can not or do not want ...


4

The process you describe is very extremely techinically ETL, though if I were reviewing qualifications with a candidate during an interview for a position that required what I usually think of as ETL experience and they described the process you mention as the "ETL" process they were familiar with, it's very likely that the interview would end politely and ...


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

When you do Export data from the database (using the wizard - Import\Export) select -- write a query to specify data transfer. If you are creating package from scratch using BIDS, then in the execute sql task editor, in the sql statement then in the data flow task in the source query specify T-SQL query as below :


3

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


3

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


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

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

Things you are likely to need to do include, preferably before you move any data: Normalize Add constraints (keys, domain checking, referential integrity, validation, etc). Both are fairly huge topics :)


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.


3

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


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

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


3

If you do not have access to create Directory, then you can use SQL Loader to get data into the database (provided you have Oracle client installed on your system). An outline of the steps is provided below: a.) Create a table with the same structure as your excel file b.) Convert your Excel File to .csv format file c.) Create a Loader Control File. Refer ...


3

Excel specifically provides a message: Dates and times that are negative or too large are displayed as ######. So, that is a Excel limit. Further, you can type in strings that look like dates, but Excel may not think of them as dates. For example, if I make a column of date type and enter into the columns -53689, 01/01/1753, and 01/01/1900 (using my ...


3

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


3

For 1-time: Save as text (csv) file. CREATE TABLE ... with the columns approximating the Excel columns. LOAD DATA LOCAL FILE ... into that table in MySQL. (If you need 'automation', there may be better ways.)


2

See the mydata.csv file at bottom of the answer. I created a table xy CREATE TABLE `xy` ( `fred` int(11) DEFAULT NULL, `mary` int(11) DEFAULT NULL, `billy` varchar(50) DEFAULT NULL ); From the documentation here, I tried this LOAD DATA INFILE 'mydata.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ...


2

You can put your cube formula in a separate cell and reference the cell where users type in the name they want. For example, if you have users put the branch name in cell B2, you can put this formula in another cell. =cubemember("Connection","[DimBranch].[Name].[All].[" & B2 & "])"


2

Seems like more often than not I run into issues trying to export to excel using the SSMS import/export wizard and I've never taken the time to learn and understand the quirks. This doesn't necessarily answer your question, but might be a viable alternative. Instead of exporting to excel, consider importing into excel. This YouTube video appears to ...


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

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

What you're describing is known as "Excel spreadsheet hell". Your scenario sounds like a disaster - constant firefighting, messy data... the list goes on. I'm not sure what sort of data volumes you're dealing with. Oracle's solution to this is Apex. It's potentially a lot more than that, but take a look here. If your volumes are not too large, you could ...



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