Is there a way to copy a specific line in a tab delimited text file to an excel file without converting the entire text file to excel? The text file is big and the conversion to excel simply takes too long.
Thanks for the link. I read through that, however, I admit that I don't have enough vba knowledge and skill to start from there to achieve my goal to copy the line in the text file (with tab delimiter) to excel's multiple cells. Would you please help me further? Thanks!
I would need to know how you determine the record to be imported from the csv.
Also, is there a rough area where it is guaranteed to appear in the csv. If this is known we can save time by passing over lines that we know aren't the one we want.
It's a .txt file not a csv file. the fields in the text file are delimited by tab. It has about 4,000 lines. the format for each line is like this : total 9 fields
If I use a split function to split fields line by line and paste each of the field to excel cells (each text line will be copied to one row in excel, and one field in one cell), it will take very long time.
I am hoping to write an access vba public function to get only the line I specify and convert that line to excel without converting the entire text file to excel , and then copy the row to another excel.
Public Function copyline (ByVal TextFileName As String, ByVal delim1 As String, ByVal lineNo As Integer, ByVal ExcelFileName As String)
where TextFileName is the text file where I want to get the data line, delim1 is the delimiter, which will be tab, lineNo is the line number in the text file I specify to get the data, and ExcelFileName will be the new excel file name I saved for the converted text line.
for example:
copyline ("c:\temp\test.txt", "char(9)", 3600, "c:\temp\test.xlsx")
will get the line 3600 in c:\temp\test.txt, with tab delimiter for each field, convert line 3600 to c:\temp\test.xlsx. So c:\temp\test.xlsx only has one row with 9 cells.
Traditionally csv is "comma separated value" but the name is often used with other separators and sometimes called "character separated values". The principles are the same anyway.
You were heading in the right direction. Here is a function that will get you a lot further.
Errors are not handled. It does not test for the text file not existing, Excel file not existing or already open. Values exceeding the maximum length of Excel values are not managed.
It writes the line into the first row of the first sheet of the Workbook. Will overwrite any existing data.
Code:
Public Function CopyLine(ByVal TextFileName As String, _
ByVal Delim As String, ByVal LineNo As Integer, _
ByVal ExcelFileName As String) As Long
Const ForReading = 1
'Return Codes
Const Success = -1
Const InvalidLineNo = 0
' Returns LineCount of file if LineNo > LineCount
Dim fso As Object
Dim TargetFile As Object
Dim FileLine As String
Dim XLApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim SkipLines As Long
Dim s As Long
Dim SplitArray As Variant
Dim SplitLB As Integer
Dim SplitCount As Integer
Dim n As Integer
Dim m As Integer
SkipLines = LineNo - 1
If SkipLines < 0 Then
CopyLine = InvalidLineNo
Else
Set fso = CreateObject("Scripting.FileSystemObject")
Set TargetFile = fso.OpenTextFile(TextFileName, ForReading)
Do While s < SkipLines And Not TargetFile.AtEndOfStream
TargetFile.SkipLine
s = s + 1
Loop
If Not TargetFile.AtEndOfStream Then
FileLine = TargetFile.readline
SplitArray = Split(FileLine, Delim)
SplitLB = LBound(SplitArray)
SplitCount = UBound(SplitArray) - SplitLB + 1
Set XLApp = New Excel.Application
Set XLBook = XLApp.Workbooks.Open(ExcelFileName)
Set XLSheet = XLBook.Worksheets(1)
m = SplitLB
For n = 1 To SplitCount
XLSheet.Cells(1, n).Value = SplitArray(m)
m = m + 1
Next
With XLBook
.Save
.Close
End With
XLApp.Quit
Set XLSheet = Nothing
Set XLBook = Nothing
Set XLApp = Nothing
CopyLine = Success
Else
CopyLine = s
End If
Set TargetFile = Nothing
Set fso = Nothing
End If
End Function
I can't express my gratitude enough. The code is too good to be true. It works extremely well. I will need to study further to fully understand your codes.
Traditionally csv is "comma separated value" but the name is often used with other separators and sometimes called "character separated values". The principles are the same anyway.
You were heading in the right direction. Here is a function that will get you a lot further.
Errors are not handled. It does not test for the text file not existing, Excel file not existing or already open. Values exceeding the maximum length of Excel values are not managed.
It writes the line into the first row of the first sheet of the Workbook. Will overwrite any existing data.
Code:
Public Function CopyLine(ByVal TextFileName As String, _
ByVal Delim As String, ByVal LineNo As Integer, _
ByVal ExcelFileName As String) As Long
Const ForReading = 1
'Return Codes
Const Success = -1
Const InvalidLineNo = 0
' Returns LineCount of file if LineNo > LineCount
Dim fso As Object
Dim TargetFile As Object
Dim FileLine As String
Dim XLApp As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim SkipLines As Long
Dim s As Long
Dim SplitArray As Variant
Dim SplitLB As Integer
Dim SplitCount As Integer
Dim n As Integer
Dim m As Integer
SkipLines = LineNo - 1
If SkipLines < 0 Then
CopyLine = InvalidLineNo
Else
Set fso = CreateObject("Scripting.FileSystemObject")
Set TargetFile = fso.OpenTextFile(TextFileName, ForReading)
Do While s < SkipLines And Not TargetFile.AtEndOfStream
TargetFile.SkipLine
s = s + 1
Loop
If Not TargetFile.AtEndOfStream Then
FileLine = TargetFile.readline
SplitArray = Split(FileLine, Delim)
SplitLB = LBound(SplitArray)
SplitCount = UBound(SplitArray) - SplitLB + 1
Set XLApp = New Excel.Application
Set XLBook = XLApp.Workbooks.Open(ExcelFileName)
Set XLSheet = XLBook.Worksheets(1)
m = SplitLB
For n = 1 To SplitCount
XLSheet.Cells(1, n).Value = SplitArray(m)
m = m + 1
Next
With XLBook
.Save
.Close
End With
XLApp.Quit
Set XLSheet = Nothing
Set XLBook = Nothing
Set XLApp = Nothing
CopyLine = Success
Else
CopyLine = s
End If
Set TargetFile = Nothing
Set fso = Nothing
End If
End Function
