TransferText converting some CSV cells to blanks (A2003 with Excel 2003)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PW - 19 May 2008 03:10 GMT

Hi,

When I run the following command, some fields are ending up blank when
the clearly have values them in Excel. I have tried converting the
columns to general and text. The ones that are ending up blank (Null,
actually) contain:

lu24769884l
lu24769884xxl

Just text. Displayed the same value up top when I click on that cell
in Excel.

The values that do get transposed correctly are all numeric as
617867094518 but that one displayed as 6.17867E+1 in it's cell.
However, that cell gets converted properly into the Access table.

My transfertext code:

strFind = "[date] = #" & varFileDate & "# And [purchaser] = " &
intPurchaser

varReturnVal = DLookup("[date]", "tblInventoryPurchases", strFind)

DoCmd.TransferText acImportDelim, , "tblTempCashReg", strReturnVal, -1

This particular field in the Access table is formatted as text.

Any ideas?

-paul

Reply to this Message

Tom van Stiphout - 19 May 2008 05:21 GMT

I think export code typically only looks at the first few lines to
determine the data type. In your case it guessed wrong and can't
import an alphanumeric value in a numeric column.
Better to first create the table, attach the file, and import it using
an Append query.

-Tom.

>Hi,
>
[quoted text clipped - 27 lines]
>
>-paul

Reply to this Message