Imported CSV data once, column recognized as integer; now they're text. Why did it change?-CodePudding

I've been working on a little VBA project; after importing data from a CSV file, I have VBA code that does things with it. I've written a bit of VBA code, but it's not my primary programming language.

The CSV file has two lines of text, then a line of text headers

"Name","Gender","18 Hole Index", ... ,"Total Slope","Total Par","Total Course Hdcp"

Then data:

"Doe, John","Male","23.45", ... ,116,71,18

I imported this through the Data,text/csv menu and got what I expected, including a column of integers under the last column.

I spent several days on my little VBA project and got it working.

But now, when I import the CSV file with the same characteristics as the first one I outlined above, it imports the last column as TEXT, and of course the VBA code doesn't work any more.

I can figure out how to convert it (eventually, VBA doesn't make that straightforward, different topic), but I want to know how I can do the import so that conversion is not necessary. I figure it's possible somehow, because it didn't require any conversion the first time. I did not do anything fancy on the original import; I did not do any transformations (I would have to look up how), I just imported data and this column was there as Integer data. What could have changed how that got imported, and how do I get back the original behavior?

EDIT: first 8 lines of data:

"Player Course Handicap"
" "
"Name","Gender","18 Hole Index","Course","Tees","Front Rating","Front Slope","Front Par","Front Course Hdcp","Back Rating","Back Slope","Back Par","Back Course Hdcp","Total Rating","Total Slope","Total Par","Total Course Hdcp"
"Doe, John","Male","23.45","River Ridge","Gold","33.6",115,36,10,"33.3",122,36,10,"66.9",119,72,20
"Doe, Jack","Male","20.78","River Ridge","Gold","33.6",115,36,8,"33.3",122,36,9,"66.9",119,72,17
"Doe, Jacques","Male","1.71","River Ridge","White","34.3",121,36,-1,"34.3",128,36,-1,"68.6",125,72,-2
"Doe, Jane","Male","25.07","River Ridge","Red","32.4",113,36,9,"31.7",111,35,9,"64.1",112,71,18
"Doe, Albert","Male","26.08","River Ridge","Gold","33.6",115,36,11,"33.3",122,36,11,"66.9",119,72,22

The actual data has actual names and goes on for 60-some lines. In both cases, the last column shows up as text, not integer.

CodePudding user response：

Found the anomaly.

If you OPEN the CSV with Excel (just double-click the CSV file to open it), Excel transforms columns containing mostly digits to numbers.

If you open Excel and execute Data=>Get Data=>From Text/CSV, it does NOT transform such columns to numbers. I was writing my VBA based on a file I had opened, not imported, assuming that "data import" and "open file" would get the same results.

Now I wish there were some (simple) way to import the data and have Excel apply the same transforms on the import that it applies to data when reading the file.

I know one can alter the transforms done by the import, by clicking "Transform Data" on the import popup and replacing "text" with "number" in that transform formula or whatever it is. But I'm creating something to be used by people for whom that is much too sophisticated, and so in my situation it is better to take the default text import and convert it in the VBA.

Thanks to all who were looking into this with and for me.