James0903
asked on
Converting .CSV Format File into an Excel Chart
Dear Sir/Madam,
I work in the aviation industry compiling data submitted by the US major airline carriers. Our system is a legacy system and its functionality is limited when it comes its output.
The system will output the data into a .CSV file. Each month, I need to copy the data into a table with headings, name of carrier, year, etc. It is a very time consuming processes as we have 900-1000 carriers. Could you create a macro that automatically performs these steps? The data needs to appear in a table format structure for our external and internal customers.
I have attached my spreadsheet. The data on "Data in CSV format(Original)" is the output data. The tab of "Data in XLS Format(Desired)" is what I am trying to accomplish. In the 'SAMPLE OF DATA' tab (see column V), I have placed notes into this tab explaining how the data should be displayed. Please let me know what you come up with! I really appreciate the hard work the Experts do to make my job easier!
Thank you. Please advise with questions.
ExpertsExchange-Question11.xlsx
I work in the aviation industry compiling data submitted by the US major airline carriers. Our system is a legacy system and its functionality is limited when it comes its output.
The system will output the data into a .CSV file. Each month, I need to copy the data into a table with headings, name of carrier, year, etc. It is a very time consuming processes as we have 900-1000 carriers. Could you create a macro that automatically performs these steps? The data needs to appear in a table format structure for our external and internal customers.
I have attached my spreadsheet. The data on "Data in CSV format(Original)" is the output data. The tab of "Data in XLS Format(Desired)" is what I am trying to accomplish. In the 'SAMPLE OF DATA' tab (see column V), I have placed notes into this tab explaining how the data should be displayed. Please let me know what you come up with! I really appreciate the hard work the Experts do to make my job easier!
Thank you. Please advise with questions.
ExpertsExchange-Question11.xlsx
als315
Are data in your csv files always sorted by carrier? Can you upload original csv file?
I have a legacy system which outputs csv's also. I use robocopy to check for new / updated files and copy to a location. I use Import Wizard to do the update/import into Access/SQL server. I also run Import wiz as a service with Fire Daemon, which fires the robocopy process first. If the PC is running, the process is virtually unstoppable. You can then query your data into Excel if required. I fire the processes at 20 second intervals on a dedicated PC, so data availability appears virtually real time to the users.
Import Wizard - http://www.beside.com/
Fire Daemon - http://www.firedaemon.com/
Chris B
Import Wizard - http://www.beside.com/
Fire Daemon - http://www.firedaemon.com/
Chris B
ASKER
als315, the programmer has provided me with sample data of two airlines. Yes, the data will always be sorted by carrier. The data is in the "Data in CSV Format (Original)" tab of my spreadsheet. She is presently coding the system to have the data produced in .CSV format now so we can find a better way to do this. Until I have the complete file, I only have these two airlines to play with. Let me know if you need me to reload the spreadsheet. Thank you for your help!
What is the significance or purpose of the letters in row 22? They might be misleading, since they do not correspond with the actual Excel column.
Does the CSV data not have a header row?
It looks like you have the following data mappings. The Offset() is from the top left corner of the report page.
A1 -> C6 or Offset(5, 1)
B1 -> C7 or Offset(6, 1)
C1 -> C8 or Offset(7, 1)
D1 -> C9 or Offset(8, 1)
E1 -> C10 or Offset(9, 1)
F1 -> R5 or Offset(4, 16)
H1 & "-(" & G1 & ")" -> R6 or Offset(5, 16)
I1 -> R7 or Offset(6, 16)
J:K -> B:C
Q:Q -> I:I
S:AB -> K:T
Does the CSV data not have a header row?
It looks like you have the following data mappings. The Offset() is from the top left corner of the report page.
A1 -> C6 or Offset(5, 1)
B1 -> C7 or Offset(6, 1)
C1 -> C8 or Offset(7, 1)
D1 -> C9 or Offset(8, 1)
E1 -> C10 or Offset(9, 1)
F1 -> R5 or Offset(4, 16)
H1 & "-(" & G1 & ")" -> R6 or Offset(5, 16)
I1 -> R7 or Offset(6, 16)
J:K -> B:C
Q:Q -> I:I
S:AB -> K:T
ASKER
Aikimark, thank you for helping me with this.
The letters in row 22 do not have any significance for us, but for our customers who use this data, it is important for them since they have created their own programs to process data from our data output. So, this row of letters is not affiliated with the original .CSV output, but needs to be in the final output.
In regards to the header rows, I had asked a similar question last week (not as complicated as this one) and Expert Imnorie replied. See:
https://www.experts-exchange.com/questions/27869211/Converting-Data-from-CSV-to-an-XLS-Table.html
When I had mistakenly left the header row in, the titles from the header row went into my final graphs, which is not what I wanted. So each time I have to remove the header row (no big deal) to get it to work correctly. Thus, this is why I don't have header rows in the .CSV file. I can easily create some if needed...
I will verify the mappings and respond within next 15 minutes. My PC is forcing me to reboot in <5 minutes so it can do its updates.
Thanks,
James
The letters in row 22 do not have any significance for us, but for our customers who use this data, it is important for them since they have created their own programs to process data from our data output. So, this row of letters is not affiliated with the original .CSV output, but needs to be in the final output.
In regards to the header rows, I had asked a similar question last week (not as complicated as this one) and Expert Imnorie replied. See:
https://www.experts-exchange.com/questions/27869211/Converting-Data-from-CSV-to-an-XLS-Table.html
When I had mistakenly left the header row in, the titles from the header row went into my final graphs, which is not what I wanted. So each time I have to remove the header row (no big deal) to get it to work correctly. Thus, this is why I don't have header rows in the .CSV file. I can easily create some if needed...
I will verify the mappings and respond within next 15 minutes. My PC is forcing me to reboot in <5 minutes so it can do its updates.
Thanks,
James
ASKER
Yes, all your mappings are correct. The programmer just notified me that she has added another value. So, for the mapping, add P1 -> H1. (I have attached an updated file. I just put a value of '50' in the column all the way down for you since I don't have the real values yet).
Also, can a column totals for columns H through T on the output (excluding blanks, if can, if not, no worries) be added as well, after the last data entry for each airline?
Thank you!
ExpertsExchange-Question11---Upd.xlsx
Also, can a column totals for columns H through T on the output (excluding blanks, if can, if not, no worries) be added as well, after the last data entry for each airline?
Thank you!
ExpertsExchange-Question11---Upd.xlsx
By inserting the new column where you did, you have invalidated the last three lines of mappings I posted earlier.
I do not see the "50" values anywhere in the output. Is this a vestigial column?
I do not have the time to code this solution. My comments are meant to help the other experts who will code a solution and to help you better describe the problem.
Since you have a template (second tab), that will be quite helpful in constructing each page (.Copy method)
Hint: Once you have a list of the unique airline codes (G:G), you iteratively apply filters to the data to isolate the data for that airline. I used the AdvancedFilter method in this solution: http:Q_27771270.html#a38132580
However, you could use a standard filter and dictionary object to iterate/isolate groups of rows. It is even possible to do this 'filtering' entirely with VBA code.
I do not see the "50" values anywhere in the output. Is this a vestigial column?
I do not have the time to code this solution. My comments are meant to help the other experts who will code a solution and to help you better describe the problem.
Since you have a template (second tab), that will be quite helpful in constructing each page (.Copy method)
Hint: Once you have a list of the unique airline codes (G:G), you iteratively apply filters to the data to isolate the data for that airline. I used the AdvancedFilter method in this solution: http:Q_27771270.html#a38132580
However, you could use a standard filter and dictionary object to iterate/isolate groups of rows. It is even possible to do this 'filtering' entirely with VBA code.
"I do not have the time to code this solution" - I hoped for you :)
ASKER
Thank you for all your comments.
I have added the value of 50 into the spreadsheet. My apologies for that. The column mappings did not change. The previous value for this column was: "Intentionally left blank...". I simply replaced this with a value of '50' and then added this to the spreadsheet.
I have attached an updated spreadsheet. Are there any other Experts who may be to assist? Expert Imnorie helped me with the original request:
https://www.experts-exchange.com/questions/27869211/Converting-Data-from-CSV-to-an-XLS-Table.html
So can he/she take a look and provide any assistance?
Thank you,
James
ExpertsExchange-Question11---Upd.xlsx
I have added the value of 50 into the spreadsheet. My apologies for that. The column mappings did not change. The previous value for this column was: "Intentionally left blank...". I simply replaced this with a value of '50' and then added this to the spreadsheet.
I have attached an updated spreadsheet. Are there any other Experts who may be to assist? Expert Imnorie helped me with the original request:
https://www.experts-exchange.com/questions/27869211/Converting-Data-from-CSV-to-an-XLS-Table.html
So can he/she take a look and provide any assistance?
Thank you,
James
ExpertsExchange-Question11---Upd.xlsx
James
How many entries go in each section?
Is it 20?
What happens when you have more than 40 entries?
How many entries go in each section?
Is it 20?
What happens when you have more than 40 entries?
ASKER
Thank you Imnorie! Our programmer told me that the most aircraft types, i.e. Boeing 737-300, Boeing 747-400, etc. (which is the first column in the table) is 17. So I am thinking 20 rows is sufficient just in case 2-3 are added to their fleet.
Since I was not sure if you would be able to help, I started on my own, trying to understand the code you sent the other day. I think I am doing okay, but it is taking me forever. This is what I have so far. However, for some reason, the formatting for page 1 is not reflecting in page 2 and so on. Yes, I am a very beginner so if you scratch your head wondering how I coded, just ask!
I have put the VB code into a Word Doc and attached just in case there are issues when you try to get to the VB code in Excel.
Thank you!
James
ExpertsExchange-Question11---Upd.xlsm
CODE-TO-GO-ALONG-QUESTION---JAME.docx
Since I was not sure if you would be able to help, I started on my own, trying to understand the code you sent the other day. I think I am doing okay, but it is taking me forever. This is what I have so far. However, for some reason, the formatting for page 1 is not reflecting in page 2 and so on. Yes, I am a very beginner so if you scratch your head wondering how I coded, just ask!
I have put the VB code into a Word Doc and attached just in case there are issues when you try to get to the VB code in Excel.
Thank you!
James
ExpertsExchange-Question11---Upd.xlsm
CODE-TO-GO-ALONG-QUESTION---JAME.docx
@imnorie
There are comments in the third worksheet that might be helpful
I would look at using a twenty row template that includes the Sum formulas. Then a .Copy of the values should render correctly.
There are comments in the third worksheet that might be helpful
THE MOST ENTRIES AN AIRLINE WILL HAVE IS 20. THE LEAST IS 2. FOR THE AIRLINE
THAT HAS 2, YOU CAN EITHER HAVE 17 BLANKS LINE (1 LINE IS FOR TOTAL) TO TOTAL
20 OR YOU HAVE 3 LINES, WHATEVER WORKS.
I would look at using a twenty row template that includes the Sum formulas. Then a .Copy of the values should render correctly.
ASKER
Imnorie, you may wonder why 'Sheet22' in the attached looks like a different format than the 'Sample of Data' sheet, in regards to rows 4-6 on the Sheet22, and 6-10 on the 'Sample of Data'. It is because I was concerned that I would not have enough room to fit the entire airline on one page, which is my goal. Thus, I reduced the contact information about me into less lines, which I seem to like better.
Thanks!
Thanks!
Test this also
ExpertsExchange-Question11-1.xlsm
ExpertsExchange-Question11-1.xlsm
ASKER
Awesome! I have run and it looks great. Thank you! The Experts always find a way to make it happen to save me hours of work! I am in the process of reviewing the data elements and if all looks good I will accept as the Solution.
If you need any changes in format - do it in "Template" sheet. It is copied to result without changes (totals line also). You should not add or remove lines in this sheet. Position is important.
Finally had a chance to take a proper look at this.
Find my effort attached.
ExpertsExchange-Question11-V2.xlsm
Find my effort attached.
ExpertsExchange-Question11-V2.xlsm
ASKER
als315, thank you for your additional comments. I will keep that in mind.
ASKER
Imnorie, thank you very much for your results as well. I appreciate your help as well in making this process a lot better!
ASKER
als315, on yours, I see the page break applied at the end of page one, but when trying to print all pages, only the first page prints (this is after I shrink the data to fit all the data onto the first page).
Can the page break be applied after each page to enable me to print each page?
Also, the original requirements stated to have the numbers displayed with a comma, i.e. 1,000. I have been informed that our primary customer (another country) do not want to see commas in the numbers (they are very picky). If not too difficult, can the commas be removed?
Thank you,
James
Can the page break be applied after each page to enable me to print each page?
Also, the original requirements stated to have the numbers displayed with a comma, i.e. 1,000. I have been informed that our primary customer (another country) do not want to see commas in the numbers (they are very picky). If not too difficult, can the commas be removed?
Thank you,
James
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent job als315 and imnorie! I appreciate all of your efforts and time put into this challenge. This is going to save me a massive amount of work and time. Our customers will be extremely satisfied with the results. Thank you very much.