Input # Problem

[blackworx]

Hi folks,

I am maintaining a program which reads through a pre-prepared CSV file using Input #. The first line of the CSV file contains field names, one of which has recently been changed from "Protection Type" to "1st Protection Type". For some reason VBA simply returns the number 1 instead of the full string.

I find this strange, since within the subsequent data lines there are many alphanumeric fields which begin with numbers and they are all read correctly.

Before I start making wholesale changes to the software (plan is to either use Line Input # and parse each line manually, or import the entire CSV file and move the data using an array) does anyone know if there is a way around this behaviour?

Cheers

[Andy Pope]

can you show some of the code you actually use, including variable declaration.

[blackworx]

Hi Andy,

Was loath to do so, simply for embarrassment's sake as it's someone else's code and quite shoddy! I'm not entirely sure why they've done it this way instead of just importing the file, but it's part of a very large project with many global variables, some of which are set by this Sub. Further data is added by other subs.

Also, a slight change to my OP... It turns out the field in question ("1st Protection Type") isn't being read simply as the number 1, but being split into two parts - the first containing "1" and the second containing "Protection Type", with the interceding "st " being treated as if it were a delimiter.

My comments (referenced in the code)

1: "pathIn" is a global variable set at startup which points to a source directory
2: "shtS.getVal" is a Function which returns a filename string from a table (contained on that sheet)
3: "tRowMax" & "tColMax" are global variables which retain the row/column extent of the imported data, including title rows.

Private Sub importMain()

Dim tRow as Long      'worksheet row
Dim tCol as Integer   'worksheet column
Dim cTitle as String  'field names
Dim cData as String   'data fields

Open pathIn & "\" & shtS.getVal("FileIn_Pay_Main") For Input As #1 'see my comments 1 & 2

tRow = 2: tCol = 0

Input #1, cTitle

Do

    tCol = tCol + 1
    shtM.Cells(1, tCol) = "main"
    shtM.Cells(2, tCol) = cTitle
    Input #1, cTitle
    
Loop Until cTitle Like "E???????" 'reached end of title row, have hit first pay number

tColMax = tCol 'see my note 3
cData = cTitle

Do

    tRow = tRow + 1

    sbUpdate Int(4 + tRow * (9 / countMain)), 58 'updates progress indicator on application statusbar
    
    For tCol = 1 To tColMax
        If tCol = 4 Then cData = Format(cData, "00")
        shtM.Cells(tRow, tCol) = cData
        If Not EOF(1) Then Input #1, cData
    Next tCol
    
Loop Until EOF(1)

Close #1

tRowMax = tRow 'see my note 3

End Sub

As I said before, the ONLY thing which has changed is the source data.

Sample source data (BEFORE the change):

Pay Number,Group Code,Pay Point,Forename,Surname,Staff Category,Post Descriptor,Full Pay Scale,Title,Call Out Rate,Rota Number,Enh.%,On Call Indicator,On Call %,Protection Type,Whole Part Time,Contracted Hours,Rota Type,Job Title/Description,Record Status,Superannuation Code,Superannuation Group,Initials
E1013890,HI,16,Joe,Bloggs,M,GROMMET BOTHERER,999M,GROMMET BOTHERER PROBATIONER,0.00,0.00,0.00,#EMPTY,0.00,#EMPTY,W,37.5,#EMPTY,GROMMET BOTHERER PROBATIONER,0,03,02,#EMPTY
E1013904,HI,19,Jane,Bloggs,M,WIDGET TECHNICIAN,304M,WIDGET TECHNICIAN PROBATIONER,0.00,3.00,0.00,A,9.5,#EMPTY,W,37.5,O,WIDGET TECHNICIAN PROBATIONER,0,03,02,#EMPTY

Sample source data (AFTER the change):

Pay Number,Group Code,Pay Point,Forename,Surname,Staff Category,Post Descriptor,Full Pay Scale,Title,Call Out Rate,Rota Number,Enh.%,On Call Indicator,On Call %,1st Protection Type,Whole Part Time,Contracted Hours,Rota Type,Job Title/Description,Record Status,Superannuation Code,Superannuation Group,Initials
E1013890,HI,16,Joe,Bloggs,M,GROMMET BOTHERER,999M,GROMMET BOTHERER PROBATIONER,0.00,0.00,0.00,#EMPTY,0.00,#EMPTY,W,37.5,#EMPTY,GROMMET BOTHERER PROBATIONER,0,03,02,#EMPTY
E1013904,HI,19,Jane,Bloggs,M,WIDGET TECHNICIAN,304M,WIDGET TECHNICIAN PROBATIONER,0.00,3.00,0.00,A,9.5,#EMPTY,W,37.5,O,WIDGET TECHNICIAN PROBATIONER,0,03,02,#EMPTY

[Andy Pope]

The code works for me. 1st Protection Type is in O2.

[blackworx]

In that case I inadvertently fixed it when I added the variable declarations at the top of the Sub. These were not there before, so cTitle was being initialised as a Variant, presumably causing the problem.

Thanks for your time, and sorry to have wasted it!

[Andy Pope]

Yep, if I make it a variant it does as you described.

Not a waste, it's why I asked you to post the code as I thought variables might be the problem.

[blackworx]

That's true, thanks again!