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

[PW]

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]", "tblInventoryPu rchases", strFind)


DoCmd.TransferT ext acImportDelim, , "tblTempCashReg ", strReturnVal, -1

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


Any ideas?

-paul

[Tom van Stiphout]

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

On Sun, 18 May 2008 20:10:21 -0600, PW
<paulremove_wil liamson858@remo vehotmail.comwr ote:

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,
>
>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
>lu24769884xx l
>
>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]", "tblInventoryPu rchases", strFind)
>
>
>DoCmd.Transfer Text acImportDelim, , "tblTempCashReg ", strReturnVal, -1
>
>This particular field in the Access table is formatted as text.
>
>
>Any ideas?
>
>-paul