[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Import text file into Excel

I need to import a comma delimited text file into an Excel 2007 spreadsheet.
Field are account number,serial number,check amount,check issue date,additional data,void indicator,payee name (1st line), payee name (2nd line), filler.
I need to count the total characters from each line of text and add a certain number of spaces to the filler cell on the spreadsheet (record size for all row data must be 160 characters).
Can you provide some vb macro code to do this ?

I aslo need to add leading zeros to all numeric fields.
Can I just format the Excel cells for that function or would the import overwrite the cell formats ?
0
villartech
Asked:
villartech
  • 5
  • 4
  • 2
1 Solution
 
dlmilleCommented:
Provide a non-sensitive sample dataset to simplify the work on this end.   This appears to be straight-forward, but with your sample file, it will be much easier.

Cheers,

Dave
0
 
Bill PrewCommented:
Might be helpful to understand what you are trying to do with the data in Excel.  If you just need to convert the CSV file into a fixed width and length TXT file they that could all easily be done in a single VBS script without getting Excel involved.

~bp
0
 
villartechAuthor Commented:
I need a VBS macro that I can run from Excel to import csv data into Excel.
I have attached a sample text file to be imported.
 test.txt
0
Technology Partners: 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!

 
Bill PrewCommented:
I suspect dlmile is handier at that than I, so I'll probably wait a bit and see what he posts up.

I think there is another question to be answered though.  You mention that you want to total number of characters to be 160 and want a "filler" column added with spaces to get to that length.  For the numeric fields that you want zero padded, what length do you want those fields padded to?  And in tallying toward the 160, is it correct that you would want to use their length AFTER padding with leading zeros, not before?

And blank fields in the input CSV count as 0 length in the tally toward 160, yes?

~bp
0
 
villartechAuthor Commented:
account number = 13
serial number = 10
check amount = 10
check issue date = MMDDYY
additional data = 15
void indicator = 1
payee name (1st line) = 40
payee name (2nd line) = 40
filler = maximun of 25

blank characters count as 1
0
 
dlmilleCommented:
Sorry for the long wait on response - I had some other things going on over the weekend.....

Ok - app prompts for Text file, then reads in each line, line by line.  The line is parsed for comma separation, assuming format as you've specified.  At the end of each line, the filler (# blank characters = 160 less the total characters for all fields to that point) is generated.

Here's the code:
 
Sub ReadTXTAndProcess()
Dim mySheet As Worksheet
Dim myCell As Range
Dim fName As String
Dim wholeLine As String
Dim varInput As Variant
Dim outCursor As Range
Dim i As Long, colonPos As Long
Dim header As Variant
Dim processLine As Variant
Dim lenOutput As Long
Dim maxLine As Long

    Set mySheet = ThisWorkbook.Sheets("Output") 'setup output sheet
    Set outCursor = mySheet.Range("A1") 'where to start writing output
    
    maxLine = 160
    
    header = Split("Account_Number,Serial_Number,Check_Amount,Check_Issue_Date,Additional_Data,Void_Indicator,Payee_Name(1st),Payee_Name(2nd),Filler", ",")
    
    mySheet.Cells.Clear 'clears the output tab
    
    outCursor.Resize(, UBound(header) + 1).Value = header 'create header in output sheet
    
    
    Set outCursor = outCursor.Offset(1, 0) 'prepare for first line of output
    
    
    fName = Application.GetOpenFilename(filefilter:="Text Files (*.txt), *.txt", MultiSelect:=False)
    If fName = "False" Then Exit Sub
    
    Open fName For Input As #1 'read the text file line by line
    
    While Not EOF(1) 'read each line until end of file
        Line Input #1, wholeLine 'puts each line into the string variable wholeLine
        'processing of wholeLine commences, here...

        processLine = Split(wholeLine, ",") 'get each field, based on comma delimiter
        
        For i = 0 To UBound(processLine) 'for each element in each record, output padded results
            Select Case i:
                Case 0: 'Account_Number
                    outCursor.Offset(0, i).Value = Format(processLine(i), WorksheetFunction.Rept("0", 13))  '13 characters, 0 padded
                Case 1: 'Serial_Number
                    outCursor.Offset(0, i).Value = Format(processLine(i), WorksheetFunction.Rept("0", 10))  '10 characters, 0 padded
                Case 2: 'Check_Amount
                    outCursor.Offset(0, i).Value = Format(processLine(i), WorksheetFunction.Rept("0", 10))  '10 characters, 0 padded
                Case 3: 'Check_Issue_Date
                    outCursor.Offset(0, i).Value = Format(processLine(i), "MMDDYY")  '6 characters, MMDDYY
                Case 4: 'Additional_Data
                    outCursor.Offset(0, i).Value = Format(processLine(i), WorksheetFunction.Rept("0", 13))  '10 characters, 0 padded
                Case 5: 'Void_Indicator
                    outCursor.Offset(0, i).Value = Format(processLine(i), "0")  '1 characters, 0 padded
                Case 6: 'Payee_Name(1st)
                    outCursor.Offset(0, i).Value = processLine(i)  'no padding?
                Case 7: 'Payee_Name(2nd)
                    outCursor.Offset(0, i).Value = processLine(i)  'no padding?
                Case Else: 'Flag Warning
            End Select
            lenOutput = lenOutput + Len(outCursor.Offset(0, i).Value)
        Next i
        
        'Filler
        outCursor.Offset(0, UBound(header)).Value = WorksheetFunction.Rept(" ", maxLine - lenOutput) 'ensure total line length is maxLine - e.g., 160 characters
                
        Set outCursor = outCursor.Offset(1, 0) 'proceed to next output line
    Wend
    
    outCursor.Resize(, UBound(header) + 1).EntireColumn.AutoFit 'make pretty
    Close #1 'tidy up, closing the file

    MsgBox "Processing Complete!"
End Sub

Open in new window


See attached file - look at Output tab to see results of your test input file.

Cheers,

Dave
importTxtAndProcess-r1.xls
0
 
villartechAuthor Commented:
I get Runtime error 9 - subscript out of range, with the following line of code:
Set mySheet = ThisWorkbook.Sheets("Output") 'setup output sheet
0
 
dlmilleCommented:
Did you move the code to another workbook?  Did you try running it from this workbook?

The reason you're getting the error is the code cannot find the Output sheet tab in the workbook.  If you deleted the tab, you'll need to re-create it.  If you moved the code, you'll need to make sure your workbook has the Output sheet tab.

Let me know.

Dave
0
 
villartechAuthor Commented:
OK thanks the import worked but it is not adding leading zeros to the fields that need it.
0
 
dlmilleCommented:
Apologies.  All fields are now formatted for text format - leading zeros, etc., will be there with appropriate spacing.

Dave
importTxtAndProcess-r2.xls
0
 
villartechAuthor Commented:
Worked great.
Thanks for the help.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now