IMPORTING PC-DOS PLAIN TEXT FILES WITH LEADING SPACES


R

Robert1998

Hello,
I am having trouble importing plain MS-DOS text files. Leading spaces on
lines get removed in Excel . How can I prevent the removal of the leading
spaces?

Example ... ORIGINAL TEXT LINE = "_____Thank you"
IMPORTED in excel = "Thank you"
The import wizard shows the leading spaces correctly.
Font used is COURRIER NEW (monospace I believe)
Cell format is TEXT.

Thank you
 
Ad

Advertisements

S

Sheeloo

I just tested with the four line file give below and spaces were preserved..
(nos added for readability, not part of the file)
1: Text1,09/10/2008, Text2,12345,09/10/2008
2: Text2,09/10/2008, Text2,12345,09/10/2008
3: Text3,12/15/2008, Text2,12345,09/10/2008
4:Text4,09/10/2008,Text2,12345,09/10/2008

Can you desribe the steps you are taking to import...
if possible paste a line of data...
 
R

Robert1998

This is the text file...

1 UI_NOM Character 35
2 UI_ADR Character 35
3 UI_VIL Character 35
4 UI_COD Character 35
5 UI_TL1 Character 10
6 UI_TL2 Character 10
7 UI_FAX Character 10

This is the macro I recorded using the wizard....

Range("C13").Select
With
ActiveSheet.QueryTables.Add(Connection:="TEXT;S:\ASEbeta\STRUC.TXT", _
Destination:=Range("$C$13"))
.Name = "STRUC_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

I hope it makes sense. (I am a new)

Thank you
 
S

Sheeloo

TextFileTabDelimiter = True tells me that TAB is taken as the separator...

..TextFileColumnDataTypes = Array(2) indicates that first field is imported
as TEXT and others as General..

Are you sure there are spaces in the text file. If it has TABs only then
they will not be imported...

If possible mail the text file to me...
my id is to_sheeloo
add @hotmail.com to it to get the address
Also let me know the Excel version...
 
Ad

Advertisements

S

ShaneDevenshire

Hi,

why don't you record the import process. Choose Tools, Macro, Record new
macro, save it in this workbook, don't worry about anything else and click OK.

Now the steps you should record - Choose File, Open, change the files of
type to *.Txt or All Files and navigate to the drive and folder, pick the
file and click open. The Import Wizard should appear. Follow the steps and
when the file is in, turn off the recorder.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top