how to move column in excel - across 8k files

i have 8k csv files in a folder. i would like to move column c to column a position.

any idea how i can do that automatically?
finnstoneAsked:
Who is Participating?
 
FlysterCommented:
This code should loop through all .csv files in the folder you select. I would try it on a test folder to insure this is what you're looking for.
Sub MoveCVSColumns()
Dim strPath As String
Dim strSearch As String
Dim strName As String
Dim strFile As String
     
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
  strFile = "*" 'Will open all .csv files regardless of name
  strPath = "C:\MyFolder\" 'Enter your path here
  strSearch = Dir(strPath & strFile & "*.csv")
    Do While strSearch <> ""
        strName = strPath & strSearch
        Workbooks.Open (strName)
        strSearch = Dir
          Columns("C:C").Select
          Selection.Cut
          Columns("A:A").Select
          Selection.Insert Shift:=xlToRight
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

Open in new window

Flyster
0
 
gr8gonzoConsultantCommented:
Here's a PHP function that will do it on a given filename. It's untested, so make sure you test it against a sample CSV file before you run it on the whole batch. Also, note that the column number is zero-based, so columns start at 0. So column A = 0, column B = 1, column C = 2, etc...

Example:
MoveColumnToBeginning("example.csv", 2); // Should move column C in example.csv to the beginning

function MoveColumnToBeginning($csvFile,$columnNumber)
{
  // Rename old file
  rename($csvFile,$csvFile.".bak");

  // Open old and new file
  $fpSrc = fopen($csvFile.".bak","r");
  $fpDst = fopen($csvFile,"w");
  $lineNumber = 0;
  
  // Read from source, and make sure the column exists...
  $line = fgetcsv($fpSrc);
  if(!array_key_exists($columnNumber, $line))
  {
    // Problem! Roll back!
    fclose($fpSrc);
    fclose($fpDst);
    unlink($csvFile);
    rename($csvFile.".bak",$csvFile);

    // Throw an exception
    throw Exception("Column {$columnNumber} does not exist in {$csvFile} on line {$lineNumber}!");
  }

  // We're okay - keep going...
  $value = $line[$columnNumber]; // Get the value
  unset($line[$columnNumber]); // Remove column
  array_unshift($line, $value); // Push the value into the first column

  // Write the re-ordered line and then increment our line number
  fputcsv($fpDst,$line);  
  $lineNumber++;

  // Close files
  fclose($fpSrc);
  fclose($fpDst);
}

Open in new window

0
 
finnstoneAuthor Commented:
Hi Flyster. thanks. However, I ran the code and nothing happens?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
FlysterCommented:
On the line that starts "strPath =" did you set the correct path to your files? I tried it with a folder containing 3 workbooks and it changed all 3.
0
 
finnstoneAuthor Commented:
yes i used this

 strPath = "F:\tweet\csvtest"
0
 
gr8gonzoConsultantCommented:
Did you try my code?
0
 
gr8gonzoConsultantCommented:
Also, for Flyster's code, I think:
strSearch = Dir(strPath & strFile & "*.csv")

...should be:
strSearch = Dir(strPath & "*.csv")

He's also defining a subroutine, but there's no code in there to actually call it, so if you haven't called the sub, then it won't run.
0
 
finnstoneAuthor Commented:
yes i need to run this for thousands of files so could not use yours.

i insert module and then run macro...but nothing happens
0
 
finnstoneAuthor Commented:
i was missing the \
0
 
finnstoneAuthor Commented:
thank you
0
 
gr8gonzoConsultantCommented:
yes i need to run this for thousands of files so could not use yours.
You probably should have mentioned that that's what you thought.

My code was a function that could be run on any number of files. I provided an example of it on one file just for example purposes, but it was something that could be run on many, many files. You simply loop through the folder with PHP and run that function on each file:

$csvFiles = glob("*.csv");
foreach($csvFiles as $csvFile)
{
   MoveColumnToBeginning($csvFile, 2);
}

Open in new window


I figured you just wanted the "guts" of the code, which was the MoveColumnToBeginning function.
0
 
gr8gonzoConsultantCommented:
For what it's worth, if you have to do this on a continual basis, the PHP method should be much faster than an Excel subroutine.
0
 
finnstoneAuthor Commented:
not on a file by file basis its not but hanks anyways
0
 
FlysterCommented:
Thanks. Just so you know, you can use the line "strFile = "*" " to filter out a specific file, or all files that start with the same string. Example, if you had files named red1, red2, red3, blue1, blue2, blue3, you could enter strFile = "Red*" and just the files that start with red will update. It adds a little more flexibility to the code.
0
 
finnstoneAuthor Commented:
hi flyster i just opened a similar question, appreciate your help!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.