Issue with file parsing

Hello, I have a weird bug when I attempt to parse output files from a visionmate. The output file is simple and has 4 columns but the column with the tube barcodes will break sometimes and I can’t figure out what’s triggering it, but it happens about half the time.

Each barcode is a string that begins with A or K and then 7 digits. When it breaks it will remove the letters and sometimes just make certain barcodes empty. I was troubleshooting for a while thinking it was my HSL parse function that was messing it up until I finally tried it using the Venus File Open and saw the same issue.

Error_2
Error_1

The CSV for plate 1 that breaks:
LocationColumn,LocationRow,TubeCode,RackID
1,A,K0182675,TS02089395
1,B,K0169989,TS02089395
1,C,K0173569,TS02089395
1,D,K0173585,TS02089395
1,E,A0009592,TS02089395
1,F,K0182764,TS02089395
1,G,K0169910,TS02089395
1,H,K0182753,TS02089395
2,A,K0169971,TS02089395
2,B,K0172834,TS02089395
2,C,K0175368,TS02089395
2,D,K0170809,TS02089395
2,E,K0173644,TS02089395
2,F,A5132811,TS02089395
2,G,A5209599,TS02089395
2,H,K0174402,TS02089395
3,A,K0182694,TS02089395
3,B,K0175311,TS02089395
3,C,K0175367,TS02089395
3,D,K0174404,TS02089395
3,E,K0174394,TS02089395
3,F,No Tube,TS02089395
3,G,No Tube,TS02089395
3,H,No Tube,TS02089395

It does not have the same issue when you export as an xls, but the issue there is my understanding is that Hamilton requires a worksheet name to open, and the visionmate exports the xls with the plates rack ID as the worksheet name so it’s variable.

Has anyone experienced a similar issue before? And is there a workaround for opening an xls without the worksheet name?

Hi Don,

Because you are using a structured text file, if the name and file path of the CSV you are providing to the method are being reused from run to run, one likely possibility is that a ‘schema.ini’ file may already exist for the CSV file used. If this file already exists, then the driver VENUS uses to process structured text files will apply whatever schema was appropriate for a previous file to the current file, which can cause issues of this nature. Check these posts for more context:

https://forums.pylabrobot.org/t/issues-with-newly-installed-venus/1249/5?u=nickhealy_hamilton

https://forums.pylabrobot.org/t/sql-open-file-error/2267/4?u=nickhealy_hamilton

When processing structured text files using VENUS, it is best practice to check for the presence of a schema.ini file in the directory that the CSV file is stored (this is where the corresponding schema.ini gets generated by the connection driver) and delete it.

Check this quick programming example that does this (requires use of the File library from HSL extensions mentioned and linked all over the VENUS section of the forum):

Hope this helps. Thanks.

-Nick

3 Likes

I’ve had some baffling parsing issues that had similar symptoms - under certain conditions I would end up with empty positions built from a CSV. I’ll have to try Nick’s schema.ini check at some point, but for what it’s worth I ended up getting around it by writing my own CSV parsing smt.

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 not use the TypeGuessRows function and can be used to both read or write to a file.