CODEWITHSUNDEEP
sql-server-2008excelt-sqltextimport
Gierso
Gierso

Reputation: 39

how to import 2 row from txt or EXCEL into the same ROW in SQL server

I need to extract information from a page I have access to.

Just in this module, I have no means to export, but just to copying and pasting the information

Looks like this in the same l

1. MANUF   MODEL   YEAR MFG    SERIAL      REG     OS DATE     DESC        LISTED
1.                 YEAR DLV                    
2. monster 4r25    1988        23547248    Waka001 7/23/2012   For sale    7/22/2009
2.                 1989                 
3. FORD    12SE    1994        6262552     DBZRLZ0 7/26/2012   For sale    7/9/2009
3.                 1994

I'm getting my data in rows, but the year mfg and year dlv is in 2 rows within one row (or 2 rows in the same field). When pasted on excel it makes 2 rows first with all the data in the row including year mng and a second row just for year dlv (in the same column).

I can parse this information in excel by adding extra column and coping that extra field and deleting blanks and so on. But I want to omit the excel part and import this from a TXT file which when pasted creates 2 rows per row as well and using tabs as delimiter (like txt text tab delimited).

When I import with bulk insert, it imports twice as much rows, but I can't imagine a way to parse this second row into a new column.

Can someone help with this? In t-sql (every row has only one row of info, but in the column year mfg /year dlv, comes with two rows). Or point me on what to read or which would be a better approach? Maybe importing 2 rows at once ETC.

Upvotes: 1

Views: 2179

Answers (3)

Richard Vivian
Richard Vivian

Reputation: 1750

You can import the data set from the text file into a temp table including the blank lines. This will give you a data set in SQL with 2 types of records. 1. Records that have all data except delivery date. 2. Records that have only delivery dates and no other fields. (Add a unique auto increment key)

Because the related records will be one record apart, Record N and Records N+1 are actually the same record.

Then a select query Joining the temp table to its self by RecID = RecId+1 will give a complete record with all fields

SELECT * FROM tmpTable AS MainRecord
INNER JOIN tmpTable AS MissingField
ON MainRecord.RecId = MissingField.RecId +1

From this dataset you can instert into your main data.

Upvotes: 2

mehdi lotfi
mehdi lotfi

Reputation: 11571

you can use SQL Server Integration Service (SSIS) for convert data from any source data such as excel to any destination data such as SQL Server

enter image description here

Upvotes: -1

Joseph
Joseph

Reputation: 5160

Do you know how to use VBA? You can run this code (FixData()) in Excel before you use it in TSQL so it fixes the extra row problem. Hope this helps

Option Explicit

Public Sub FixData()
    Dim ws As Excel.Worksheet
    Dim iCurRow As Long
    Dim iLastRow As Long

    Set ws = ActiveSheet
    iLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row

    ' move through the spreadsheet from bottom to top
    For iCurRow = iLastRow To 1 Step -1
        If (isCurrentRowMessedUp(ws, iCurRow) = True) Then
            Call AppendDataToPreviousRow(ws, iCurRow)

            ' delete the row since we moved the data out of there
            ws.Rows(iCurRow).EntireRow.Delete
        End If
    Next
End Sub

Private Sub AppendDataToPreviousRow(ByRef ws As Excel.Worksheet, ByVal currentRow As Long)
    Dim firstCellInRow As Excel.Range
    Dim lastCellInRow As Excel.Range
    Dim previousRowRangeToPaste As Excel.Range

    ' check if first column has data in it, otherwise find the first column that has data
    If (ws.Cells(currentRow, 1).Value = vbNullString) Then
        Set firstCellInRow = ws.Cells(currentRow, 1).End(xlToRight)
    Else
        Set firstCellInRow = ws.Cells(currentRow, 1)
    End If

    Set lastCellInRow = ws.Cells(currentRow, ws.Columns.Count).End(xlToLeft)
    Set previousRowRangeToPaste = ws.Cells(currentRow - 1, getNextColumnAvailableInPreviousRow(ws, currentRow))

    ws.Range(firstCellInRow, lastCellInRow).Cut previousRowRangeToPaste
End Sub

Private Function isCurrentRowMessedUp(ByRef ws As Excel.Worksheet, ByVal currentRow As Long) As Boolean
    Dim cellCountInRow As Long
    Dim firstCellInRow As Excel.Range
    Dim lastCellInRow As Excel.Range

    Set firstCellInRow = ws.Cells(currentRow, 1)
    Set lastCellInRow = ws.Cells(currentRow, ws.Columns.Count).End(xlToLeft)

    cellCountInRow = Application.WorksheetFunction.CountA(ws.Range(firstCellInRow, lastCellInRow))

    If (cellCountInRow <= 1) Then
        isCurrentRowMessedUp = True
    Else
        isCurrentRowMessedUp = False
    End If
End Function

Private Function getLastColumnInPreviousRow(ByRef ws As Excel.Worksheet, ByVal currentRow As Long) As Long
    Dim rng As Excel.Range
    Set rng = ws.Cells(currentRow - 1, 1).End(xlToRight)

    getLastColumnInPreviousRow = rng.Column
End Function

Private Function getNextColumnAvailableInPreviousRow(ByRef ws As Excel.Worksheet, ByVal currentRow As Long) As Long
    getNextColumnAvailableInPreviousRow = getLastColumnInPreviousRow(ws, currentRow) + 1
End Function

Upvotes: 1

Related Questions