How to ignore embedded linefeed in CSV quoted text fields?
 

I have a CSV file where the text elements in quotes have embedded linefeeds

123,"12/08/2009","is a new{LF}line",""

where {LF} is an actual linefeed, char(10)

I can open it directly in Excel 2007, which ignores these line breaks.
However, the US m/d/y dates come out incorrectly because my locale uses d/m/y

But if I open it in VBA with a Text Query (to parse the m/d/y date formats)
it does not import into Excel correctly. The text columns have linefeeds
inserted that force a newline so the line is correct up to the LF then the
record continues on the next row, eg

123 08.12.2009 is a new
line

Is there some Querytable parameter I can use to tell Excel to ignore
linefeeds?


TIA

Patrick


<pre
Set qt = ActiveSheet.QueryTables.Add(Connection:="TEXT;" & sFileName,
Destination:=ActiveSheet.Range("A1"))
With qt
.Name = sName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001 ' Unicode UTF-8, not Windows ANSI = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = aDataTypes ' eg Array(1, 1, 1, 1, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
DoEvents: qt.Delete ' remove querytable link when done importing

</pre

How to ignore embedded linefeed in CSV quoted text fields?
 

I can open it directly in Excel 2007, which ignores these line breaks.
However, the US m/d/y dates come out incorrectly because my locale uses d/m/y

You can use the 'Convert Text to Columns Wizard to convert the dates

--Select the range of dates which needs to be corrected.
--From menu Data'Text to Columns' will populate the 'Convert Text to Columns
Wizard'.
--Hit NextNext will take you to Step 3 of 3 of the Wizard.
--From 'Column Data format' select 'Date' and select the date format in which
your data is 'MDY'.
--Hit Finish. MSExcel will now convert the dates to the default date format
of your computer.

--
Jacob


"SysMod" wrote:

I have a CSV file where the text elements in quotes have embedded linefeeds

123,"12/08/2009","is a new{LF}line",""

where {LF} is an actual linefeed, char(10)

I can open it directly in Excel 2007, which ignores these line breaks.
However, the US m/d/y dates come out incorrectly because my locale uses d/m/y

But if I open it in VBA with a Text Query (to parse the m/d/y date formats)
it does not import into Excel correctly. The text columns have linefeeds
inserted that force a newline so the line is correct up to the LF then the
record continues on the next row, eg

123 08.12.2009 is a new
line

Is there some Querytable parameter I can use to tell Excel to ignore
linefeeds?


TIA

Patrick


<pre
Set qt = ActiveSheet.QueryTables.Add(Connection:="TEXT;" & sFileName,
Destination:=ActiveSheet.Range("A1"))
With qt
.Name = sName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001 ' Unicode UTF-8, not Windows ANSI = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = aDataTypes ' eg Array(1, 1, 1, 1, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
DoEvents: qt.Delete ' remove querytable link when done importing

</pre