As a routine for go live preparation, you always have a lot of master data to uploaded on the production database. And when you’re using Data Transfer Workbench (DTW), sometimes it will be an error if you use files that have more than 10000 rows on one text files (.txt)
So, you need to split your excel files that contains you raw data.
We can use Macro feature on Microsoft Excel.
On View tab, click on icon Macros.
Then, input the Macros name, it’s up to you.
After that, just click OK button. Then, open your split query.txt, copy-paste your query.
Sub split()
Dim rLastCell As Range
Dim rCells As Range
Dim strName As String
Dim lLoop As Long, lCopy As Long
Dim wbNew As Workbook
Dim rowloop As Long
Dim sheetname As String
rowloop = 4999
sheetname = ThisWorkbook.Sheets(1).Name
With ThisWorkbook.Sheets(1)
Set rLastCell = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
For lLoop = 2 To rLastCell.Row Step rowloop
lCopy = lCopy + 1
Set wbNew = Workbooks.Add
wbNew.Sheets(1).Name = sheetname
.Range(.Cells(1, 1), .Cells(1, .Columns.Count)).EntireRow.Copy _
Destination:=wbNew.Sheets(1).Range("A1")
.Range(.Cells(lLoop, 1), .Cells(lLoop + rowloop, .Columns.Count)).EntireRow.Copy _
Destination:=wbNew.Sheets(1).Range("A2")
wbNew.SaveAs Filename:=Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5) & "_" & lCopy & ".xls", FileFormat:=56
wbNew.Close SaveChanges:=False
lLoop = lLoop + 1
Next lLoop
End With
End Sub (Or, you can save as to .txt that query first, so you can use it later)
Open your excel document again, then press Alt + F8 to show a Macro menu.
Click Edit button, then paste your split query into the field.
Paste it to there, so it will look like this:
Set your own preferences for rowloop. On the picture above, we set a rowloop for 4999, so the final document will split into 5000 rows (max). You can change it by yourself.
Then, click Run icon (see picture below), and your document will be split into a separated file (base on your choice on rowloop).
And you can easily change the final document type, either it’s .doc (Ms. Word) or .txt (Text file). After that, you can click the Run button to execute this code for splitting the document again.
Finally, you can preview your split document on the same directory with your previous ‘raw’ data.
Also read:
