Import Text (CSV) Type Conversion Failure

[obiron]

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: (e-mail address removed)

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

 

[joppenheim via AccessMonster.com]

obrion, without going into a whole lot of explaining. I had problems similar
to yours. I couldn't seem to get the "pre-defined" table to match the data
being imported. So, I resolved the problem this way. I DID NOT CREATE THE
TABLE FIRST!, I simply let the import create the table, let it define what it
things should be there, then modify from that point on. Worked great and
saved me from going crazy. Hope this works for you.

/Jeff

 

[obiron]

Jopp,

thanks for the reply, I actually managed to fix it by doing a manual
import and saving the schematics using the Advanced tab as 'SHIM'
(Sales History IMport). I then added that as the second argument in
the acImportDelim function and it worked fine.

DoCmd.TransferText acImportDelim, "SHIM" , "SalesHistory",
StrImportFile,-1, , 0


Obiron