Import csv files, delete rows, arrange columns ....

[Guest]

I am importing 34 csv files (each into a different sheet) into one workbook
with one "Main" sheet. e.g. "Main", "1", "2", ..... , "34". Once these are
imported, I have to delete unwanted data from certain columns. Then organize
the columns to a specific order to match the "Main" sheet. The column
heading is located in range("A16:AF16") on the csv imported tabs. Once it is
arranged in this format, I have to find the max and min of certain columns
and select that corresponding row of data to copy to the "main" sheet. I
just need help getting started with the sorting process. This is what I have
so far. TIA for any help you can provide.

Sub OpenCSVFiles()
Dim wks As Worksheet
Dim wkbk As Workbook
Dim rngchan As Range
Dim wksmain As Worksheet
Dim i As Integer

Set wksmain = Sheets("Main")

With Application.FileSearch
.NewSearch
.LookIn = "C:\test\csv"
.SearchSubFolders = False
.Filename = ".csv"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set wkbk = Workbooks.Open(Filename:=.FoundFiles(i))

For Each wks In wkbk.Worksheets
dblLastRow = Range("A65535").End(xlUp).Row
Set rngchan = Range("A16:AF16")
wks.Copy after:=ThisWorkbook.Sheets(i) 'Change sheet name

Range("a1,b1,c1,d1,e1,h1,j1,m1,n1,v1:x1,z1,aa1:ad1,ai1,al1:aq1,ax1:ba1").EntireColumn.Delete

This is where I need to arrange columns in a certain order from the column
heading in rngchan. In the "main" sheet it is in a certain order. e.g.main
sheet 1,2,3,...34 and on csv tabs it is in 16,3,9,...2

Then I have to find max and min of column 3 and then copy those rows of data
to the "main" sheet in a specified location.



Next wks
wkbk.Close
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

 

[Guest]

Wow... I assume you can not change the format of the CSV when it is
created... Have you considered reading the CSV one line at a time and using
the split function to load an array. You can then just output the correct
elements of the array in the proper order. I wonder if that might not be
easier. Looking at your code you know what you are doing so I do not assume
to over ride your judgement. Just to offer an alternative.

 

[Guest]

I am really new to VBA coding but I am trying to learn as I go. I am not
aware of the split function you are referencing. Where would be the best
place to learn about this procedure? You may be correct....that may be
easier. I was using this method because there was so much data manipulation
involved once I inputed the data, I thought it would be easy to click on each
tab and see the data without actually touching the original CSV files as they
take hours of simulation to produce. And no I cannot change the exporting
format of these files. Thank you for your input. If you have any
suggestions about the split function, I am all ears

 

[Guest]

Also I forgot to ask....do you think this would be easier to accomplish in
Access? Even though I do not know Access very well.

 

[Tom Ogilvy]

Since the csv's are in a standard order, you can just put the required order
above the existing column headings, then sort left to right. That assumes
your headers are not really numbers. If they are numbers and you just want
to sort in numerical order, you don't need to add a dummy header.

The code would be

Range("A1").CurrentRegion.Sort _
Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlno,OrderCustom:=1, _
MatchCase:=False, Orientation:=xlLeftToRight

rwMin = Application.Match(application.Min(columns(3)), _
Columns(3),0)
rwMax = Appliction.Match(application.Max(columns(3)), _
Columns(3),0)

rows(rwMin).copy Destination:=worksheets("Main").Cells( _
rows.count,1).End(xlup)(2)
rows(rwMax).copy Destination:=worksheets("Main").Cells( _
rows.count,1).End(xlup)(2)


------
if you need to put in a row, then
Rows(1).Insert
Range("A1:AF1").Value = Array(16,3,9, . . .,2)

do the sort and copy

Rows(1).Delete

 

[Guest]

Here is an example of the split function in action. Plcae this code in a
sheet or module. Put some data in one cell like

This, that, The other

Make sure that the cursor is placed in the cell and then run this code

Private Sub SplitCSV()
Dim aryCSVValues As Variant
Dim rngPasteTo As Range
Dim intCounter As Integer
Dim wksPasteTo As Worksheet

aryCSVValues = Split(ActiveCell.Value, ",")
Set wksPasteTo = Worksheets.Add
Set rngPasteTo = wksPasteTo.Range("A1")

For intCounter = LBound(aryCSVValues) To UBound(aryCSVValues)
aryCSVValues(intCounter) = Trim(aryCSVValues(intCounter))
rngPasteTo.NumberFormat = "@"
rngPasteTo.Value = aryCSVValues(intCounter)
Set rngPasteTo = rngPasteTo.Offset(1, 0)
Next intCounter
End Sub

HTH

 

[Guest]

If you have access then this is fairly easy. Open a new blank databse. You
will be asked to save the database. Click on file and then on Get External
Data -> import. Switch to File Type Text. Find your file and click Open. A
wizard will walk you through extracting the data. You can now create a query
off of the table to extract only the columns you want in the order you want.
The nice thing about doing it this way is that you are not throwing away any
source data. It is a perfectly acceptable option.

Of course Tom has some code for you that might just get you around the
problem that you are having. Take a look at what he has. It will do the
sorting for you...

HTH

 

[Guest]

Thank you Tom and Jim for all your wonderful help. You guys are great. With
the code you provided and some recorded macros, I think I will be able to
accomplish this task....hopefully. Thanks again.

I'm sure it won't be the last time you pull me out of a jam

xadnora

 

[Guest]

Sorry to bug you, but during the sort procedure, how do I sort the entire
column for the dummy header. It is sorting the dummy header row but the
column data is not sorted with it. Thanx.

xadnora