Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 I want text quoted and then the value of a cell followi | Excel Worksheet Functions | |||
Adding comment text - linefeed visible - why? | Excel Programming | |||
How to produce comma-delimited and quoted text file from Excel? | Excel Discussion (Misc queries) | |||
Linking Embedded Document Fields | Excel Discussion (Misc queries) | |||
Sorting embedded fields - control box | Excel Programming |