Importing Tab Delimited Text File with Multiline Cells (to Excel or Access)
The tab delimited text file that i am trying to import looks like this (4 fields, Field 3 is multiline)... and about 100,000 rows of data:
Below is just a sample... but please give a general solution where the multiline text could be in multiple fields... like say fld3, fld6 and fld7 of a total 10 fields.
Field 1 <tab> Field 2 <tab> Field 3.1
Field 3.2
Field 3.3<tab>Field 4
Field 1 <tab> Field 2 <tab> Field 3.1
Field 3.2
Field 3.3<tab>Field 4
Field 1 <tab> Field 2 <tab> Field 3.1
Field 3.2
Field 3.3<tab>Field 4
But, actually when it was was exported to the above text file from the database... it had 3 lines of actual... that looked like this... 4 Fields. Field 3 is multiline.
Field 1 <tab> Field 2 <tab> Field 3.1(CR)Field 3.2(CR)Field 3.3<tab>Field 4
Field 1 <tab> Field 2 <tab> Field 3.1(CR)Field 3.2(CR)Field 3.3<tab>Field 4
Field 1 <tab> Field 2 <tab> Field 3.1(CR)Field 3.2(CR)Field 3.3<tab>Field 4
where is Tab, and (CR) is carriage return. When importing the tab delimited text file into Excel, i want all 3 lines in Field 3 (Field 3.1, Field 3.2 and Field 3.3) be in one cell, but multiline. So basically i want excel to ignore carriage return with in Field 3. How do i do that???
For your info... The text fields are NOT in double quotes... Please tell me what is the best way to convert the plain text tab delimited text file to Excel 4 columns like this:
does anyone know of a solution. it would be greatly appreciated.
Additional Comment
Please note that what I have is only the resulting Text File... as shown on the very top of this message.... with text "Field 3.2" in Line 2, "Field 3.3" and "Field 4" in Line 3 with separating them.
Also... not necessarily Field 3 will be always multiline... it may be multiline with 2, 3 or more lines... or even nothing or single line. Field 3 will never have a with in itself.
Additional Comments with Sample File for someone to Test - Update 25Jun2013 09:05 UTC
pls note copy paste will not work.. as tabs are lost.. you will have to add it yourselves.
File with Quotes around the multiline field:
f1hdr f2hdr f3hdr
f11 "f12
part of f12" f13
f21 "f22part of f22
part f22
part f22
part f22" f23
f31 "f32aaa" f33
f41 "f42bbb" f43
File without Quotes around the multiline field:
f1hdr f2hdr f3hdr
f11 f12
part of f12 f13
f21 f22part of f22
part f22
part f22
part f22 f23
f31 f32aaa f33
f41 f42bbb f43
Open the file with quotes in Excel... Open File Dialog... Hold Shift... and Click Open.. show the file nicely with multiline.
But opening the file without quotes.. doesn't work.. it breaks.
I have no idea why holding shift works. I knew of this from here: Import multiline csv files into Excel internationally
Now.. it still remains to be answered...
1) How to add the quotes around the text file in easier fashion before importing to Excel... Why Shift Open works? What if I want to control each delimited columns using text import wizard?
2) How to add the quotes around the fields by default in SQL Server 2015. This is in addition to above Q1. We still need a solution where we can't export the file again. But, for any new export.. someone could user the answer from Q2.
3) Any other methods which may be even simpler?
Answer
Here's what I came up with. If you can guarantee the last column will never have a carriage return, then this should work ok.
What this does is read the text file in VBA and brings it into the workbook. You have to specify how many fields to expect (in the test scenario, 4). This is so it can keep track of when it's ready to start a new row.
It's a little confusing because of the carriage returns, but step through the code and I think you'll be able to figure it out. Let me know if you have any questions.
Option Explicit
Const fieldCount = 4
Sub import()
Application.ScreenUpdating = False
Dim fileNumber As Integer
Dim data As String
Dim curCol As Long, curRow As Long
Dim dataCols As Long
Dim i As Long
Dim sh As Excel.Worksheet
Dim arr() As String
Dim hasCarriageReturn As Boolean
fileNumber = FreeFile()
Open "C:\test.txt" For Input As #fileNumber
curCol = 1
curRow = 1
Set sh = ThisWorkbook.Worksheets("Sheet1")
While Not EOF(fileNumber)
' if we reached the "correct" last column, then move to next row
If (curCol > fieldCount) Then
curCol = 1
curRow = curRow + 1
hasCarriageReturn = False
End If
Line Input #fileNumber, data
arr = Split(data, vbTab)
dataCols = UBound(arr)
If (dataCols = fieldCount - 1) Then
' full row has no carriage returns
hasCarriageReturn = False
For i = 0 To dataCols
sh.Cells(curRow, curCol).Value = arr(i)
curCol = curCol + 1
Next
ElseIf (dataCols = 0 And hasCarriageReturn = True) Then
' if there is only 1 value in the row, append it to the current column
sh.Cells(curRow, curCol - 1).Formula = sh.Cells(curRow, curCol - 1).Formula & Chr(10) & arr(0)
ElseIf (dataCols = 0 And hasCarriageReturn = False) Then
' carriage return begins in the first field
sh.Cells(curRow, curCol).Formula = sh.Cells(curRow, curCol).Formula & Chr(10) & arr(0)
curCol = curCol + 1
hasCarriageReturn = True
ElseIf (hasCarriageReturn) Then
' append first item to field 3, then rest goes in other columns
sh.Cells(curRow, curCol - 1).Formula = sh.Cells(curRow, curCol - 1).Formula & Chr(10) & arr(0)
For i = 1 To dataCols
sh.Cells(curRow, curCol).Value = arr(i)
curCol = curCol + 1
Next
hasCarriageReturn = False
Else
' process row and note that it has carriage returns
For i = 0 To dataCols
sh.Cells(curRow, curCol).Value = arr(i)
curCol = curCol + 1
Next
hasCarriageReturn = True
End If
Wend
Application.ScreenUpdating = True
End Sub