PDA

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



Simo Bk
07-08-2008, 08:01 AM
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 :
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

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

Here is the result I get :
http://www.simo-multimedia.com/tmp/vbaImport.jpg

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
07-08-2008, 09:20 AM
Simo,

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

Cheers,

mdmackillop
07-08-2008, 09:49 AM
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

Simo Bk
07-08-2008, 10:37 AM
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
07-08-2008, 10:57 AM
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
07-08-2008, 11:00 AM
Import the file then reread as a textstream to get the required line

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

Simo Bk
07-08-2008, 11:02 AM
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
07-08-2008, 12:57 PM
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... :D).

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
07-08-2008, 03:03 PM
Good to stretch the brain occasionally! Glad it's worked out.
Regards
MD