asked on

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 ?

dlmille

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

Bill Prew

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

villartech

ASKER

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

Bill Prew

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

villartech

ASKER

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

dlmille

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

villartech

ASKER

I get Runtime error 9 - subscript out of range, with the following line of code:
Set mySheet = ThisWorkbook.Sheets("Output") 'setup output sheet

dlmille

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

villartech

ASKER

OK thanks the import worked but it is not adding leading zeros to the fields that need it.

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

villartech

ASKER

Worked great.
Thanks for the help.