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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. 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
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. 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
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. 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,130,083
Messages
5,639,980
Members
417,121
Latest member
DallyDally

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top