Excel - TAB separated, quote encapsulated TXT file

[CatPlus]

Hello all:

There are two types of files that Excel can import, FILE.CSV and FILE.TXT

CSV files are relatively easier to handle because no wizard get into play in opening the excel worksheet. However, that causes problems when cells have numeric values that are treated as number, like zip code 08648 would result 8648

This would be a major disaster if file is a mailing list

TXT files invokes Excel wizard with TAB delimitor and the
QUOTE symbol text qualifier.... two questions:

[1] What is the symbol or ESC CODE that represents TAB which can replace the , (COMMA SYSMBOL) in the example below
The wizard would then default to TAB and you click NEXT!
[2] Why QUOTE encapsulated cell is brought in as NUMBER instead of a NUMERIC STRING. If you dont highlight the field and check TEXT, value would be imported as NUMBER!

I have posted this question before but got inconclusive responses

"NAME","STREET","CITY","STATE","08648"

 

[SkipVought]

Hi,
In the Text Wizard, you specify that the ZIP field is to be imported as TEXT. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

The other option is to use a custom format of 00000 for the ZIP column Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[CatPlus]

Thanks you Skip for your reply!

My understanding of a CSV or TXT file is when a cell value is encapsulated within quotes, then it is treated as text and not number. If ZIP is "08648" then the cell value must read 08648 and not 8648.

If file is CSV, it does not invoke the wizard. If file is TXT then wizard is invoked.

If wizard gets invoked, the delimitor defaults to TAB even though the delimitor is , (comma). To minimize errors in selecting default values prompted by the wizard, I would like to actually have TAB as my default delimitor instead of the comma delimitor. What key replicates TAB? I have used the TAB key but thats not recognized by Excel when importing

Please help!

 

[Gavona]

In Excel 2000 I opened a text file and specified tab de-limiters - all with the macro recorder on. This is the code that was generated:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 30/03/2003 by gavona
'
    Workbooks.OpenText _
        Filename:="\\VS-AY012\xxx\dirss.txt", _
        Origin:=xlWindows, StartRow:=1, _
        DataType:=xlDelimited, _
        TextQualifier:= xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, _
        Comma:=False, Space:=False, _
        Other:=False, FieldInfo:=Array(1, 1)
End Sub

For me the import wizard defaulted to Tab but in any case the wizard allows selection of the delimiter to use.


hope this helps,

Gavona