importing and manipulating data

[fuzit]

I have about 30 txt files with about a thousand records in each. Th
elements are comma delimited. There is no header line and the firs
record has different fields than the remaining records.

The first records contains some descriptive elements (highlevel typ
info) of which only the last element is important.

I want to import all the txt files into one large table. But I want t
append the last element in the first line of each file to the end o
each successive record from the same file.

Example:
File1.txt
xxx,xxxxx,xx,123,xxxx
zzzzzzz,zzzz.zz,zzzzzz,zzzzz
zzzzzz,zzzz.zz,zzzzz,zzzzz
zzzzzzz,zzzz.zz,zzzzz,zzzzz

File2.txt
aaa,aaaaa,aa,124,aaaa
zzzzzzz,zzzz.zz,zzzzzz,zzzzz
zzzzzz,zzzz.zz,zzzzz,zzzzz
zzzzzzz,zzzz.zz,zzzzz,zzzzz

File3.txt
ppp,pppppp,pp,245,pppp
zzzzzzz,zzzz.zz,zzzzzz,zzzzz
zzzzzz,zzzz.zz,zzzzz,zzzzz
zzzzzzz,zzzz.zz,zzzzz,zzzzz

New spreadsheet
zzzzzzz,zzzz.zz,zzzzzz,zzzzz,xxxx
zzzzzz,zzzz.zz,zzzzz,zzzzz,xxxx
zzzzzzz,zzzz.zz,zzzzz,zzzzz,xxxx
zzzzzzz,zzzz.zz,zzzzzz,zzzzz,aaaa
zzzzzz,zzzz.zz,zzzzz,zzzzz,aaaa
zzzzzzz,zzzz.zz,zzzzz,zzzzz,aaaa
zzzzzzz,zzzz.zz,zzzzzz,zzzzz,pppp
zzzzzz,zzzz.zz,zzzzz,zzzzz,pppp
zzzzzzz,zzzz.zz,zzzzz,zzzzz,pppp

Any help would be appreciated

 

[Guest]

You can easily append the descriptive elements

=concatenate(a2,right($a$1,5)

If your imported data exists in column A, then pase this formula down column B. Copy. Paste values overtop of your original text

----- fuzit > wrote: ----

I have about 30 txt files with about a thousand records in each. Th
elements are comma delimited. There is no header line and the firs
record has different fields than the remaining records

The first records contains some descriptive elements (highlevel typ
info) of which only the last element is important.

I want to import all the txt files into one large table. But I want t
append the last element in the first line of each file to the end o
each successive record from the same file

Example
File1.tx
xxx,xxxxx,xx,123,xxx
zzzzzzz,zzzz.zz,zzzzzz,zzzz
zzzzzz,zzzz.zz,zzzzz,zzzz
zzzzzzz,zzzz.zz,zzzzz,zzzz

File2.tx
aaa,aaaaa,aa,124,aaa
zzzzzzz,zzzz.zz,zzzzzz,zzzz
zzzzzz,zzzz.zz,zzzzz,zzzz
zzzzzzz,zzzz.zz,zzzzz,zzzz

File3.tx
ppp,pppppp,pp,245,ppp
zzzzzzz,zzzz.zz,zzzzzz,zzzz
zzzzzz,zzzz.zz,zzzzz,zzzz
zzzzzzz,zzzz.zz,zzzzz,zzzz

New spreadshee
zzzzzzz,zzzz.zz,zzzzzz,zzzzz,xxx
zzzzzz,zzzz.zz,zzzzz,zzzzz,xxx
zzzzzzz,zzzz.zz,zzzzz,zzzzz,xxx
zzzzzzz,zzzz.zz,zzzzzz,zzzzz,aaa
zzzzzz,zzzz.zz,zzzzz,zzzzz,aaa
zzzzzzz,zzzz.zz,zzzzz,zzzzz,aaa
zzzzzzz,zzzz.zz,zzzzzz,zzzzz,ppp
zzzzzz,zzzz.zz,zzzzz,zzzzz,ppp
zzzzzzz,zzzz.zz,zzzzz,zzzzz,ppp

Any help would be appreciated

 

[Dave Peterson]

How about putting all 30 text files into a folder (copy them if you want). Then
run a macro:

Option Explicit
Sub testme01()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim tempWks As Worksheet
Dim tempVal As Variant
Dim AllWks As Worksheet
Dim iRow As Long
Dim destCell As Range

'change to point at the folder to check
myPath = "c:\my documents\excel"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.txt")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

Set AllWks = Workbooks.Add(1).Worksheets(1)

Application.ScreenUpdating = False

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
Set destCell = AllWks.Range("a1")
For fCtr = LBound(myFiles) To UBound(myFiles)
Application.StatusBar = "Processing: " & myFiles(fCtr)
Workbooks.OpenText Filename:=myPath & myFiles(fCtr), _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False ', FieldInfo:=Array(1, 1)

Set tempWks = ActiveSheet
With tempWks
tempVal = .Cells(1, .Columns.Count).End(xlToLeft).Value
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(iRow, _
.Columns.Count).End(xlToLeft).Offset(0, 1).Value _
= tempVal
Next iRow
.Range("A2", .Cells.SpecialCells(xlCellTypeLastCell)).Copy _
Destination:=destCell
With AllWks
Set destCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
.Parent.Close savechanges:=False
End With
Next fCtr
End If

AllWks.UsedRange.Columns.AutoFit

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm