Export Data to CSV file

Question

We have more than 65,536 rows of data to export(write) in .csv file. But Excel(CSV) supports only 65,536. Excel supports multiple workbook, so we can write the data in multiple workbooks . But CSV doesn't support this feature too. Is there any other way to do this. Could any one help on this?

Answer 1

If you are targeting Excel, there are many libraries to help generate xls file insted of the CSV. On of them is CarlosAg.ExcelXmlWriter.dll that is easy to use.

Answer 2

You could write the data to multiple CSV-files, if that's possible. CSV is basically just a text file, so there is no stuff like multiple sheets etc.

Maybe you could use Excel files (xls) and multiple sheets. There exist libraries to write Excel files depending on the language you are using (e.g. Apache POI for Java).

Answer 3

The below was originally written for Excel2003 but when I migrated to 2007, COMDLG32 was not supported so annoyingly you just get a InputBox. I haven't used it for some time so it may need a bit of reworking, but should hopefully point you in the right direction.

Sub OpenCSV_bysheet()

'No COMDLG32.OCX

    Dim fileNo As Integer
    Dim tempRow, fileNm As String
    Dim tempRowNo, x, y As Long
    Dim CommaOnOff As Boolean

    fileNm = InputBox("Please input Drive:\Path\Filename.csv", , CurDir & "\*.csv")
    If fileNm = "" Then Exit Sub
    For x = 1 To Len(fileNm)
        If Mid(fileNm, x, 1) = "*" Or Mid(fileNm, x, 1) = "?" Then Exit Sub
    Next x

'    UserForm1.CommonDialog1.CancelError = True
'    UserForm1.CommonDialog1.Flags = cdlOFNHideReadOnly
'    UserForm1.CommonDialog1.Filter = "Comma Separated Value files (*.csv)|*.csv|All Files (*.*)|*.*"

    On Error GoTo errorTrap
'    UserForm1.CommonDialog1.ShowOpen

'    fileNm = UserForm1.CommonDialog1.Filename

    fileNo = FreeFile
    tempRowNo = 0
    x = 0
    y = 0

    On Error Resume Next
    Workbooks.Add (xlWBATWorksheet)
    Application.ScreenUpdating = False

    Open fileNm For Input As fileNo
        Do While Not EOF(fileNo)
            Line Input #fileNo, tempRow

            If x Mod 65536 = 0 And x > 0 Then
                Sheets.Add
                x = 0
            End If
            x = x + 1
            y = y + 1

            ActiveCell.Cells(x, 1).Value = tempRow

            ActiveCell.Cells(x, 1).TextToColumns Destination:=ActiveCell.Cells(x, 1), _
                DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
                Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False

            Application.StatusBar = y

        Loop
    Close fileNo

errorTrap:
    Application.ScreenUpdating = False
    Application.StatusBar = False
End Sub