Data Import Question

S

Steve Haack

I need to import data into Excel for statistical analysis. The data is being
collected from thousands of PC's in an organization. Each of them generates a
"log" file and places it on a server.

Each file is a text file, tab delimited with a header row, and a data row.
Each file imports perfectly into Excel.

What I would like to do, is concatenate the files into one file, then import
the entire file. I am able to do this by doing: "copy *.log concatenated.txt"
and it creates one file which imports fine.

The problem is that in this concatenated file, every other line is a repeat
of the column headers.

Is there an easy way in Excel to import that file and recognize every other
line as headers and reject them? I am using Excel 2007.

Thanks,
Steve
 
J

Jacob Skaria

Insert a temporary column to the left (ie Col A)and number the rows 1,2,3....
Sort with Col B so that headers will be together and then delete header rows
Sort them with ColA ie the sequence number..
Delete the temporary Col A

If this post helps click Yes
 
J

Jacob Skaria

Insert a temporary column to the left (ie Col A)and number the rows 1,2,3....
Sort with Col B so that headers will be together and then delete header rows
Sort them with ColA ie the sequence number..
Delete the temporary Col A

If this post helps click Yes
 
G

Gord Dibben

You can use a helper column.

Enter 1 and 2 in top two cells.

Select both then right-click and drag down.

Release and "Copy Cells"

Autofilter for 1 and delete those visible cells found by the filter.

Or use a macro.

Sub delete_even_rows()
Dim r As Long
Dim rng As Range
Dim numLoops As Long
ActiveSheet.Rows("1:1").EntireRow.Insert
Set rng = Range(Cells(1), Cells(Rows.Count, ActiveCell.Column).End(xlUp))

numLoops = Int((rng.Rows.Count / 2))
For r = 1 To numLoops
rng(r + 1, 1).EntireRow.Delete
Next
End Sub


Gord Dibben MS Excel MVP
 
G

Gord Dibben

You can use a helper column.

Enter 1 and 2 in top two cells.

Select both then right-click and drag down.

Release and "Copy Cells"

Autofilter for 1 and delete those visible cells found by the filter.

Or use a macro.

Sub delete_even_rows()
Dim r As Long
Dim rng As Range
Dim numLoops As Long
ActiveSheet.Rows("1:1").EntireRow.Insert
Set rng = Range(Cells(1), Cells(Rows.Count, ActiveCell.Column).End(xlUp))

numLoops = Int((rng.Rows.Count / 2))
For r = 1 To numLoops
rng(r + 1, 1).EntireRow.Delete
Next
End Sub


Gord Dibben MS Excel MVP
 

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