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

x
?
Solved

Converting Text to Excel

Posted on 2007-08-07
9
Medium Priority
?
320 Views
Last Modified: 2013-11-05
Hey Guys,

I am trying to convert a text file into an excel spreadsheet.  I can get it converted, but it comes up not formatted and the text is everywhere.  Here is my code, but I need to add something to it so that it formats it across the cells.

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim sFilbane As String
        Dim sFilnavn As String
        Dim myXL As Object
        Dim myWkBk As Object
        Dim objRange As Object
        sFilbane = "C:\GiftCards\GiftCardFiles\"
        sFilnavn = "WILCORPT.txt"
        Dim splitt As Object = Split(sFilnavn, ".", -1)

        myXL = CreateObject("Excel.application")
        myXL.Visible = True
        myWkBk = myXL.Workbooks.Open(sFilbane & sFilnavn)
        objRange = myWkBk.worksheets(1).Range("A1").EntireColumn
        objRange.TextToColumns(objRange, , , , , , True)

        myXL.DisplayAlerts = False
        myWkBk.SaveAs(sFilbane & splitt(0) & ".xls", -4143)
        myWkBk.Close()
        myWkBk = Nothing
        myXL.Quit()
        splitt = Nothing
    End Sub
0
Comment
Question by:edfreels
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 27

Expert Comment

by:VBRocks
ID: 19646092
What do you mean by "the text is everywhere?"  Why does the text in WILCORPT.txt look like?
0
 
LVL 1

Author Comment

by:edfreels
ID: 19646149
I t looks like this in the text file


          CLIENT: WILCO PRE-PAID

          REPORT TITLE : Transaction Summary

          REPORT ID: RPT580B

          REPORT PERIOD : 07/18/2007 TO 07/18/2007

          TIME PRINTED: Thu Jul 19 01:38:23 2007 (Job# 5607)

          BIN RANGE: WILCO

               CREDITS

                      MERCHANT 000000 -
                           TRANSACTION TYPES                   ITEMS              USD AMOUNT                  AMOUNT
                          TOTAL CREDITS MERCHANT 000000            0                    $.00                    $.00

                      MERCHANT 061990 -
                           TRANSACTION TYPES                   ITEMS              USD AMOUNT                  AMOUNT
                          TOTAL CREDITS MERCHANT 061990            0                    $.00                    $.00

                      MERCHANT 066634 - Wilco Oil
                           TRANSACTION TYPES                   ITEMS              USD AMOUNT              USD AMOUNT
                            7 - Issue Gift Card                  112               $3,425.00               $3,425.00
                           13 - Reload/Recharge                    4                 $330.00                 $330.00
                           24 - Reversal                          44                 $412.82                 $412.82
                           61 - Mass Valuation                   105               $1,000.00               $1,000.00
                          TOTAL CREDITS MERCHANT 066634          265               $5,167.82               $5,167.82

                TOTAL CREDITS BINRNG WILCO                       265               $5,167.82


I need it to come out like this in Excel

ReportFile      ReportJobNumber      PeriodFrom      PeriodTo      BeginBalance      EndBalance      TotalCredits      TotalDebits      TransCode      TransType      Items      Amount
RPT580A      5645      22-Mar-07      22-Mar-07      339,493.70      336,719.50      3,114.88      5,889.01      7      Issue Gift Card                50      2,110.00
RPT580A      5645      22-Mar-07      22-Mar-07      339,493.70      336,719.50      3,114.88      5,889.01      13      Reload/Recharge                3      400.00
RPT580A      5645      22-Mar-07      22-Mar-07      339,493.70      336,719.50      3,114.88      5,889.01      24      Reversal                       55      554.88
RPT580A      5645      22-Mar-07      22-Mar-07      339,493.70      336,719.50      3,114.88      5,889.01      61      Mass Valuation                 5      50.00
RPT580A      5645      22-Mar-07      22-Mar-07      339,493.70      336,719.50      3,114.88      5,889.01      3      CAT Confirm                    178      2,425.33
RPT580A      5645      22-Mar-07      22-Mar-07      339,493.70      336,719.50      3,114.88      5,889.01      4      Tender/Redeem                  246      3,388.15

But straight across, here it has a return because of the text box size, but the line should go all the way across until the next RPT starts on a new line.
0
 
LVL 27

Accepted Solution

by:
VBRocks earned 900 total points
ID: 19646874
Ed, I don't think your current approach is going to work for you.  You'll have to actually open the file
up, parse it, and insert the data into the appropriate cell.  It's going to be a little work.

        'HERE IS A BRIEF EXAMPLE OF HOW TO PARSE SOMETHING FROM YOUR FILE
        '    AND INSERT IT INTO EXCEL.
        '
        '    You are going to have to put your head into it and parse the whole file, but you can do it!
        '
        Dim sFilbane As String
        Dim sFilnavn As String
        Dim myXL As Object
        Dim myWkBk As Object
        Dim myWkSht As Object
        Dim objRange As Object
        sFilbane = "C:\TEMP\"
        sFilnavn = "WILCORPT.txt"
        Dim splitt As Object = Split(sFilnavn, ".", -1)

        myXL = CreateObject("Excel.application")
        myXL.Visible = True
        myWkBk = myXL.Workbooks.Add
        myWkSht = myWkBk.Worksheets(1)

        Dim sr As New IO.StreamReader(sFilbane & sFilnavn)
        Dim inLine As String = String.Empty

        Dim iRowCounter As Int16 = 0
        Dim startIndex As Int16 = 0
        Do Until sr.EndOfStream = True
            inLine = sr.ReadLine()

            startIndex = inLine.IndexOf("REPORT ID:")

            'You want the Report ID in column A
            If startIndex > -1 Then
                'Increment Row counter
                iRowCounter += 1

                myWkSht.Range("A" & iRowCounter).value = _
                    inLine.Substring(startIndex + "REPORT ID:".Length + 1)

            End If

        Loop


        objRange = myWkBk.worksheets(1).Range("A1").EntireColumn
        objRange.TextToColumns(objRange, , , , , , True)

        myXL.DisplayAlerts = False
        myWkBk.SaveAs(sFilbane & splitt(0) & ".xls", -4143)
        'myWkBk.Close()
        'myWkBk = Nothing
        'myXL.Quit()
        'splitt = Nothing


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:edfreels
ID: 19646945
Thanks VBRocks,

I opened up the access file that is doing this now, it is faling but here is what I get in VB6 editor, would this help?

Private Sub Command59_Click()
Dim strPath As String
Dim strName580 As String
Dim strName590 As String
strPath = DLookup("[FilePath]", "tbl_Path", "[PathIdx]=2")
strName580 = strPath & "Rpt_580_" & Format(Date, "mm-dd-yy") & ".xls"
strName590 = strPath & "Rpt_590_" & Format(Date, "mm-dd-yy") & ".xls"
DoCmd.OutputTo acOutputQuery, "qry_580", acFormatXLS, strName580
DoCmd.OutputTo acOutputQuery, "Qry_590", acFormatXLS, strName590
MsgBox "Reports sent to " & DLookup("[FilePath]", "tbl_Path", "[PathIdx]=2")
0
 
LVL 3

Assisted Solution

by:Pigtor
Pigtor earned 600 total points
ID: 19647035
Because the data has in the text file has not the same order as the output excel file,  you must do it manually, moving through the worksheet and editing each cell with the corresponding value.

To make it simple, use RegularExpressions to detect each statement of your text file, and copy the results to the corresponding cell in Excel.

Copy and paste this example:

            Dim XLS As Excel.Application = New Excel.Application
            Dim WRK As Excel.Workbook = XLS.Workbooks.Add
            Dim Sheet As Object = WRK.Sheets.Add

            XLS.Cells(1, 1).Value = "ReportFile"
            XLS.Cells(2, 1).Value = "ReportJobNumber"
            Dim XlFormat As Excel.XlFileFormat = Excel.XlFileFormat.xlWorkbookNormal
            Try
                XLS.ActiveWorkbook.SaveAs("c:\report.xls", XlFormat, String.Empty, String.Empty, False, False, Excel.XlSaveAsAccessMode.xlNoChange)
            Catch e As Exception
                System.Windows.Forms.MessageBox.Show(e.Message, e.Source, MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
            WRK.Close()
            XLS.Quit()

I hope that helps
0
 
LVL 3

Expert Comment

by:Pigtor
ID: 19647184
To complement my last comment, the RegularExpressions will help you reduce the text file analisis.

For example, to detect the Report Title, use this expression:

TitlePattern = New System.Text.RegularExpressions.Regex("REPORT TITLE\s:\s.*\x0D\x0A", System.Text.RegularExpressions.RegexOptions.Multiline);
Match = TitlePattern.Match(data)
0
 
LVL 14

Expert Comment

by:wsh2
ID: 19647520
edfreels.. sorry, but you are between a rock and a hard place.

Text parsing the data is NOT going to be fun because the only delimiter you have is a space. The space is ambiguous though, as it is being used either legitimately as part of the description, or as a delimeiter between fields.. which is which is not easy to tell.

Yes, you can parse as suggested above.. but you could also consider.. grabbing the MsAccess data directly into your spreadsheet using the same query that the Access report did. Your data will come in cleanly and as an additional benefit, properly typed (string, date, number).. reducing the amount of effort you will need to rebuild the report format.

Whether you go with data parsing or data query, you are going to have to reconstruct the formatting of the report. In my opinion, using an Excel data query is far less work and will produce far more accurate results.

0
 
LVL 1

Author Comment

by:edfreels
ID: 19647640
Thanks Pigtor,

wsh2, what do you mean use an Excel Data Query?  You mean like a datatable in the application itself?
0
 
LVL 14

Expert Comment

by:wsh2
ID: 19648129
From the Excel Menu..

Tools -> Import External Data -> New Database Query..

Link to your MsAccess database and select the Query that the report uses to run. You will be asked where you want to put the data, how often the query should run, replace exisiting data.. etc. (I can't remember them all).

This will run the query and load all the same data the report gets into a spreadsheet. Using VBA copy the data to another spreadsheet.. and then try running of the pre-defined Excel report templates on it.. Or.. using VBA.. reformat the data yourself. IN all honesty, it is the same work you are going to have to do if you parse the data from text.. only less <wink>.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Video by: Zack
Viewers will learn about using Excel in a browser with Excel Online.
Viewers will learn the basics of using filtering and sorting in Excel 2013.

577 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