Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

How to change *defaults* for importing Tab-delimited text file into Excel2002 -

How to I change the default setting when importing a .txt file?

I spend a lot of time converting Excel (Office 2002) files into HTML.
The quickest way to clear off all the Microsoft formatting crud seems
to be to save as a tab delimited .TXT file.

This works well but it is extremely irritating that having saved as
"tab delimited text" from Excel, that the same piece of software cant
recognise its own file when you reopen it! It seems to default to
Delimited and file origin
"932: Japanese (Shift-JIS)" whereas what I want is file origin of:
"1252 : Western European (Window)". However this requires clicking on
the drop-down list (which always takes a while to fill up for some
reason) and the scrolling to the bottom of the list...!

How can I get Excel to recognise the file-type of what it has just
saved?!

Ship


Post your answer or comment

comments powered by Disqus
Is there a way to import into Excel a tab delimited text file but skip the first 5 lines?
The file has up to 91 columns
Also I would like to import only certain columns let's say column 3, 5 and 9.
Attached is an exmple of the file that I would like to import into excel.
Thanks in advance for your help.

I have been using the following code to import tab delimited text files for awhile now and for some reason its not working the same as usual.

The file I am bringing in has some html in it so that is why I'm changing the format to text only upon importing but its not bringing it in correctly as tab delimited. I know the 1st row is tab delimited but its bringing it in all in one cell.

If I use "Get External Data" and "Import Text File" it will come in correctly so I'm pretty sure its not a problem with the data.

Microsoft did have a recent update that failed my ADO stuff so I am wondering if something else changed as well.


	VB:
	
Workbooks.OpenText Filename:=Filename, Origin:=xlWindows, _ 
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ 
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ 
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _ 
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _ 
Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15 _ 
, 2), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2), Array(21, 2), _ 
Array(22, 2), Array(23, 2), Array(24, 2), Array(25, 2), Array(26, 2), Array(27, 2), Array( _ 
28, 2), Array(29, 2), Array(30, 2), Array(31, 2), Array(32, 2), Array(33, 2), Array(34, 2), _ 
Array(35, 2), Array(36, 2), Array(37, 2), Array(38, 2), Array(39, 2), Array(40, 2), Array( _ 
41, 2), Array(42, 2), Array(43, 2), Array(44, 2), Array(45, 2), Array(46, 2), Array(47, 2), _ 
Array(48, 2), Array(49, 2), Array(50, 2), Array(51, 2), Array(52, 2)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance for your help.

How to change encoding for posting data from Excel 2007 to win-1251? Now I have UTF-8, use WinHTTP.dll

Hi

I have never used pivot tables in 2003, so nothing of that sort... but whenever I use pivot tables in excel 2007, it irritates me, and people around me who generally claim that they could perhaps do it in excel 2003.

So here goes my questions.

1. How to make it remember what I have done right now? (Or may be how to change default settings?)
eg- In first column I have some classification, and then I have 12 months data, in 12 columns.
So I put first column in Row Label. Then I put 2nd column (first month data) in "Values" as "sum". Then I want to add all the months into the "values" column as sum, just by ticking in the checkboxes present in Field Section. But somehow excel behaves badly, it sometimes add the field into row label, or sometimes it will put the field in "values" but not as "sum" but as "count".

2. Is 24 month data too big to handle?
If I have 24 month data, and I want to add all the 24 fields in "values", it seems pretty irritating to me to drag each field into the right area and then set up the position of field section and area sections so that you can put next field in right place. (Or if you are dragging to pivot table itself instead of area sections, you will have to set up the position of worksheet so that you can put the next field in right place.

Please help with these two problems, it seems that I might ask more questions in future.

Hi,

I have a tab delimited text file which contains some empty values in
some of the cells, and some of the lines have empty cells at the end of
the line. In my original file (outputted by some software) empty cells
at the end of the line are represnted by a number of 't' characters
with empty strings in between.

When I open the file to edit it in excel and then resave it, empty
cells at the end of a line are not saved.

An example:
If this is the original line string (spaces are not part of the string
- they just seperate chracters here, for clarity):

1 t 2 t t t n

Excel will save it as:

1 t 2 n

I need the file saved in the first format of the example rather then in
the second format outputted by excel in order to continue processing it
in other software. Any way to do it, if I know in advance how many
columns I need in each line?

--
osios
------------------------------------------------------------------------
osios's Profile: http://www.excelforum.com/member.php...o&userid=33756
View this thread: http://www.excelforum.com/showthread...hreadid=535308

Hi,

I have a problem saving my spreadsheet as a tab-delimited text file.

My file consists of a row for each "person", and 6 columns for various details about each person (eg. hospital, name, tel, city, state, etc).

When I save as a tab-delimited text file, I sometimes (not always) end up with some of the entries for one person containing quotation marks. For example:
"Fishville Hospital" "Dr John Smith" 555-123-4567 Fishville OH USA

When the problem occurs, it always relates to the new person I have just added to the spreadsheet.

I have tried changing the format of all cells from General to Text, but this doesn't help.

If anyone has any advice, I would be most grateful.

Cheers,

Steven

Hi,

I have a tab delimited text file which contains some empty values in some of the cells, and some of the lines have empty cells at the end of the line. In my original file (outputted by some software) empty cells at the end of the line are represnted by a number of 't' characters with empty strings in between.

When I open the file to edit it in excel and then resave it, empty cells at the end of a line are not saved.

An example:
If this is the original line string (spaces are not part of the string - they just seperate chracters here, for clarity):

1 t 2 t t t n

Excel will save it as:

1 t 2 n

I need the file saved in the first format of the example rather then in the second format outputted by excel in order to continue processing it in other software. Any way to do it, if I know in advance how many columns I need in each line?

Sometimes when I export excel data to a tab delimited text file one or more
of the data fields is enclosed in quotation marks that did not exist in the
original file. How do I prevent this from happenning. (i.e. Name SSN,
will convert to "Name" SSN

I have the Excel 2000 (9.0.8960 SP3). I created a speadsheet with 4 worksheets in it. I save each of these works sheets as separate Tab delimited Text files. I see extra tabs stored in the tab delimited text files. This happens if I have uneven number of columns of data in the file.
For example , if my data file is as follows with the following data in each of the cells on the row numbers specified

Row 1: Comment line
Row 2: Data1
Row 3:
Row 4: <Heading1> <Heading2> <Heading3>
Row 5: Data0 Data1 Data2

My text file looks as follows with the number of tab as specified by <Tab>
-------------------------------------------------------------------------
comment line <Tab><Tab><Tab>
data<Tab><Tab><Tab>

<heading1><Tab><heading2><Tab><heading3><Tab><heading4>
data0<Tab>data1<Tab><Tab>
data0<Tab>data1<Tab>data2<Tab>data3
--------------------------------------------------------------------------
I have a program that parses this text file and splits data with the tab delimiter. So, when the program see extra tabs, it thinks there are more data points with null data and adds them. I need to have the file as follows for proper export of this data file.
-------------------------------------------------------------------------
comment line
data

<heading1><Tab><heading2><Tab><heading3><Tab><heading4>
data0<Tab>data1<Tab><Tab>
data0<Tab>data1<Tab>data2<Tab>data3
--------------------------------------------------------------------------

Please let me know how I can prevent excel from putting in these extra tabs.

Appreciate any help.

Hi,

I have a worksheet (In a workbook with several sheets) that I want to save to a tab delimited text file.

I want to include the full range of data on the worksheet except the header row.

Currently I can't even figure out how to achieve this without a macro, so I've not even been able to record and modify the action.

Any help?

Many thanks

I'm trying to save an excell spreadsheet as a (tab delimited) text file.
Everytime I try to save it excel decides to add quotes to some of my cells.
How do I get it to stop doing this?
The cells it adds quotes to are cells that already have quotes in them, it
adds extra quotes around the whole cell. I really don't want that
Also cells that have numbers with commas (ie a cell with 64,4) it decides
needs quotes.
So my text file has "64,4" in it instead of 64,4 and "date "4-8-05""
instead of date "4-8-05"

I'm trying to save an excell spreadsheet as a (tab delimited) text file.
Everytime I try to save it excel decides to add quotes to some of my cells.
How do I get it to stop doing this?
The cells it adds quotes to are cells that already have quotes in them, it
adds extra quotes around the whole cell. I really don't want that
Also cells that have numbers with commas (ie a cell with 64,4) it decides
needs quotes.
So my text file has "64,4" in it instead of 64,4 and "date "4-8-05""
instead of date "4-8-05"

Hello, I hope someone can help me with this issue, please.

I need to open a large semi-colon delimited text file into
a recordset using ADO.

Is this possible? If so, how do I make ADO aware that the
file is "semi-colon" delimited?

Right now, my code does not recognize this delimeter and
imports all the data into the recordset as one column
(NOTE: there are too many rows to open into a spreadsheet).

Your example code would be most appreciated, thanks in
advance.

If needed, my code so far is below:

Set cnText = New ADODB.Connection
cnText.CursorLocation = adUseClient
cnText.ConnectionString = "PROVIDER=MSDASQL;DRIVER=
{MICROSOFT TEXT DRIVER (*.TXT; *.CSV)};DBQ=" & argFilePath
& ";"
cnText.Open

strSQL = "SELECT * FROM " & argFileName & ";"
Set rsText = New ADODB.Recordset
rsText.MaxRecords = 0
rsText.Open Source:=strSQL, ActiveConnection:=cnText,
CursorType:=adOpenForwardOnly, LockType:=adLockReadOnly,
Options:=adCmdText

Your example code would be most helpful, TIA.

I have a csv file which I have opened in Excel 2008 on my mac.

How do I export it as a pipe delimited text file.

I can only see an option to save it as a tab delimited text file.

If I cant save as a pipe delimited text file,
Is there a way to convert from a tab delimited text file to a pipe delimited text file?

Excel2002 /Win2K

Hi

When I save a spreadsheet to tab delimited text and re-open it (and
this is something I do a *lot* to get rid of micro$oft formatting and
stray HTML coding...) it always defaults the "file origin" to be
"932:Japanese (Shift-JIS)" instead of "1252:Western European
(Windows)"!

How do I get my MS Excel to default to the correct thing?!

ie I want it to default to:
"1252:Western European (Windows)"
and not
"932:Japanese (Shift-JIS)"
whenever I open a .txt file !

Ship
Shiperton Henethe

Hi Guys,

I want to import a set of 10 tab delimited CSV files in to 10 already existing sheets in an excel workbook.

All the files are in the same location "my data sources" and have the naming convention "page 1 report", "Page 2 report" etc....

I wish to import these in to already named sheets, lets say for arguments sake they are called "sheet 1 report" etc.

I’m having difficulty because i can not workout how to do it without putting it in a loop which, i've been told are flaky or its best to avoid them in vb. I’m also having trouble using the adodb connection which i think is too advance for me at this stage but can not think of how to get around it because i need to import the data and not copy it in.

I haven't attached my code as im adapting an import code which is really long an has many sub and public functions and I feel this may complicate the solution but let me know if you want it posted. if you need any more information or if i've missed any elements out then just ask.

Thanks

Anton

I'm using Excel to import tab-delimited text files containing a large
number of rows and columns. The data are mostly numerical, but some are
text.

Somehow, Excel insists in renaming certain values as if they were
dates. For instance I have a column with gene names, some of which with
names like "SEP10" or "DEC7"... and Excel sees that as a date and turns
it into "10-Sep"... In other cases renames entries such as "3-24" as
"24-Mar"...

How can I turn off EVERY automatic "intelligent" feature in Excel so
that it just takes what I feed it? I have gone through every menu
turning off automatic formatting and everything I could see... but that
behaviour remains.

I'm sure there must be a way to import these data so that numbers are
treated like numbers, and everything else as text... but I can't find
how, and I am finding it very frustrating.

Any ideas?

Thanks!

Jose

How to change the default printing parameters on Excel & keep them changed
for future workbooks.

Example: Normally I use Printing margins 0.25 on all directions, but
default printing margins are 0.75. I want to change them to set 0.25 as
DEFAULT.

Hello,

We are running Excel 2002 and I have been asked by a user how to change an
option for opening files.

When this person selects to open files in Excel the default "Files of type"
option is set to "All Microsoft Excel Files" - they would prefer that this
default setting is set to "Microsoft Excel Files". They swear that this was
an option before and has changed and I can not find out to change this.

If someone knows how to do this that would be great and I can get this one
of the list! :-)

Thanks so much in advance.
Tammy

I'm using Excel to import tab-delimited text files containing a large
number of rows and columns. The data are mostly numerical, but some are
text.

Somehow, Excel insists in renaming certain values as if they were
dates. For instance I have a column with gene names, some of which with
names like "SEP10" or "DEC7"... and Excel sees that as a date and turns
it into "10-Sep"... In other cases renames entries such as "3-24" as
"24-Mar"...

How can I turn off EVERY automatic "intelligent" feature in Excel so
that it just takes what I feed it? I have gone through every menu
turning off automatic formatting and everything I could see... but that
behaviour remains.

I'm sure there must be a way to import these data so that numbers are
treated like numbers, and everything else as text... but I can't find
how, and I am finding it very frustrating.

Any ideas?

Thanks!

Jose

How to change the default printing parameters on Excel & keep them changed
for future workbooks.

Example: Normally I use Printing margins 0.25 on all directions, but
default printing margins are 0.75. I want to change them to set 0.25 as
DEFAULT.

Hi Everyone,

I have built a massive set of queries which work together to export a table containing 942 records and 137 fields. I want to export them to a tab-delimited text file for use in uploading to Turbo Lister 2 (ebay). I have been able to do this a few times, but for some reason Access is not able to export the file consistently, even though my test data has not changed. At first, I thought it might be a Turbo Lister issue, but when I exported my text file from Access, I relinked it back into table to take a look at it, and sure enough, it was garbled. When Turbo Lister tries to import these files, it normally will see about 600 records of the 942, and the fields are all out of whack. I'm thinking that there must be some problem with the fields in the table that are causing it not to tab-delimit correctly, but like I said, it has worked a few times. There is HTML in some of the fields (field is defined as "memo" type), so maybe that's causing the export to see something as a tab that is not??? That doesn't make sense, but I'm game for any ideas or troubleshooting anyone can offer. I wish I could give you more details, but I honestly don't know why it's not working. The table looks good, the data in the table looks fine. I can't just paste into Excel because some fields contain way more than 255 characters. I'm tried export to RTF into Word and back into TXT, but that didn't work at all. I'm out of ideas, out of my mind, frustrated and ready to strangle Bill Gates. I NEED this to work! Thanks.

Greg

Hello
could you tell me how to change the default file name in
the dialog "save as" or "save" without opening the dialog
or save the file
tks in advance..... I am really stucked....

Hi!

I have heaps of files and I need to save them as a tab delimited text (.txt) after I did some changings in each of them. I tried to write a macro for that but I cant make it that the file keeps its original name. If I do it like this it always gets the name "Track 13-3-08":

Sub speichernalstxt()
'
' speichernalstxt Macro
'
' Keyboard Shortcut: Option+Cmd+b
'
    Range("O221").Select
    ActiveWorkbook.SaveAs Filename:= _
        "Macintosh HD:Users:stefandittmann:Desktop:Test:Track 13-3-08.txt", FileFormat _
        :=xlText, CreateBackup:=False
    Range("O221").Select
    ActiveWindow.Close
End Sub

How can I make it to keep the original name or to take the name out of cell B2?

Thanks for helping!!!
Klara


No luck finding an answer? You could always try Google.