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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.