Import .csv file(s) with different column headers to master report to align

I'm currently trying to build a reporting tool and for one of the commands (button in place), I want to import one or more .csv files of raw data and have them placed in a tab named "Current" which is the master report. However, the files that are going to be imported may have different column headers. I want to be able for the vba macro to import the .csv file(s) and look at the column header and if it reads for example "id" in one file which is "SR Number" in the other and master report, to align under column A in the current tab. I've attached the reporting tool and some upload examples to provide a better understanding.
emc-and-ge-view-2015-03-18-0855.csv
US-Bank-PHD-17-Mar-2015-13-25.xls
DSE-Carelog-Report.xls
AckeemKAsked:
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.

ThomasMcA2Commented:
You could use VBA to update the CSV file, changing the headers to be what you want them to be. Once your code fixes the data, it should be able to import it.
0
AckeemKAuthor Commented:
Do you know where I can find out how to do so within Excel for Mac 2011?
0
ThomasMcA2Commented:
To be precise, you can't update a CSV file. But you can create a new one that has a new header row.

Here are the basic steps:

1) Write the new header row to a new CSV file
2) Read the CSV file, 1 line at a time
3) Ignore line 1 from the read loop
4) Write lines 2 through the end to the new CSV file
5) Close the new CSV file
6) Continue with the import, using the new CSV file

Use code similar to this to open the original CSV:
Open "C:\path\to\your.csv" For Input As #1

Open in new window


Create the new CSV:
Open "C:\path\to\your\new.csv" For Output As #2

Open in new window


Read the first line (outside of the loop) so it gets ignored:
Line Input #1, DataString

Open in new window


Write the new headers:
Write #2, "Heading1", "Heading2", "Heading3"

Open in new window


Now write the rest of your input CSV:
Do Until EOF(1)
   Line Input #1, DataString
   Write #2, DataString
Loop

Open in new window

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
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.