Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Converting Data from CSV to an XLS Table

Posted on 2012-09-18
8
Medium Priority
?
283 Views
Last Modified: 2012-09-27
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 to 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..." is the output data.  The tab of "Data in XLS Format..." is what I am trying to accomplish.  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-Question9.xlsx
0
Comment
Question by:James0903
  • 4
  • 4
8 Comments
 
LVL 36

Expert Comment

by:Norie
ID: 38410547
Is it always the same 9 lines of data for each airline?

Do you want each airline on a separate worksheet?
0
 

Author Comment

by:James0903
ID: 38410683
Imnorie,

Thank you for taking this on.

A change in requirements after I sent.  I only need the rows highlighted in green on the "Data in CSV format..." sheet to appear in the table of "Data in XLS Format...".  

Now, only 6 rows per airline are needed.  And yes, the same 6 lines of data will appear for each airline.

I would like to have all the airlines displayed on the same tab with Page Breaks if possible.

Thank you (new file attached)
ExpertsExchange-Question9.xlsx
0
 
LVL 36

Expert Comment

by:Norie
ID: 38410948
This will take the data from the CSV and put it on a new sheet in the required format.

Code can be added for page breaks but I was having some problems with that so just
thought I'd post this in the meantime.

Option Explicit

Sub CSVToXLS()
Dim wsData As Worksheet
Dim wsDst As Worksheet
Dim rngSrc As Range
Dim rngDst As Range
Dim I As Long

    Set wsData = Worksheets("Data in CSV Format(Original)")

    Set wsDst = Worksheets.Add

    Set rngSrc = wsData.Range("A1")
    Set rngDst = wsDst.Range("A4")

    While rngSrc.Value <> ""

        rngDst.Value = "STATE: " & rngSrc.Offset(, 5)

        rngDst.Offset(1).Value = "AIR CARRIER: " & rngSrc.Offset(, 1)

        rngDst.Offset(2).Value = "YEAR ENDED: " & rngSrc.Offset(, 6)

        rngSrc.Offset(, 3).Resize(2, 2).Copy rngDst.Offset(4)

        rngSrc.Offset(3, 3).Resize(2, 2).Copy rngDst.Offset(6)
        rngSrc.Offset(7, 3).Resize(2, 2).Copy rngDst.Offset(8)

        With rngDst.Offset(4).CurrentRegion
            .Interior.ColorIndex = xlNone
            .BorderAround xlContinuous, xlThin, 0
            With .Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = 0
            End With
            With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = 0
            End With
        End With
                
        Set rngSrc = rngSrc.Offset(9)
        Set rngDst = rngDst.Offset(21)
    Wend

    wsDst.Range("A:B").EntireColumn.AutoFit

End Sub

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

Expert Comment

by:Norie
ID: 38411001
This does the page breaks and adds the header.
Option Explicit

Sub CSVToXLS()
Dim wsData As Worksheet
Dim wsDst As Worksheet
Dim rngSrc As Range
Dim rngDst As Range
Dim I As Long

    Set wsData = Worksheets("Data in CSV Format(Original)")

    Set wsDst = Worksheets.Add

    Set rngSrc = wsData.Range("A1")
    
    Set rngDst = wsDst.Range("A4")

    While rngSrc.Value <> ""
    
        With rngDst.Offset(-3).Resize(3)
            .Value = Application.Transpose(Array("INTERNATIONAL CIVIL AVATION ORGANIZATION", _
                           "AIR TRANSPORT REPORTING FORM D", _
                           "FLEET AND PERSONNEL - COMMERCIAL AIR CARRIERS"))

            .Resize(, 6).HorizontalAlignment = xlCenterAcrossSelection
        End With

        rngDst.Value = "STATE: " & rngSrc.Offset(, 5)

        rngDst.Offset(1).Value = "AIR CARRIER: " & rngSrc.Offset(, 1)

        rngDst.Offset(2).Value = "YEAR ENDED: " & rngSrc.Offset(, 6)

        rngSrc.Offset(, 3).Resize(2, 2).Copy rngDst.Offset(4)

        rngSrc.Offset(3, 3).Resize(2, 2).Copy rngDst.Offset(6)
        rngSrc.Offset(7, 3).Resize(2, 2).Copy rngDst.Offset(8)

        With rngDst.Offset(4).CurrentRegion
            .Interior.ColorIndex = xlNone
            .BorderAround xlContinuous, xlThin, 0
            With .Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = 0
            End With
            With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = 0
            End With
        End With

        Set rngSrc = rngSrc.Offset(9)
        Set rngDst = rngDst.Offset(21)
        
        wsDst.HPageBreaks.Add rngDst.Offset(-3)
        
    Wend

    wsDst.Range("A:B").EntireColumn.AutoFit

End Sub

Open in new window

0
 

Author Comment

by:James0903
ID: 38411239
Imnorie,

This is awesome!  Looking great!

Two more requests:
1.  --And I apologize big time for this one--now I have asked to have all fields to appear on the table, not just the ones highlighted in green.  
2.  Is there a way to get the airline code in column A to appear after the Air Carrier Name on the table, such as:   Air Carrier Name: United Airlines (UA)

Thnak you very much!
0
 
LVL 36

Accepted Solution

by:
Norie earned 2000 total points
ID: 38411267
Not quite sure what you mean by 1.

Is it that you want to revert to the 9 rows of data rather than just the 6?

2 can easily be done.

This code does the 9 rows and adds the airline code.
Option Explicit

Sub CSVToXLS()
Dim wsData As Worksheet
Dim wsDst As Worksheet
Dim rngSrc As Range
Dim rngDst As Range
Dim I As Long

    Set wsData = Worksheets("Data in CSV Format(Original)")

    Set wsDst = Worksheets.Add

    Set rngSrc = wsData.Range("A1")
    
    Set rngDst = wsDst.Range("A4")

    While rngSrc.Value <> ""
    
        With rngDst.Offset(-3).Resize(3)
            .Value = Application.Transpose(Array("INTERNATIONAL CIVIL AVATION ORGANIZATION", _
                           "AIR TRANSPORT REPORTING FORM D", _
                           "FLEET AND PERSONNEL - COMMERCIAL AIR CARRIERS"))

            .Resize(, 6).HorizontalAlignment = xlCenterAcrossSelection
        End With

        rngDst.Value = "STATE: " & rngSrc.Offset(, 5)

        rngDst.Offset(1).Value = "AIR CARRIER: " & rngSrc.Offset(, 1) & " (" & rngSrc.Value & ")"

        rngDst.Offset(2).Value = "YEAR ENDED: " & rngSrc.Offset(, 6)

        rngSrc.Offset(, 3).Resize(9, 2).Copy rngDst.Offset(4)

        'rngSrc.Offset(3, 3).Resize(2, 2).Copy rngDst.Offset(6)
        'rngSrc.Offset(7, 3).Resize(2, 2).Copy rngDst.Offset(8)

        With rngDst.Offset(4).CurrentRegion
            .Interior.ColorIndex = xlNone
            .BorderAround xlContinuous, xlThin, 0
            With .Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = 0
            End With
            With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = 0
            End With
        End With

        Set rngSrc = rngSrc.Offset(9)
        Set rngDst = rngDst.Offset(27)
        
        wsDst.HPageBreaks.Add rngDst.Offset(-3)
        
    Wend

    wsDst.Range("A:B").EntireColumn.AutoFit

End Sub

Open in new window

0
 

Author Closing Comment

by:James0903
ID: 38413545
EXCELLENT!  Thank you very much.  Exactly what I needed!  This is a great time saver.  I will have more coming in the near future so maybe we will have the chance of working together again.  Thank you!
0
 

Author Comment

by:James0903
ID: 38441335
Imnorie,

Would you be able to help with this request?  You did a great job on this original request and you have the understanding of what I am trying to accomplish.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27879206.html

Thank you very much,
James
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question