Excel File Manipulation - Repeat First 7 Columns Based On Number Of Lines In Transaction
Jul 10, 2015
I have a task where I need to process roughly 60000 excel spreadsheets and bring them into a SQL Server 2014 database. All excel files have the same format and same number of identical columns. I have set up an SSIS package that is using Foreach Loop Container to look into a folder and process these files one at a time and load them to a table. The mappings are straight-forward, no problems there.
I am attaching a sample spreadsheet with two tabs - current structure and desired structure.
Basically what I need to do is to repeat the first 7 columns based on the number of lines in a transaction.
The number of lines is variable per patient.
View 6 Replies
ADVERTISEMENT
Mar 6, 2014
I have two columns which needs to repeat based on ID and number of distinct rows in that ID.
ID Date Created
1 1/1/2012 Sudheer
1 1/2/2013 Sudheer
1 3/3/2013 Sudheer
2 1/2/2014 Veera
2 2/5/2015 Veera
Results
ID Date Created Date Created Date Created
1 1/1/2012 Sudh 1/2/2013 Sudh 3/3/2013 Sudh
2 1/2/2014 Veera 2/5/2015 Veera
View 3 Replies
View Related
Jun 29, 2006
hi.i am using ms sql server 2000. can somebody tell me what the code would be to remove all the values in a given column and replace them with the associated number of the row with each execution. so, if i have a column:nums|1||2||3||4|and somebody deletes record |2|i would like the nums colum to update to|1||2||3|not:|1||3||4|it seems simple but i am having a hard time with this. how is it done?thanks.
View 14 Replies
View Related
Jan 7, 2008
Is there a way to control the way Reporting Services exports the report to Excel? The actual report being exported is filling all the columns and lines with blank into the sheet. The report has only 600 lines and 5 columns and the final Excel file is about 3 MB in size because is filling the 65,XXX lines allowed by Excel.
View 1 Replies
View Related
Aug 23, 2007
Hi there!
I need help on how to count the number of lines in a text file..
Pls. advise
thanks in advance
View 7 Replies
View Related
Nov 19, 2007
I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.
Thanks,
Manisha
View 4 Replies
View Related
Jul 14, 2014
I have a table that holds notes for item's. I'm want to do a select statement where one of my columns assigns a sequential value to each row based on the item number. Would like data to look like this where doc_no would be my row_number function:
item_no seq_no note doc_no
ABC 1 blah 1
ABC 2 blahh 1
ABC 3 bla3 1
XYZ 1 more n 2
XYZ 2 another 2
EFG 1 blahhh 3
View 2 Replies
View Related
Sep 11, 2015
We have a requirement to produce adhoc Excel reports with a standardized header page with a disclaimer attached. We want to be able to feed in a SQL Statement, or a table with the resultset from a SQL Statement and have SSIS populate an existing blank Excel workbook, which the disclaimer attached. The use of xp_cmdshell is not an option.I've spent a lot of time looking for solutions on the web and it seems though its not possible - although many articles are 3-5 years old. Before I throw in the towel, I just wanted to get feedback from this group if it still is not possible in the latest versions of SQLServer and SSIS, or to ask if there are any other 3rd party solutions that can do this today.
View 5 Replies
View Related
Apr 14, 2014
I need find out the number of columns in flat file before i process that particular file.I have file name in @filename variable and file path is @filepath variable.But do not not that how i will check the column name in before i will process that file.
@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles
And i am using for each loop container to read the file one by one and put the file name in @filename variable.and my file name like
Product_20120607060930.txt
Product_20130708060930.txt
[code]....
Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file.if it is not there then i have to send mail to client saying that file is not valid.I need to also calculate the size of flat file.
View 4 Replies
View Related
Apr 30, 2015
I have a query that gives me a result with a column value for example 4.
I now want to repeat this row 4 times with a new column that calculated from 1 - 4.
Or when column value is 3 I want to repeat row 3 times with new column name 1-3
View 7 Replies
View Related
Nov 4, 2014
I have one table,
Table1:
EventId EventName EventStartDate EventEndDate RepeatCount StartTimeEndTime
1Event1 2014-11-04 00:00:00.0002014-11-25 00:00:00.000 4 4:30 PM6:30 PM
2Event2 2014-11-05 00:00:00.0002014-11-12 00:00:00.000 2 3.00 AM4.00 AM
3Event3 2014-11-01 00:00:00.0002014-11-15 00:00:00.000 3 1.00 PM 2.00 PM
4Event4 2014-10-30 00:00:00.0002014-11-06 00:00:00.000 2 5.30 PM 6.30 PM
5Event5 2014-10-16 00:00:00.0002014-11-16 00:00:00.000 0 5.30 PM 6.30 PM
I want to display 2 weeks Events from Table 1 depends on repeat count
(if event scheduled on Tuesday(2014-11-04) and repeat count is 4 then it should repeat the 2 row of data)
We have to display only upcomming events, if the repeat count is 0 and the startdate and enddate falls by todays date then it should display as a single row.Data's Should be ordered by recent date
Expected Result
S.NoEventIdEventName EventDateTime
11Event1 2014-11-04 00:00:00.000
22Event2 2014-11-05 00:00:00.000
34Event4 2014-11-06 00:00:00.000
43Event3 2014-11-08 00:00:00.000
51Event1 2014-11-11 00:00:00.000
62Event2 2014-11-12 00:00:00.000
73Event3 2014-11-15 00:00:00.000
85Event5 2014-11-16 00:00:00.000
Assume today's date 2014-11-04 00:00:00.000 and time now 3.40 PM
View 8 Replies
View Related
Mar 5, 2015
I want to make a query with the result like this, where Seqno is the result query which has repeating sequence number for same Field1
Field1Field2SeqNo
BVFUSBVFUS011
BVFUSBVFUS021
BVFUSBVFUS031
BVFRPBVFRP012
BVFRPBVFRP022
[Code] ....
I want the result also no ordered by Field1. it just as the natural order of the table..
View 3 Replies
View Related
Oct 25, 2005
I am having some trouble creating a query that will preform some string manipulations on a field, and include this as part of the WHERE clause
For example I want to do something like this:
Code:
SELECTTable1.Column1,
Table1.Column2,
Table1.Column3,
Table2.Column1
FROMTable1
INNER JOIN Table2 ON Table1.UID = Table2.UID
WHERE(SET @Temp = Table2.Column1
--remove all 0's
SET @k = patindex('%[^0 ]%', @Temp)
WHILE @k> 0
BEGIN
SET @Temp = replace(@Temp, substring(@Temp, @k, 1), '')
SET @k= patindex('%[^0 ]%', @Temp)
END
SELECT @Temp
) = ''
But of course this isn't working so much. I am wondering if I have to use a cursor?
View 1 Replies
View Related
Jan 20, 2006
Please leave feedback for Microsoft regarding this problem at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126493
Ok I'm sure it just me, SSIS has been great so far....but how can you import a straight CSV file with and uneven column count.
For example: (assume CR LF row delimiter)
The,Quick,Brown,Fox,Jumps
Hello,World
This,is,a,test
"Normally" I'd expect this
| Col1 |
| Col2 |
| Col3 |
| Col4 |
| Col5 |
The
Quick
Brown
Fox
Jumps
Hello
World
NULL
NULL
NULL
This
is
a
test
NULL
Ok but what we get is the row delimiter is ignored in preference for the column delimiter and the row delimiter gets sucked into the column and the next row starts to get layed down.
So we get
| Col1 |
| Col2 |
| Col3 |
| Col4 |
| Col5 |
The
Quick
Brown
Fox
Jumps
Hello
World{CR}{LF}This
is
a
test
I'm I not seeing a tick box somewhere that says "over here if you want to terminate a row on the row delimiter even if all columns aren't full and we'll pad NULLs in rest of the row columns which you can fix in the flow transformations"
I'm sure it's there.....help!
(By the way SSIS team, great job on the package love using it)
View 64 Replies
View Related
Jan 20, 2007
I was wondering if you guys might give me some advice on how best to handle a particular scenario i'm struggling with.
I have a client that basically wants web-based-update access to their sql server database. Specifically, for a group of users to be able to access a page where they select a record for editing. the caveat is that no two users should be able to pull up the same record at the same time. Originally I would have thought there was some easier record-locking-mechanism I could exploit within sql server or ado.net itself, but I haven't been able to come up with anything.. so this is my current approach:
The page they use starts-out with basically a blank form. there are custom-built paging controls at the bottom of the screen. they click page-forward to begin and a stored procedure is ran to select a record and update a field on that record to indicate "in-process". when they finish editing the record - or page on to the next record without updating - another stored procedure is ran - updating/resetting the status field on the record appropriately.
The entire page is encapsulated within an ajax.net updatepanel.
The entire page has caching disabled. This works well in conjunction with the first page being blank. if they get out of the app and try to get back in by clicking the back button - all they can do is get to the first (blank) page.
A piece of javascript window.onunload clicks a button on the page that releases the record they currently have selected in the event of a re-direct, clicking back, etc.. it appears to work with everything except a window close. in that case, i have a stored procedure running periodically on the server that checks how long a record has been selected - and if it exceeds the time indicated - resets the record as to allow it to be re-selected later.
In the event of session timeout, they are redirected to another page that tells them their session has timed-out (and since the window.onunload fires - it takes care of releasing the record if they have one on the screen).
The concept seemed to be working well until I started multi-user testing. Now it seems as if two users time it perfectly - they are actually able to both select the same record. it happens pretty rarely, but it does seem to happen. I'm guessing this has to do with how my stored procedure is structured - possibly allowing a tiny-enough window between the record being selected for editing - and the update running to actually status the record as in-process (2 separate sql statements within the one stored procedure).
I believe I also have a found a second quirk in my approach where something is causing the window.onunload event to fire twice in some strange situations.. but that's more annoying/confusing from a logging standpoint than anything..
I've read where people say to ensure you dont update a record that's already been updated - that you should compare the fields before you actually perform the update and ensure they haven't changed since you selected it.. but to me that doesn't solve anything.. if two people select the same record and both spend time working on it - the person that tries to update last has just wasted their time.
I've also toyed with the idea of maintaining a separate table in the database to hold the keys to the currently selected records and use that to keep multiple people from selecting the same record - but honestly i dont know if that approach is any better than what i'm doing now.
anyway, I was just curious if you guys had any advice in regards to how you'd handle a request like this.. or if you see any obvious problems/fixes with my current approach..
I would greatly appreciate any info you could provide-
thanks-
View 3 Replies
View Related
Mar 7, 2008
I am using SSIS to export data from a table to an Excel spreadsheet. This all seems to work put just fine. The user would like a data in column B1 to say when the spreadsheet was created. Is there a way within SSIS to do this. I was looking at using a .NET script but it accesses the spreadsheet as a table so I do not know how to insert data above the headings in row 1. I believe the OleDB provider using column 1 as it column names for the table. Maybe I am just going about the whole think wrong?
Thanks
View 5 Replies
View Related
Dec 6, 2012
I am trying to update a SQL table using an excel file which has 2 columns FMStyle and FMHSNum.
FMStyle is my link to the SQL table.
Here is what I have for code....
--------------------------------------------------
Update DataTEST.dbo.zzxstylr
SET hs_num = (select FMHSNum FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c: empStyleHSCodesLoad.xls;HDR=YES ', [Sheet1$]))
Where FMStyle = zzxstylr.style
--------------------------------------------------
Everything seems to be ok except for the "Where FMStyle" is giving me a message Invalid Column name on FMStyle. Do I need to qualify FMStyle and if so how.
View 1 Replies
View Related
Jun 13, 2008
Hi everybody,
I'm sending text based e-mails using SMS and I need some lines to be bolded. I don't want to switch to HTML based just to make some lines bold. But, I cannot figure out how I can make the lines bold in SMS. I didn't see any options in SMS to bold a line of text. Is there any any function available for varchar datatype that will bold the text or something like that? or will I have to go to HTML based e-mail? any help is greatly appriciated.
devmetz
View 1 Replies
View Related
Jun 16, 2015
Here is a requirement. Need to update the columns in the tables with the latest values available in CSV.
The file is based on department so the list of tables which is under this department all the corresponding tables needs to updated.
The CSV file which is dynamic in nature the number of columns changes it has header of the columns that needs to be updated.
The destination tables are listed under department table for each department. So I have to update the columns in the tables with the values in csv.
View 4 Replies
View Related
Feb 26, 2007
I am writing a package that will process delimited flat files that will come in one of a few different versions. Within each flat file, the number of delimited columns will be the same, but each version of the file has a different number of columns. I have tried configuring the flat file data source to expect the version with the largest number of columns, but it will then throw away rows that have less than this number of columns (warning: There is a partial row at the end of the file).
Is it possible to use a single flat file data source that will work with all of the different width files?
View 1 Replies
View Related
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Nov 1, 2007
I am developing a report analog of a machine readable form that has to display a static number of detail rows regardless of the number returned from the database - i.e. if a record set has only three detail records, I need to display three blank rows, while if the record set has ten detail records, I need to display six detail records, print the footer, start a new page, repeat the header information, print the remaining 4 detail records and 2 blank rows, print the footer again and move on to the remaining recordset. I am new to report development and I'm having to pick it up on the fly. I can't seem to locate any documentation about how to handle this scenario.
I don't have the time or inclination to re-invent the wheel here, is there anyone who has solved this problem who can point me in the direction of some help?
View 2 Replies
View Related
Dec 29, 2006
Hello,
I'd need an help because I'm stucked!!
I have to import an Excel file into my DB.
The Excel file is made by 2 worksheets but I need only one and inside this worksheet I have to loop through the columns and for each column I define a Data Flow that trasform the data as necessary and then insert into the table.
I started with a "Foreach ADO.NET Schema Rowset Enumerator" with connection=excel file and the schema was set to "Columns" but the loop go also through the worksheet that I don't need..
after 4 hours of tries I'm lost...
Someone could give me an advice?
ThankX
Marina B.
View 14 Replies
View Related
Nov 11, 2014
I have an excel file which has dynamic columns
i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.
My problem is to Unpivot the data
Date 8/2/2013 8/9/2013 8/16/2013
Stock 1,561 1,661 1,761
i.e. the abobe table should become as
Date Stock
8/2/2013 1561
8/9/2013 1661
8/16/2013 1,761
How can I unpivot the dynamic columns given that the columns will keep on increasing every week.
View 1 Replies
View Related
Apr 4, 2007
Hi,I have an Excel file with 400 rows of old values and the correspondingnew values. My table currently has 10 columns out of which 3 columnsuse the old value specified in the excel file. I need to update thoseold values in the columns with the new values from the Excel file.Please guide me as to how to proceed with this.Thanks in advance!
View 4 Replies
View Related
Jul 9, 2007
Using ASP.NET 2, C#, Web application, we have an rdlc report which will be primarily used to export to an Excel spreadsheet. When we run thw report, then export it, we wind up with a spreadsheet with no gridlines, eg a spreadsheet with invisible cell borders.
Is the a setting or property somewhere which can be changed so that the spreadsheet which opens after the export looks like a standard spreadsheet, that is, with visible cell borders?
Many thanks
Mike Thomas
View 2 Replies
View Related
Aug 20, 2015
I am creating a report query that returns all unreconciled P/O lines. I am near completion but I am unable to find a way to remove the reconciled records.
I have included a script to produce some sample table, data & query.
The recordset dispalys 6 rows. All reconciled Supplier Invoices are duplicated and have transaction codes 40, 50 and reconcile code of 9 (5024, 921689471).
All unreconciled only appear once and have transaction codes 40 and reconcile code of 0 (4835 & 921978016). These are the only records that I want to show.
CREATE TABLE [dbo].[Purch_Ledger](
[EPDIVI] [nvarchar](3) NULL,
[EPSUNO] [nvarchar](10) NULL,
[EPSINO] [nvarchar](24) NULL,
[EPDUDT] [numeric](8, 0) NULL,
[EPTRCD] [numeric](2, 0) NULL,
[code].....
Whatever I try I cant find a way to get rid of the unwanted records.
View 3 Replies
View Related
Feb 6, 2007
Hi,
It would like that my report was in the following way, for example:
Product A
10 Itens 00001 00002 00003 00004 00005
00006 00007 00008 00009 00010
Product B
5 Itens 00020 00021 00022 00023 00024
This is possible?
Thank you
Sandro Borsatto
View 1 Replies
View Related
Apr 2, 2014
I have a situation where I want to load the Excel file dynamically, and the excel file have different columns or even worksheet name. How I could approach this? I believe there's no way to modify the meta data (specifically the mapping) in the data flow.
View 6 Replies
View Related
Jun 11, 2015
We are using a table that may give 1 to and unknown number of data elements (ie. years) . How can we break this to show only three years in each row. Since we don't know the number years we really won't know the number of rows needed. Years are stored in their own table by line.
car make year1 year2 year3
A volare 1995 1996 1997
a volare 1997 1998 1999
b toyat 1965 1966 1968
We can pivot out the first X# but we don't know how many lines so we don't know how many rows we will be creating.
View 8 Replies
View Related
Dec 1, 2015
I have this query:
SELECT
ID1,
ID2,
type,
(case when type = '1' then sum(value) else '0' end) as Value1,
(case when type = '3' then sum(value) else '0' end) as Value2,
(case when type <> '1' and type <> '3' then sum(value) else '0' end) as Value3
FROM table1 WHERE ID1 = 'x' and ID2= 'y'
GROUP BY ID1, ID2, Type
Which returns:
ID1 ID2 Type Value1 Value2 Value3
005 11547 0 0.00 0.00 279.23
005 11547 1 15.23 0.00 0.00
005 11547 3 0.00 245.50 0.00
And I want to obtain this result:
ID1 ID2 Value1 Value2 Value3
005 11547 15.23 245.50 279.23
View 5 Replies
View Related
Oct 28, 2004
I am working on an application which uses DTS to move data into temporary tables. I would like to be able to rename/relocate the source file in order to maintain a historical reference. The process which creates the source file is not flexible at all. Is there a way to manipulate the file's name and/or relocate the file by using SQL Server.
Thanks in advance!
Daniel
Austin, Texas
View 4 Replies
View Related
Apr 29, 2007
Does anyone know the best way to handle this type of situation?
A file coming into a directory based on the date filename042707
I use the fileexist stored procedure to check for the existence.
I use xp_cmdshell(sp)... stored procedure to rename the file so that
it just has filename instead of the date.... I can not use
variables within the xp_cmdshell to replace the date...
everyday the filename would change to filename042807,filename042907 etc....
Basically I know how to copy the file to another directory if it exists...
then I want to strip the right 8 characters off... rename just to that
but with the rename i have to know what the file will be named for that day ...
excuse the grammar just somewhat tired...
any suggestions... please thanks time for sleep
View 2 Replies
View Related
