December 4, 2015 at 7:04 am
I have a spreadsheet that comes in the same format every month. The source, unfortunately, puts two pieces of information on top of each other. So....Looking at the attachment, you'll see a column called Grantor. Next to that is a column called Instrument. Prior to importing into SSIS/SSMS, I need to copy that number down 1 row (or 3, or 10, or whatever) as the Instrument Number is the Key for all the information on the line it's on, and all the lines below it until it finds another Instrument number. Make sense? I've never played with C# but my old boss would have build this into a scripting task.
I am certain this can be done, but I'm not sure how yet. Any help is GREATLY appreciated.
December 4, 2015 at 11:29 am
if you want to pre-prep in Excel, then just use
=IF(ISBLANK(F10), G9, F10)
for Column G, starting in row 10. Then just copy down to all rows in dataset.
If you want an SSIS approach, can be done, basically the same premise, but loading the 'last known good' valu eto a variable and then using this until you hit the next value.
Steve.
December 4, 2015 at 11:47 am
The idea is that I get these files on a weekly basis. I could spend 10 minutes fixing the spreadsheet, or I could code a solution and never touch it again. Unfortunately, I don't know how to code in C#.. I'm learning, but don't have enough time to learn fast enough.
December 4, 2015 at 11:57 am
i've done this with excel automation, which you can do in a script task,
this is a rough example i modified, but I did not explicitly test against your file.
basically, i'm looping through the Excel.Range, and keeping track of the previous value, and assigning it .
i think you have to assign the
you need using Microsoft.Office.Interop.Excel, with a reference in the SSIS to excel
even worse, i ran my old vb code through a c# converter, so it might have errors
private string ExcelFileToString_ThreeColumns(string Excelfilename)
{
Excel.Application xlApp = default(Excel.Application);
Excel.Workbook xlWorkBook = default(Excel.Workbook);
Excel.Worksheet xlWorkSheet = default(Excel.Worksheet);
Excel.Range range = default(Excel.Range);
object Obj = null;
string Results = string.Empty;
try {
if (!File.Exists(Excelfilename)) {
Interaction.MsgBox("The Path to the Esxcel File is Invalid!", , "Cannot Continue");
return string.Empty;
}
xlApp = new Excel.Application[];
xlWorkBook = xlApp.Workbooks.Open(Excelfilename);
xlWorkSheet = xlWorkBook.Worksheets(1);
//--Not sure of the name! 1-based index, not zero
range = xlWorkSheet.UsedRange;
string Ax = null;
string Bx = null;
string Cx = null;
string PreviousValue = null;
//starting at TWO, not one, since the first row should have header labels!
for (i = 2; i <= range.Rows.Count; i++) {
Obj = ((Excel.Range)range.Cells(i, 1));
Ax = GetString(Obj.value);
if (Ax == string.Empty) {
Ax = PreviousValue;
Obj.value = Ax;
} else if (Ax != string.Empty) {
PreviousValue = Ax;
}
//Obj = (CType(range.Cells(i, 2), Excel.Range))
//Bx = GetString(Obj.value)
//Obj = (CType(range.Cells(i, 3), Excel.Range))
//Cx = GetString(Obj.value)
//Results = Results & Ax & " " & Bx & ":" & Cx & "; "
}
xlWorkBook.Close();
xlApp.Quit();
PostMessage(xlApp.Hwnd, WM_QUIT, 0, 0);
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
Pause();
System.Diagnostics.Process proc = default(System.Diagnostics.Process);
foreach ( proc in System.Diagnostics.Process.GetProcessesByName("EXCEL")) {
proc.Kill();
}
} catch (Exception ex2) {
try {
PostMessage(xlApp.Hwnd, WM_QUIT, 0, 0);
xlWorkBook.Close();
xlApp.Quit();
} catch (Exception ex) {
}
try {
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
} catch (Exception ex) {
}
} finally {
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
}
return Results;
}
private string GetString(object objValue)
{
if (objValue == null || Convert.IsDBNull(objValue)) {
return "";
} else {
return Convert.ToString(objValue);
}
}
private void releaseObject(object obj)
{
try {
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
} catch (Exception ex) {
obj = null;
} finally {
GC.Collect();
}
}
//=======================================================
//Service provided by Telerik (www.telerik.com)
//Conversion powered by NRefactory.
//Twitter: @telerik
//Facebook: facebook.com/telerik
//=======================================================
'Imports Excel = Microsoft.Office.Interop.Excel
Private Function ExcelFileToString_ThreeColumns(ByVal Excelfilename As String) As String
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim range As Excel.Range
Dim Obj As Object
Dim Results As String = String.Empty
Try
If Not File.Exists(Excelfilename) Then
MsgBox("The Path to the Esxcel File is Invalid!", , "Cannot Continue")
Return String.Empty
End If
xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Open(Excelfilename)
xlWorkSheet = xlWorkBook.Worksheets(1) '--Not sure of the name! 1-based index, not zero
range = xlWorkSheet.UsedRange
Dim Ax, Bx, Cx As String
Dim PreviousValue As String
For i = 2 To range.Rows.Count 'starting at TWO, not one, since the first row should have header labels!
Obj = (CType(range.Cells(i, 1), Excel.Range))
Ax = GetString(Obj.value)
If Ax = String.Empty Then
Ax = PreviousValue
Obj.Value = Ax
ElseIf Ax <> String.Empty Then
PreviousValue = Ax
End If
'Obj = (CType(range.Cells(i, 2), Excel.Range))
'Bx = GetString(Obj.value)
'Obj = (CType(range.Cells(i, 3), Excel.Range))
'Cx = GetString(Obj.value)
'Results = Results & Ax & " " & Bx & ":" & Cx & "; "
Next
xlWorkBook.Close()
xlApp.Quit()
PostMessage(xlApp.Hwnd, WM_QUIT, 0, 0)
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
Pause()
Dim proc As System.Diagnostics.Process
For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
proc.Kill()
Next
Catch ex2 As Exception
Try
PostMessage(xlApp.Hwnd, WM_QUIT, 0, 0)
xlWorkBook.Close()
xlApp.Quit()
Catch ex As Exception
End Try
Try
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
Catch ex As Exception
End Try
Finally
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
End Try
Return Results
End Function
Private Function GetString(ByVal objValue As Object) As String
If objValue Is Nothing OrElse Convert.IsDBNull(objValue) Then
Return ""
Else
Return Convert.ToString(objValue)
End If
End Function
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
Lowell
December 4, 2015 at 12:56 pm
I KNEW someone has done this before.. Now I don't have to look like a complete idiot to one of my subordinates and go ask them for help. :w00t:
Now I can just ask them to help me debug the code... LOL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply