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