Hi guys, I hope you can help.

I am importing a CSV format file into a predefined table (all text
columns)

The file is auto generated so I have no control over the format.  It
contains:-

A header line with the field headings - which match the table field
names
a blank line
An undertermined number of data records, all with the correct number of
fields.  All fields are separated by commas and surrounded by quotes
A blank line
two summary lines that provide details of the file contents and when it
was generated.  These two lines are comma separated but not surrounded
by quotes

When I import the data using the wizard, all the lines come in
correctly.  When I import the data using code (docmd.TransferText
acImportDelim) I get a type conversion error in the first data line
because the 4th field of the second to last line contains a date. (the
date imports correctly).  I have a couple of questions about this.

1.  Why does it import correctly using the wizard if the data is
inconsistent
2.  Why does it reject the text data but import the date data when it
is the date data that is in the wrong data type
3.  What is the easiest way to fix this apart from edit the file... :)

My thoughts on fixing the problem are to either import all the data as
text strings into a different file and use the replace option to force
quotes around all the data fields or to open the text file and read
each line and save it to a different file if I need to import it;  I
dont need the summary or blank lines, I currently delete them from the
imported table if they are there.

I have included a sample import file and a copy of the sub that I am
running.
File

Sales Record Number,User ID,Buyer Fullname,Buyer Address 1,Buyer
Address 2,Buyer City,Buyer State,Buyer Postcode,Buyer Country,Item
Number,Item Title,Quantity,Sale Price,Included VAT Rate,Postage and
Packing,Insurance,Total Price,Payment Method,Sale Date,Checkout
Date,Paid on Date,Dispatch Date ,Invoice date,Invoice number,Feedback
left,Feedback received,Notes to yourself

"536","mitsystems","MiT Systems","110 Ballyards
Road","Lislea","Armagh","Armagh","BT60 3JH","United
Kingdom","","","2","£12.48","0%","£2.95","£0.00","£15.43","PayPal","06-Mar-06","06-Mar-06","06-Mar-06","","","","","",""
"536","mitsystems","","","","","","","","5670000262","3Com Office
Connect 16 port
hub","1","£4.99","","","","","","03-Mar-06","","","","","","No","",""
"536","mitsystems","","","","","","","","5670000379","3Com Office
Connect 16 port
hub","1","£7.49","","","","","","04-Mar-06","","","","","","No","",""
"537","garo603","","","","","","","","5670000472","3Com Office Connect
16 port
hub","1","£4.99","","£2.95","£1.00","£7.94","","06-Mar-06","","","","","","No","",""

2, record(s) downloaded,from ,03-Mar-06,14:45:14, to
,06-Mar-06,14:45:08
Seller ID: aaron.reese@tiscali.co.uk

and the sub:

Public Sub PR_RunImport()
   Dim StrImportFile As String
   StrImportFile = "C:\Documents and
Settings\Office\Desktop\saleshistory2.csv"

   DoCmd.TransferText acImportDelim, , "SalesHistory", StrImportFile,
-1, , 0
   DoCmd.OpenQuery "QD_01_delete_non_user_IDs", acViewNormal, acEdit
   DoCmd.OpenQuery "QD_02_delete_recordsdownloaded", acViewNormal,
acEdit
   DoCmd.OpenQuery "01_QU_Strip Quotes", acViewNormal, acEdit
   DoCmd.OpenQuery "02_QU_Uppercase", acViewNormal, acEdit
   DoCmd.OpenQuery "03_QU_spaces_from_postcodes", acViewNormal, acEdit
   DoCmd.OpenQuery "04_QU_spaces_back_in_postcodes", acViewNormal,
acEdit
End Sub

Thanks in advance folks

Obiron