type conversion error

[neerak via AccessMonster.com]

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 saying
that it is caused by the mixed data type stored in the column and the
solution is to insert an additional row at the top of the file such that
access can determine the type of each column by reading the data in the first
row.

Now, my question is that column 3 and 4 in my csv file contains both numeric
and non-numeric data. I've tried to insert "ABC" and "ABC" into the first
line of column 3 and 4 respectively but the error still arise. I get stuck
in this problem for quite a while and i really hope some of you might have
encounter the same problem before and have already found a solution.

Another question is that the type conversion error problem does not occur if
i manually use the import function from get external data. Does it mean that
access use different methods to import data to access for the 2 approaches
such that the error only occurs when i tried to import the data
programatically? Any help will be much appreciated....

 

[Allen Browne]

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.

 

[neerak via AccessMonster.com]

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....

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....

 

[Paolo]

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

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....

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....

 

[RD]

Create a schemea file in the same directory as the text file you're importing.
It must be named schema.ini

Below is an example of a schema file I had to create. In my case the file
didn't have headers and was tilde delimited. Change those parameters to match
your own circumstances. Since I started using schema files I've never had a
problem with Access' messed up type conversions.

HTH,
RD


[MRA035E.TXT]
ColNameHeader=False
Format=Delimited(~)
Col1=CS-ID Text Width 7
Col2=CS-LAST-NM Text Width 25
Col3=CS-FIRST-NM Text Width 25
Col4=CS-MI Text Width 1
Col5=CWIN Long Width 9
Col6=INDV-LAST-NM Text Width 25
Col7=INDV-FIRST-NM Text Width 25
Col8=INDV-MI Text Width 1
Col9=PGM-AID-CD Text Width 2
Col10=ES-PGM-TYP-CD Text Width 2
Col11=ES-PGM-TYP-DESC Text Width 30
Col12=SSN Text Width 11
Col13=AGE Text Width 5
Col14=PRM-LANG-CD Text Width 2
Col15=PRM-LANG-DESC Text Width 50
Col16=ADR-CTY Text Width 30
Col17=ADR-ZIP Text Width 5
Col18=CNSS-TRCT Text Width 20
Col19=ETHN-CD Text Width 2
Col20=ETHN-DESC Text Width 30
Col21=ES-PGM-STS-CD Text Width 2
Col22=ES-PGM-STS-DESC Text Width 30
Col23=WTW-ACT-TYP Text Width 2
Col24=WTW-ACT-TYP-DESC Text Width 50
Col25=ACT-BGN-DT DateTime Width 10
Col26=ACT-END-DT DateTime Width 10
Col27=ACT-STS-CD Text Width 2
Col28=ACT-STS-DESC Text Width 50
Col29=ACT-STS-DT Text Width 10
Col30=ACT-ELAPSED-TIME Long Width 3
Col31=ACTL-WEEKLY-HOURS Long Width 2
Col32=SCHED-WEEKLY-HRS Long Width 3
Col33=PRVDR-LAST-NM Text Width 50
Col34=PRVDR-FIRST-NM Text Width 25
Col35=PRVDR-MI Text Width 1
Col36=SESSION-ID Long Width 9
Col37=WITHIN-PROCESS-SW Text Width 1
Col38=BETWEEN-PROCESS-SW Text Width 1
Col39=ACT-STS-TYP-CD Text Width 2
Col40=NONCOMPLI-SW Text Width 1
Col41=GOOD-CAUSE-SW Text Width 1
Col42=ENROLLEE-SW Text Width 1
Col43=EXEMPTION-SW Text Width 1
Col44=WTW-SNCTN-SW Text Width 1
Col45=TIME-LIMIT-SW Text Width 1
Col46=ENTERED-EMP-SW Text Width 1
Col47=EXIT-DUE-TO-EMP-SW Text Width 1
Col48=GR-WAIVER-SW Text Width 1
Col49=SIP-SW Text Width 1
Col50=ES-CASELOAD-NUM Text Width 4
Col51=OFFICE-ID Text Width 20
Col52=UNIT-ID Text Width 20
Col53=DIVISION-ID Text Width 20
Col54=APP-BETWEEN-PROCESS Text Width 1
Col55=COUNT-THIS-RECORD-SW Text Width 1
Col56=SELF-EMPLOYEE-SW Text Width 1
Col57=SUBSI-PRIVATE-SW Text Width 1
Col58=SUBSI-PUBLIC-SW Text Width 1
Col59=UNSUBSIDIZED-SW Text Width 1
Col60=SAT-UNSAT-SW Text Width 1
Col61=AG-TYP-CD Text Width 2