Solved: [RESOLVED] Import a text file and keep the spaces

[Simo Bk]

Hi,

I am trying to import a text file that has a structure that's basically like this :

 
      TITLE
     2   45  18  
COL1    COL2      COL3     COL4
aaa     000       11       222
bbbbb   33        4444     5

I use the following code to import it :
[vba]With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;D:\test.txt", _
Destination:=Range("A1"))
.Name = "test"
.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, 2, 2, 2)
.TextFileFixedColumnWidths = Array(8, 10, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With[/vba]

And then recreate the split headers like this :
[vba]Range("E1") = Range("A1").Value & Range("B1").Value
Range("E2") = Range("A2").Value & Range("B2").Value[/vba]

Here is the result I get :


If you compare the re-generated title in column E2, you can see that it went from 2 45 18 to 245 18

How can I make it so it doesnt get rid of the preceeding and trailing spaces on import, so I don't have this problem?

Thank you,

Simo

[RonMcK]

Simo,

How about inserting the following?
[vba]Range("E2") = Range("A2").Value & " " & Range("B2").Value [/vba] Will this help you? (underline for emphasis only)

Cheers,

[mdmackillop]

[VBA] With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\AAA\data.txt", _
Destination:=Range("A1"))
.Name = "data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
[/VBA]

[Simo Bk]

Thank you for your answers.

I should have specified that the file I attached was just a sample of multiple way more complicated files.

@ RonMcK :
The number of digits is not always the same. So by using this method, it would create the opposite problem where if the split occurs in a part of the string where there was no space, an unnecessary space would be created. I really need to recover the original string as it was, wether it was 2 45 18 or 25414 56521 45 or anything else

@ mdmackillop :
From what I can understand, you used spaces as delimiters instead of fixed column width. Unfortunately, once again it works with the example I gave, but in the real situation, there could be spaces in the strings. IE :

    TITLE
   2   45  18  
COL1    COL2      COL3     COL4
a a a   000       11       222
bbb bb  33        4444     5

Any help would be greatly appreciated. Basically, all I want is to retrieve the exact value "2 45 18" in a variable (or whatever the actual value in the file is)

Thank you,

Simo

[RonMcK]

Simo,

Thanks for the added points. Are there any other gotchas in your data that we need to be aware of? Remember quality of suggested solutions is proprotionate to the completeness of examples in sample data/files.

So, there may be one space between characters in a cell but never 2 or more as my mod to your 'b' line, below, suggests. Right?

    TITLE
   2   45  18  
COL1    COL2      COL3     COL4
a a a   000       11       222
b  bb bb  33        4444     5

Thanks,

[mdmackillop]

Import the file then reread as a textstream to get the required line
[vba]
Option Compare Text
Sub Test()
Dim fs, a, retstring, MyTitle

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.OpenTextFile("c:\AAA\Data.txt", 1, False)
Do While a.AtEndOfStream <> True
retstring = Trim(a.readline)
If retstring = "TITLE" Then
mytitle = Trim(a.readline)
Exit Do
End If
Debug.Print retstring
Loop
a.Close
Range("A2").Resize(, 10).ClearContents
Range("A2") = mytitle

End Sub

[/vba]

[Simo Bk]

Sorry, there COULD BE two or more spaces...

I am sorry it's kinda hard thinking about all the possibilities because I have files that are generated daily and different everyday. Data on the files is confidential so I cannot post the actual data, just examples.

I'm actually thinking of creating a temporary sheet, reimporting the file to that sheet without splitting it, copying the header, then deleting the temporary sheet.

But I am sure there must be another solution?

[Simo Bk]

Thanks mdmackillop,

I had a hard time understanding and applying the code (specially that I didnt get the fact that readline actually moved to the next line and I was wondering why the same function returned different results without moving... ).

But after some effort, I could apply it to the bigger macro, tried it with about 20 differents sources and it seems to work perfect.

Thanks a lot,

Simo

[mdmackillop]

Good to stretch the brain occasionally! Glad it's worked out.
Regards
MD