Import csv file and export in batches of 1000

This recent project assigned to datanology’s VBA team required a regular import of a standard csv file that was created from a web extract which then needed to be split into separate csv files (with headers) with a maximum of 1,000 records or rows. There was also a requirement to alter the order of the headings, rename the headings and also to concatenate (merge) two columns – one with the date, the other with the time.

Several parts were required to build this short project, and the logic was considered using the following:

  • Be able to choose which file to import – Set up an import button to open the dialog box in Excel so the user can choose the file.
  • Embed a count function within a loop that goes from the top of the imported data to the last row to ensure that a new file is created when 1,000 records are reached in the current output file.
  • Export the data from the current row in the import into the new schema, or order of the headings and at the same time concatenate the date and time columns in correct format (dd/mm/yyyy hh:mm:ss).
  • Save the newly created csv files in the same folder location as the Excel application.
  • Include an update commentary in the statusbar at the bottom left and end with a message box to confirm completion of the task.

The code below is a direct copy of the code used in the project, so feel free to copy, paste and amend as required for your project. This includes a UDF to get the file using a dialog box to only show csv files by default. You can send us a message using the contact form or leave a note on the comments below, we’d be happy to help you on any Excel project or specifically with the amendments to this project code.


Sub Import_and_split_into_1000_rows_csv()
‘Code by datanology.co.uk – November 2015

Call Get_File_CSV

Application.ScreenUpdating = False

myPath = ThisWorkbook.Path
tsnd = “1”
cnt = 0

With Worksheets(“Import”)
lastrow = .Cells(.Rows.Count, “A”).End(xlUp).Row
End With

x = 1

For n = 2 To lastrow
Application.StatusBar = “Exporting ” & n & “of ” & lastrow
Dim FileUser As String
Dim FileuserDocFILE As String
FileUser = ActiveWorkbook.Name
‘On Error Resume Next
FileuserDocFILE = myPath & “\” & x & “.csv” ‘name the .txt database to hold the info
‘On Error GoTo 0
‘This section lets you choose the fields to go into the .txt file
‘The Chr(9) allows a Tab to be included between the fields
Open FileuserDocFILE For Append As #1 ‘Use Output instead of APPEND to overwrite data

If cnt = 0 Then
Print #1, “NAME” & “,” & “DATE TIME” & “,” & “RED” & “,” & “BLUE” & “,” & “YELLOW” & “,” & “GREEN” & “,” & _
“BLACK” & “,” & “DIFFERENCE” & “,” & “A1” & “,” & “C1” & “,” & “D8” & “,” & “CH1” & “,” & “AN1” & “,” & “RES1”
End If

Print #1, Worksheets(“Import”).Range(“A” & n) & “,” & _
Format(Worksheets(“Import”).Range(“c” & n), “DD/MM/YYYY”) & ” ” & Format(Worksheets(“Import”).Range(“D” & n), “hh:mm:ss”) & “,” & _
Worksheets(“Import”).Range(“E” & n) & “,” & Worksheets(“Import”).Range(“F” & n) & “,” & Worksheets(“Import”).Range(“G” & n) & “,” & _
Worksheets(“Import”).Range(“H” & n) & “,” & Worksheets(“Import”).Range(“L” & n) & “,” & Worksheets(“Import”).Range(“K” & n) & “,” & _
Worksheets(“Import”).Range(“M” & n) & “,” & Worksheets(“Import”).Range(“O” & n) & “,” & Worksheets(“Import”).Range(“J” & n) & “,” & _
Worksheets(“Import”).Range(“Q” & n) & “,” & Worksheets(“Import”).Range(“P” & n) & “,” & Worksheets(“Import”).Range(“I” & n)
Close #1
cnt = cnt + 1

If cnt = 1000 Then
cnt = 0
x = x + 1
End If

Next n
Application.StatusBar = “Export completed”
MsgBox x & ” individual csv files generated.”
Application.StatusBar = False
End Sub


Sub Get_File_CSV()
Worksheets(“Import”).Select
With ActiveSheet.QueryTables.Add(Connection:= _
“TEXT;” & GetFile, Destination:=Range( _
“$A$1”))
.Name = “importCSV”
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(5, 2, 2, 2, 2, 2, 9, 9, 9, 9, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


Function GetFile() As String
Dim filename__path As Variant
filename__path = Application.GetOpenFilename(FileFilter:=”Csv (*.CSV), *.CSV”, Title:=”Select File To Be Opened”)
If filename__path = False Then Exit Function
GetFile = filename__path
End Function


Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

Leave a Reply

Your email address will not be published. Required fields are marked *