Save a file as .txt & keep spacing

beancounter

Board Regular
Joined
Oct 30, 2002
Messages
111
I thought this would be easy. I can't figure out how to paste a portion of a worksheet so, here's what I have:

A B C D E F
1 123 ABC 1 123 Joe Smith 1234 N. Elm
2 123 ABC Tommy Jones 59th Street

When I save it as a .txt (no matter csv or DOS...) my text file looks like this:

123 ABC 1 123 Joe Smith 1234 N. Elm
123 ABC Tommy Jones 59th Street

How can I save it as .txt and keep my columns aligned without inserting gibberish in columns C & D and filling non-matching columns E & F?????

Thanks,
John
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,645
Office Version
2013
Platform
Windows
A B C D E F
1 123 ABC 1 123 Joe Smith 1234 N. Elm
2 123 ABC Tommy Jones 59th Street

When I save it as a .txt (no matter csv or DOS...) my text file looks like this:

123 ABC 1 123 Joe Smith 1234 N. Elm
123 ABC Tommy Jones 59th Street

Is this an Excel file that you are saving as a text file? How are you saving it -- by using File | Save As ?

AB


EDIT: Note, if your sheet looks like this you will probably need to do something as you describe -- the columns need to line up with the addresses in the same column, names in the same column, etc.
book1
ABCDEF
1Col1Col2Col3Col4Col5Col6
2123ABC1123JoeSmith1234N.Elm
3123ABCTommyJones59thStreet
4
5
Sheet1
 
Last edited:

beancounter

Board Regular
Joined
Oct 30, 2002
Messages
111
It is funny because the posting did the same thing the save as does and moved all the data to the left.

However, Yes the data is in each respective column (name is in the name column, address is in the address column). What I am doing is a data conversion to another system and can extract into excel from Unix and need to save as .txt for upload into SQL. So the spacing is crucial. Any ideas?

How did you get the picture of a worksheet in your post???

Thanks,
John
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,645
Office Version
2013
Platform
Windows
I'm trying to get a bead on what's happening but it doesn't entirely make sense.

Here's my file with proper columns:
20080616_txtFileProblem.xls
ABCDEF
1Col1Col2Col3Col4Col5Col6
2123ABC1123JoeSmith1234N.Elm
3123ABCTommyJones59thStreet
4
5
Sheet1


Here's my file when saved as a .csv file:
(File | Save As and using .csv for the save as file type)


Col1, Col2, Col3, Col4, Col5, Col6
123, ABC, 1, 123, Joe Smith, 1234 N. Elm
123, ABC, , , Tommy Jones, 59th Street

(I added spaces to even it out:
actually,
Col1,Col2,Col3,Col4,Col5,Col6
123,ABC,1,123,Joe Smith,1234 N. Elm
123,ABC,,,Tommy Jones,59th Street
)


It doesn't make sense why this doesn't work for you. In any event, are you sure of the specifications you need for your import? Is there any way to avoid these multiple conversions (...something to Excel to text to SQL...) - I guess the answer is no or you wouldn't be posting here!

AB

Posting excel sheets, see here under HTML Maker:
http://www.mrexcel.com/forum/showthread.php?t=126629
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,645
Office Version
2013
Platform
Windows
How can I save it as .txt and keep my columns aligned
Depending on your application that is importing from this file, alignment may or may not be important...a comma-separated file may look like a mess but import just fine. The same would be true for tab-delimited files. What's happening to you is that you appear to have no recognizable delimiter (or spaces are being used, improperly).
 

beancounter

Board Regular
Joined
Oct 30, 2002
Messages
111
I'm not sure what program our implementer will be using to import but I've been told it has to be in a specific format with the columns lined up.

How do I get a recognizable delimiter?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,645
Office Version
2013
Platform
Windows
I'm not sure what program our implementer will be using to import but I've been told it has to be in a specific format with the columns lined up.

How do I get a recognizable delimiter?
This sounds like a either a fixed width file or a tab delimited file to me. I'd suggest you get more information from your colleagues ... I think you need to start with the correct text import specifications, then figure out how to get the data into that format.

AB
 

Watch MrExcel Video

Forum statistics

Threads
1,099,288
Messages
5,467,756
Members
406,550
Latest member
miraclewhip

This Week's Hot Topics

Top