Re: type conversion error

Hi neerak,

Take a look to the missing data in your csv file so you can understand why
they are discarded. Perhaps they are more than 255 char so they can't be
contained in a text field so you must define it as memo.

HTH Paolo

"neerak via AccessMonster.com" wrote:

Thanks for Allen for your suggestion, but i've almost tried every single
method mentioned in this forum and it still doesn't work.

I've tried to convert the columns in the csv file to text before the
transfertext method as follows

xlwkbk.worksheets(1).columns("A").numberformat = "@"
DoCmd.TransferText acImportDelim, , "tmpTable", CurrentProject.Path & "\test.
csv", no

and it doesn't work. then i tried to create tmpTable with all text fields
and insert "ABC" in the first line of every column in the csv file then
execute transfertext... but still it doesn't work. These two methods will
generate the type conversion error table after execution.

Then i moved on to use the following function BuildJetTextSource written by
John Nurick with the following statements and this time I didn't get the type
conversion error table but some of the values in the columns (the ones that
generate the conversion error previously) were missing in the target table.

strSQL = "insert into targetTable (ACCOUNT_ID,DEPT_NAME) SELECT f1,f2 from "
& _
'BuildJetTextSource("C:\test.csv", False) _
'& ";"

Function BuildJetTextSource(ByVal FileSpec As String, _
ByVal HDR As Boolean) As String

'Takes a filespec and returns a string that can be used
'in the FROM clause of a Jet SQL query.
' E.g.
' C:\My Folder\MyFile.txt
' returns
' [Text;HDR=No;Database=C:\My Folder\;].[MyFile#txt]
' The HDR argument controls the HDR parameter in string returned.
'
' By John Nurick 2005
' Revised 2007 to remove call to Dir()

Dim fso As Object 'FileSystemObject
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strTemp As String

'Parse FileSpec
Set fso = CreateObject("Scripting.FileSystemObject")
With fso
FileSpec = .GetAbsolutePathName(FileSpec)
strFolder = .GetParentFolderName(FileSpec)
strFileName = .GetBaseName(FileSpec)
strFileExt = .GetExtensionName(FileSpec)
End With
Set fso = Nothing

'Build string
strTemp = "[Text;HDR=" _
& IIf(HDR, "Yes", "No") _
& ";Database=" _
& strFolder & "\;].[" _
& strFileName & "#" _
& strFileExt & "]"

BuildJetTextSource = strTemp
End Function

Can anyone please help me with this problem? Or is there a way such that i
can programatically execute the import method in the menu bar. Because
manually importing the same csv file does not encounter any errors.

Thank you....

Allen Browne wrote:
The safest way to do this is to create another table with Text fields (not
numbers or dates or curreny), and use an Append query to populate this table
from the text file.

Since they are Text fields, Access can stick the data in. You can then
massage the data to populate your real table with the correct data types.

I kept on receiving type conversion error while trying to programatically
import a csv file into an access table. I've read a lot of the posts
[quoted text clipped - 20 lines]
such that the error only occurs when i tried to import the data
programatically? Any help will be much appreciated....

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200802/1


.