type conversion failure after transfertext()

[mpearce]

i have a text file with a few thousand records in it that i need to import into an access table. I think i have written the code correctly, as i get no runtime errors.

Private Sub Command0_Click()
DoCmd.TransferText acImportDelim, "UHS import specification", "import", "c:\UHS Bulk upload 03202009.txt", True
End Sub

I get a lot of errors related to phone number fields which are just plain digits. I have the data type on those fields set to text with a width of 255, which should be plenty of room for them, in face more than enough.

I am just confused as to why those fields aren't importing. everything else in the text file does import perfectly.

I can provide the database and a few import samples if needed.

Thanks in advance.

[Oorang]

What errors are you getting?

[mpearce]

What errors are you getting?

I'm not getting any errors on the import. But after the import I see the table the data was imported into plus another errors table with a bunch of type conversion failures, the field names, and row numbers. those two fields are just phone numbers and they are just plain digits no -'s or other characters.

[Oorang]

That sounds like it might be an issue with the actual "UHS import specification" spec. When you try to use it manually are you still getting errors?

[mpearce]

That sounds like it might be an issue with the actual "UHS import specification" spec. When you try to use it manually are you still getting errors?

manually importing the text file seems to fix one of the two fields that were getting type conversion failure. These fields are just phone numbers. they are ten digits, plain digits no () or - as a separator.

Any ideas on why this might be happening?

Again, if i need to provide a data sample i can do that just let me know.

Thanks

[CreganTur]

What data type is the field that will be receiving the phone numbers? WHen you use TransferText, Access guesses which data type results should be. It could be reading your phone numbers as long, which would error if your Table fields are String... it could eb reading them as String when your Table fields are set to a number data type.

[mpearce]

What data type is the field that will be receiving the phone numbers? WHen you use TransferText, Access guesses which data type results should be. It could be reading your phone numbers as long, which would error if your Table fields are String... it could eb reading them as String when your Table fields are set to a number data type.
that is a good point i noticed that in the table design both phone fields were set to long integer. I set them both to text with a field size of 50, which i think would be plenty for 10 characters. This fixed one of the fields completely, but the other still gets errors in about every row, there are about 3100 rows.

#PASPFM0|0009999999999 |01/12/2008|SMITH ,JOHN|02/07/1990|M|644.03|MTR |100000||N |0|OP|5000.00|111111111|123 MAIN STREET||ANYWHERE|US|123450000|9999999999|SMITH|JOHN|111111111|123 MAIN STREET||ANYWHERE|US|12345|9999999999|UNEMPLOYED|||||||D60|A12345678|||||||| ||||

The field in red is the one that is getting the type conversion failure. what could access be reading this as?

[OBP]

If you import the same file in to Excel what does it interpret that field as?
If this is a one off Import excel is a good option to view the data and maybe put a ' in front of numbers to turn them to text.
I have a macro to do that if you need it
If you have an Import spec you should be able to force that field to text regardless of what Access wants to do with it.

[CreganTur]

If Tony's suggestion doesn't work for you, then my next suggestion would be to use ADO to create a recordset based on the text file, and then run some data maniuplation routines to format it correctly and use the ADO execute method to run an action query to load the records into your database.

[mpearce]

If you import the same file in to Excel what does it interpret that field as?
If this is a one off Import excel is a good option to view the data and maybe put a ' in front of numbers to turn them to text.
I have a macro to do that if you need it
If you have an Import spec you should be able to force that field to text regardless of what Access wants to do with it.

If i import the data into excel all fields are interpreted as general. This will be a regular import process occurring daily. I do have an import spec setup already, I'm just not sure how to force access to import as text regardless.

[OBP]

Can you supply a dummy file?

[CreganTur]

What does the field read as if you manually link it to your Access database?

[mpearce]

What does the field read as if you manually link it to your Access database?
I am not familiar with linking excel to an access database.



Can you supply a dummy file?

I'll work on putting one together.

[CreganTur]

I am not familiar with linking excel to an access database.

No, the text file itself.

CLick File-> Get External Data-> Link Tables. On the file dialog window, be sure to change the selection to text files. When you click okay, a wizard should step you through creating a linked table out of the text file.

[mpearce]

No, the text file itself.

CLick File-> Get External Data-> Link Tables. On the file dialog window, be sure to change the selection to text files. When you click okay, a wizard should step you through creating a linked table out of the text file.
wow...that was simple enough lol...the phone number fields are read as text fields under a linked table.



If Tony's suggestion doesn't work for you, then my next suggestion would be to use ADO to create a recordset based on the text file, and then run some data maniuplation routines to format it correctly and use the ADO execute method to run an action query to load the records into your database.

i have done this with an excel spreadsheet but not with a text file. What do I have to do differently?

[mpearce]

alright so i did a little more fooling around with this import method and found that it works fine if i take the data in the text file and put it in an excel file and then use ADO to import the workbook to an access table.

I was able to create an excel macro that imports a text file into excel and saves the workbook to a directory.

So my question is how can i run an excel macro within access? I take it there isn't a way to record a macro in access like you can in excel.

[CreganTur]

I've forgotten your original problem... and I'm too lazy to reread everything right now (new job stress), so this might be a dumb question:

why not use ADO to just pull the data from the text file directly into Access, instead of adding in an extra step of using Excel?

[mpearce]

why not use ADO to just pull the data from the text file directly into Access, instead of adding in an extra step of using Excel?
I'd like to use whatever the simplest way to programmatically import a text file into access is. If ADO is a good option, I'd like to do that. I'm just not sure how to code that.

Ideally I would like to keep this all in access and not have to make too many references to other applications.

[CreganTur]

I'd like to use whatever the simplest way to programmatically import a text file into access is. If ADO is a good option, I'd like to do that. I'm just not sure how to code that.

Ideally I would like to keep this all in access and not have to make too many references to other applications.

You can do it easily.

The way I do it is by creating a loop from the start of the recordset to EOF. I use the ADO.Execute method to run a SQL update statement to load each record into an existing table. It's a very fast process.

[mpearce]

You can do it easily.

The way I do it is by creating a loop from the start of the recordset to EOF. I use the ADO.Execute method to run a SQL update statement to load each record into an existing table. It's a very fast process.

could you post an example?

Thanks

[CreganTur]

Post the code you are currently using that creates an ADO recordset out of the text file- I need it as a baseline to add in the code for Access.

[mpearce]

Thats the thing i dont have that code written yet. i have code that creates an ADO recordset from an excel file, but not a plain text file.

[CreganTur]

Thats the thing i dont have that code written yet. i have code that creates an ADO recordset from an excel file, but not a plain text file.

Oh, okay. One of your earlier posts made it sound like you did.

Is this a delimited text file? If so, what's the delimiter? If not, then how is it organized?

[mpearce]

the file is pipe delimited, with column headings

[CreganTur]

This should work for you. Just replace the field names in the Select Case structure to match your own, and adjust the SQL statement to match your table as well.

HTH:thumb


Public Const INSTR_NOTFOUND As Long = 0
Public Const TOKEN_NOTFOUND As Long = -1

'delimeter constants
Public Const DELIM_SPACE As String = " "
Public Const DELIM_COMMA As String = ","
Public Const DELIM_TAB As String = vbTab
Public Const DELIM_PIPE As String = "|"

Public Const ERR_BADFILENAMEORNUMBER As Long = 52
Public Const ERR_FILENOTFOUND As Long = 53
Public Const ERR_FILEALREADYOPEN As Long = 55
Public Const ERR_BADRECORDLENGTH As Long = 59
Public Const ERR_PERMISSIONDENIED As Long = 70
Public Const ERR_PATHNOTFOUND As Long = 76


Public Function PeelToken(ByRef item As String, _
Optional Delimiter As String = DELIM_PIPE) As String
Dim pch As Long
pch = InStr(Nz(item), Delimiter) - 1
If pch = TOKEN_NOTFOUND Then pch = Len(Nz(item))
PeelToken = Trim(Left(Nz(item), pch))
item = Mid(Nz(item), pch + Len(Delimiter) + 1)
End Function


Public Sub ReadRecordsFromFile(fileName As String)
Dim hFileSrc As Long '<<<Variable to hold the file number
Dim item As String '<<<Variable to hold the row of data from the file
Dim i As Integer
Dim FName As String, LName As String, EMail As String
Dim Area As String, Portfolio As String, DPD As String, AcctNmbr As String
Dim strSQL As String
Dim Conn As ADODB.Connection
Set Conn = CurrentProject.Connection
'obtain the next available file handle
hFileSrc = FreeFile
'open the file
Open fileName For Input As #hFileSrc
'loop through each row in the text file
Do While Not EOF(hFileSrc)
'read the row into the item variable
Line Input #hFileSrc, item

i = 1
Do While Len(item) > 0
Select Case i
Case 1
FName = Replace(PeelToken(item), "'", "")
Case 2
LName = Replace(PeelToken(item), "'", "")
Case 3
EMail = PeelToken(item)
Case 4
Area = PeelToken(item)
Case 5
Portfolio = PeelToken(item)
Case 6
DPD = PeelToken(item)
Case 7
AcctNmbr = PeelToken(item)
End Select
i = i + 1
If i > 7 Then Exit Do
Loop
strSQL = "INSERT INTO tblSource VALUES('" & FName & "', '" & LName & "', '" & EMail _
& "', '" & Area & "', '" & Portfolio & "', '" & DPD & "', '" & AcctNmbr & "');"
Conn.Execute strSQL
Loop
MsgBox "done"
Cleanup:
'always remember to close your files
On Error Resume Next
Close #hFileSrc
Terminate:
On Error GoTo 0
Exit Sub
Err_Handler:
Dim msg As String
Dim title As String
'calculate the error message
Select Case Err.Number
Case ERR_FILENOTFOUND
msg = "File not found: " & fileName
Case ERR_PERMISSIONDENIED
msg = "Permission Denied on " & fileName
Case Else
msg = Err.Description
End Select
'display the error
MsgBox msg, vbCritical, "Error Number " & Err.Number
'clear the error, then go to cleanup to close the file
Err.Clear
GoTo Cleanup:
End Sub