Txt file import - automatic comma and semi colon separation?

  • Thread starter Thread starter ptech
  • Start date Start date
P

ptech

I am currently involved in a lot of testing and the software linked t
our equipment produces data that we import into excel as a txt file.

It is incredibly time consuming to do when every test produces 50 file
of data.

Is there any way that I can get excel to automatically convert the dat
from the following form into excel format without having to click o
the options of separate by comma and semi-colon when opening eac
file?

typical txt data form -

1.54,999;
1.56,988.3;
1.88,990;
1.58,977;
1.3,997;

I would be very grateful for any help in this matter!

Kind regards,
ptec
 
Don't know waht you mean by comma and by semi-colon,
you should be using only one delimiter.

If you change the
file extension to "csv" instead of ".txt" then Excel will make
determinations for you based on your Regional Settins and
I think on your previous usage (is so not reliable).

If that works
for you then you are set. Most people have problems with
what Excel assumes for ".csv" so end up having to use
".txt" so that Excel will invoke the Text Import Wizard.
 
Just an idea but you could possibly save the data as a *.csv file first, then open it with excel. File-->Open then change the file type to "Text" which includes file extensions of *.csv. I believe it you do this it will handle everything automatically, except your semi-colons at the end.
 
Hi, ptech.

If you wish to open all the files and import their
data, you can play with this macro. All files
should be in the same directory. Use test files
first, of course.

Sub OpenFiles()
Dim nam As String
Dim dr As String
dr = "C:\Directory\Subdirectory\"
nam = "MyDatafile*.txt"
nam = Dir(dr & nam)
While nam <> ""
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & dr & nam, _
Destination:=ActiveCell)
.Name = nam
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier =
xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Columns("A:A").Find(What:="", After:=Range("A1"),
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Select
nam = Dir
Wend
End Sub

Good Luck!
jeff
 
Thank you all for your replies!

I'm going to give them all a shot - would like to try the macro - but
have never worked with them before - so it might take me some time to
get it to work properly!

Once again thaks everybody!

Regards
Ptech
 
Back
Top