File:Open detects only integers when set to string

[EH421]

I’m reading in a .csv with File:Open and the column SourceBarcode contains both strings and integers. I’ve set the File:Open to read this column in as a string but when I retrieve column specification for the .csv, it seems to not read in the “NA” string and only read the numerical values. Does anyone know why this happens?

Here is how my File:Open is set up. I updated this to use a comma delimiter after I notice it was set to a “;” but that didn’t seem to change anything.

Here is retrieve column specifiation data

Here is my .csv file that is being read in

[EH421]

Additional information after more testing

I think this issue has to do with the number of “NA” strings before we reach the cells with serial numbers (int). Through the process of elimination I have found that having <= 12 “NA” prior to a serial number in the csv will result in the File:Open seeing the NA as blank or null, but if I have 13 “NA” prior to a serial number, then File:Open works as expected and reports both the “NA” and serial number in the retrieve column specification. Seems really odd.

12 NAs prior to serial numbers

13NAs prior to serial numbers is able to read the NA strings.

Edit:
I compared the schema.ini files generated between the 12 NA and 13 NA and they are identical so I don’t suspect the schema.ini as the culprit.

[cwehrhan]

Hey,

I think it is related to this:

Issue with file parsing

This is a pretty old thread but the blanks when importing a mixed worklist is from Microsoft jet engine trying to guess the type of data in the file, by default it only looks at the first 8 rows I believe. This is turned on when you import mixed types. By changing the registry here: Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Excel. Then setting: TypeGuessRow from 8 to 0 This should allow you to import mixed types without blanks. Also FYI the Append Mode does …

Try append mode and see if it fixes it; then if you want change the registry and it should work in read mode as well.

[EH421]

Thank you, setting it to append did fix the issue, although I would prefer it not try to guess the type by default. I will look into changing the registry and if that is something I can implement in the future.

[cwehrhan]

I believe this is fixed in Venus6 as they are using a newer excel engine, could be wrong though, haven’t fully tested.

lol its a mind bending bug, wasted so much time on it many moons ago