Export Data to CSV file
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 othe开发者_运维百科r way to do this. Could any one help on this?
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).
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.
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
精彩评论