Strange problem importing a csv to a recordset

Question

I'm trying to import a csv into a recordset. I've been using code kindly provided by a stackoverflow user here and it worked well for a long time.

Have started to get a bizarre error recently though. In all cases the file is opened and read without throwing any errors.

SYMPTOMS (AFTER LOTS OF TESTING!)

Certain rows are SOMETIMES imported with a given (large) field blank (text in bold below isn't imported)

£0.00,0.00,£0.00,£0.00,,,,,,,"Birmingham - OCW1","N221A-BIR","Switch",0,,,04/02/2011 14:16:00,0," Job Updated On 04/02/2011 05:50 by James Helanor.

Job Updated On 04/02/2011 08:02 by Scott Murgatroyd.

Job Updated On 04/02/2011 10:05 by Scott Murgatroyd.

Job Updated On 04/02/2011 11:10 by Sean Hatherley.

Job Updated On 04/02/2011 11:55 by Dawn Marie.

Job Updated On 04/02/2011 12:00 by Sean Hatherley.

Job Updated On 04/02/2011 13:13 by Sean Hatherley.

Job Despatched On 05/02/2011 06:03 by James Helanor with Risks : ,

Job Updated On 08/02/2011 14:57 by Sean Hatherley.

Job Updated On 08/02/2011 14:59 by Sean Hatherley.

Job Updated On 09/02/2011 08:26 by O2 Engineer 2.

Job Updated On 10/02/2011 08:47 by Sean Hatherley.

Job Updated On 15/02/2011 10:48 by Sean Hatherley.

Job Updated On 18/02/2011 10:25 by Sean Hatherley.

Completed on 24/02/2011 11:27 by Sean Hatherley

Reinstated (Reactive hasn't been completed) on 11/03/2011 15:56 by O2 Engineer 2 ---- 11/03/2011 15:53 By O2 Engineer 2:** Case to remain open** Job Updated On 21/03/2011 07:29 by O2 Engineer 2. ",,,,"JAMES","P1 Out of Hours Emergency",£0.00,0.00,£0.00,0.00,"Issued",04/02/2011 05:26:40,0.00,,,0,,04/02/2011 05:26:00,"C63024","Adams alarm - 9th floor EMX AHU 4 Prompt","OOH Property","Priority 1",1,-1,,,,"Helpdesk",04/02/2011 05:26:00,"no","F80-03-03","01753 564 222",04/02/2011 11:30:00,04/02/2011 06:56:00,,,"In Progress","BTW-OOH","BT Wholesale - S","---- 04/02/2011 05:50 By James Helanor: Called BT to advise of fault - given ref of TH32330 - manual text sent to advise of fault although no eta yet ---- 04/02/2011 08:02 By Scott Murgatroyd: Job passed through to BT eng, updating text sent ---- 04/02/2011 10:05 By Scott Murgatroyd: BT Engineer to attend, updating text sent ---- 04/02/2011 11:10 By Sean Hatherley: As per BT engineer, he will be onsite in 20 minutes. ---- 04/02/2011 11:53 By Dawn Marie: update from Babu Patel 07711640502 BT engineer is on site the unit has tripped on low pressure, Babu is trying to find refrigerant leak but this may take a few days to find. ---- 04/02/2011 11:59 By Sean Hatherley: Rang Adam Elvidge, he advised to leave this as a P1 and to chase daily, not 4 hourly. ---- 04/02/2011 13:12 By Sean Hatherley: Text sent ---- 08/02/2011 14:57 By Sean Hatherley: BT are reattending 09/02/11 to rectify low pressure fault. Rang Adam Elvidge to see if it can be downgraded. ---- 09/02/2011 08:25 By O2 Engineer 2:Adam Elvidge- Case to remain as a P1 ---- 10/02/2011 08:44 By Sean Hatherley: Chased with BT, they have no further updates yet but are passing it onto their engineer to ring back. ---- 15/02/2011 10:47 By Sean Hatherley: Chased with BT, they have no further updates. ---- 18/02/2011 10:24 By Sean Hatherley: Rang BT, went to overflow office and they will chase the engineer and ring back with an update as it is still open on their system. ---- 24/02/2011 11:27 By Sean Hatherley: Alarm now cleared on Adams, job closed ---- 11/03/2011 15:54 By O2 Engineer 2: Gas leak hann't been repaired, awaiting an update from the BT area manager John Greenfield. ---- 21/03/2011 07:28 By O2 Engineer 2: Case to be left open, awaiting reply from BT on current status. Case to remain open",,,,,

The cause for this seems to be at least one other row in the CSV:

£0.00,0.00,£0.00,£0.00,,,,,"AF PO RAISED","Purchase order raised","Croydon - OCW1","N221A-CRO","Switch",,,,08/03/2011 11:32:00,0," Job Despatched On 08/02/2011 11:32 by Sarah Northveth with Risks : ,

Job Updated On 09/02/2011 07:56 by Ann Farish.

Job Despatched On 09/02/2011 07:57 by Ann Farish with Risks : ,

Job Updated On 09/02/2011 09:03 by Priya Mistry.

Job Updated On 15/03/2011 15:21 by Sean Hatherley.

Job Despatched On 15/03/2011 15:21 by Sean Hatherley with Risks : ,

Job Updated On 15/03/2011 15:28 by Sean Hatherley.

Job Despatched On 15/03/2011 15:29 by Sean Hatherley with Risks : , ",,,"02/12980","SARAHN","P4 CWP Engineer to carry out Project Works",£0.00,0.00,£0.00,0.00,"Issued",08/02/2011 11:32:06,0.00,,,0,,08/02/2011 11:32:00,"C63448","replace fans on UPS System","ISS-Engineer","Priority 4",4,-1,,,,"Hilton Gumbs",08/02/2011 11:32:00,"Yes","Engineer","07702368336",,08/03/2011 11:32:00,,,"In Progress","CWP-ENG-HGUMBS","Hilton Gumbs - D","_---- 09/02/2011 07:56 By Ann Farish: PO Has been requested for emerson to replace fan by Hilton gumbs ---- 09/02/2011 09:03 By Priya Mistry: po approved and sent out ---- 15/03/2011 15:21 By Sean Hatherley: Emailed Hilton for update ---- 15/03/2011 15:28 By Sean Hatherley: Hilton update: UPS fans due to be replaced on 07/04/2011

_",£0.00,"ENG-CRO-EAL","HILTON GUMBS",30/12/1899 00:00:00,

If I include this row in the file to be imported the text in bold above disappears, If I leave this row out it imports fine.

Here's the weird bit, the outcome seems to depend on the position of the above row and how many other rows are included.

If I import just these two rows everything works fine, If I import both rows with lots of others they import as long as the second row is in the second half of the text file

Even weirder, if I reduce the field value marked in italic to just under 244 characters everything works.

Is this something to do with the recordset making assumptions about what type of data it's handling? Any thoughts very welcome.

Thanks

Answer 1

Fixed it!

As I understand it, the system will make an educated guess at the data types of the csv and in my case was getting it wrong.

Turns out I needed a schema.ini file (in the same folder as the csv) to specify the data type of each column along the lines of:

[file.txt]
Format=CSVDelimited
CharacterSet=ANSI
ColNameHeader=True
Col1=ActLabCost text
Col2=ActLabTime text
Col3=ActMatCost text
...

I set the column type of the problem column to LongChar and all seems to be working.