Solved File Read error (1 Viewer)

[prasadgov]

Hi,

My text file looks as below

A1010307300000038824 2024178000010000038800000380 O 0000038824-000001S513000000388 06055JFD6OM 000000000 * 85173 01965137 SB 030000000 533SEG5

Private Sub Command0_Click()
Dim FSO As Object, objFolder As Object, objFile As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder("C:\Prasad\BC2\TC")
i = 1
For Each objFile In objFolder.Files
If Right(objFile.Name, 3) = "txt" Then
DoCmd.TransferText acImportDelim, , "tbltcc", objFolder & "\" & objFile.Name, True
End If
i = i + 1
Next objFile

Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing
End Sub

I get an error message "There is no field name "A1010307300000038824" in tbltcc.
I have a single field, "message" in tbltcc to which I need to import the contents of the text file.

TIA

 

[gemma-the-husky]

Maybe the last option in transfertext should be false, not true. (Has titles). I'm not in front of a PC so just thinking out loud.

 

[arnelgp]

you should Read the content of the textfile to a variable and save it using SQL insert.

Private Sub Command0_Click()
Dim FSO As Object, objFolder As Object, objFile As Object
Dim content As String
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder("C:\Prasad\BC2\TC")
i = 1
For Each objFile In objFolder.Files
If Right(objFile.Name, 3) = "txt" Then
    With FSO.OpenTextFile(objFolder & "\" & objFile.Name, 1)
        content = .ReadAll
        .Close
    End With
    CurrentDb.Execute "insert into tbltcc (message) select '" & content & "';"
End If
i = i + 1
Next objFile

Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing
End Sub

 

[prasadgov]

Maybe the last option in transfertext should be false, not true. (Has titles). I'm not in front of a PC so just thinking out loud.

I changed it to False and it imports all files but it stops after "A1010307300000038824" and does not import the rest of the data

 

[arnelgp]

maybe it was truncated to 255 chars only.
is "message" a Long Text (Memo)?

 

[prasadgov]

maybe it was truncated to 255 chars only.
is "message" a Long Text (Memo)?

yes, It is Long Text

 

[prasadgov]

you should Read the content of the textfile to a variable and save it using SQL insert.

Private Sub Command0_Click()
Dim FSO As Object, objFolder As Object, objFile As Object
Dim content As String
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder("C:\Prasad\BC2\TC")
i = 1
For Each objFile In objFolder.Files
If Right(objFile.Name, 3) = "txt" Then
    With FSO.OpenTextFile(objFolder & "\" & objFile.Name, 1)
        content = .ReadAll
        .Close
    End With
    CurrentDb.Execute "insert into tbltcc (message) select '" & content & "';"
End If
i = i + 1
Next objFile

Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing
End Sub

It gives a runtime error '3075' syntax error in string in query expression "A1010307300000038824'.

 

[prasadgov]

maybe it was truncated to 255 chars only.
is "message" a Long Text (Memo)?

Apologies, I was trying with "DoCmd.TransferText acImportDelim, , "tbltcc", objFolder & "\" & objFile.Name, False and it truncates.

But Using your code of sqlinsert, I got an syntax error, which I have replied in your response.
It gives a runtime error '3075' syntax error in string in query expression "A1010307300000038824'.

 

[CJ_London]

to be clear you are expecting to retrieve 12 rows of data from a space delimited text file? Or a single row?

and what happens with the data after, you split it to various fields? Something else?

 

[prasadgov]

to be clear you are expecting to retrieve 13 rows of data from a space delimited text file?

No.
This all is in a single row
A1010307300000038824 2024178000010000038800000380 O 0000038824-000001S513000000388 06055JFD6OM 000000000 * 85173 01965137 SB 030000000 533SEG5.

I need all this data to be dumped in one field (Long Text). But it truncates after A1010307300000038824.

It gives a runtime error '3075' syntax error in string in query expression "A1010307300000038824'.

 

[The_Doc_Man]

It is likely that you are running afoul of some special character in the Long Memo field such that it truncates at the special character. The most likely case of this would be a single-quote mark since that is what you are using as a delimiter in the SQL statement. Also twice three times now, you have shown us the error message which includes your A1010... string beginning with a double-quote but ending with a single quote. Yes, that could be YOUR typo, but it might also be real.

 

[The_Doc_Man]

After looking at that for a while, it seems that your number of insertions is limited (by the number of files). I'm going to suggest something that is a variant of Arnel's suggestion but which will avoid concatenation issues entirely.

Private Sub Command0_Click()
Dim FSO As Object, objFolder As Object, objFile As Object
Dim content As String, rsTmp as DAO.Recordset
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder("C:\Prasad\BC2\TC")
Set rsTmp = CurrentDB.OpenRecordset( "tbltcc", dbOpenTable )
i = 1
For Each objFile In objFolder.Files
If Right(objFile.Name, 3) = "txt" Then
    With FSO.OpenTextFile(objFolder & "\" & objFile.Name, 1)
        content = .ReadAll
        .Close
    End With
    rsTmp.AddNew
    rsTmp!Message = content
    rsTmp.Update
'    CurrentDb.Execute "insert into tbltcc (message) select '" & content & "';"
End If
i = i + 1
Next objFile

rsTmp.Close
Set rsTmp = Nothing
Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing
End Sub

If you do this, there is no string-processing intermediary involved that does any concatenation. It should be as fast as the other method.

I DO have one question of curiosity... what has "i" got to do with anything? Were you using it for single-step tracing purposes? Because I don't see where it contributes anything.

 

[CJ_London]

this works for me based on the example provided

INSERT INTO tmpTable ( LText )
SELECT txt.F1
FROM (SELECT * FROM [TEXT;DATABASE=D:\Dev\;HDR=No].test.txt)  AS txt;

LText works as memo or text fields (since the char count is 142 chars)

 

[prasadgov]

It is likely that you are running afoul of some special character in the Long Memo field such that it truncates at the special character. The most likely case of this would be a single-quote mark since that is what you are using as a delimiter in the SQL statement. Also twice three times now, you have shown us the error message which includes your A1010... string beginning with a double-quote but ending with a single quote. Yes, that could be YOUR typo, but it might also be real.

I am not sure where that is coming from since the data in the text file is
A1010307300000038824 2024178000010000038800000380 O 0000038824-000001S513000000388 06055JFD6OM 000000000 * 85173 01965137 SB 030000000 533SEG5

Is there a way to

After looking at that for a while, it seems that your number of insertions is limited (by the number of files). I'm going to suggest something that is a variant of Arnel's suggestion but which will avoid concatenation issues entirely.

Private Sub Command0_Click()
Dim FSO As Object, objFolder As Object, objFile As Object
Dim content As String, rsTmp as DAO.Recordset
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder("C:\Prasad\BC2\TC")
Set rsTmp = CurrentDB.OpenRecordset( "tbltcc", dbOpenTable )
i = 1
For Each objFile In objFolder.Files
If Right(objFile.Name, 3) = "txt" Then
    With FSO.OpenTextFile(objFolder & "\" & objFile.Name, 1)
        content = .ReadAll
        .Close
    End With
    rsTmp.AddNew
    rsTmp!Message = content
    rsTmp.Update
'    CurrentDb.Execute "insert into tbltcc (message) select '" & content & "';"
End If
i = i + 1
Next objFile

rsTmp.Close
Set rsTmp = Nothing
Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing
End Sub

If you do this, there is no string-processing intermediary involved that does any concatenation. It should be as fast as the other method.

I DO have one question of curiosity... what has "i" got to do with anything? Were you using it for single-step tracing purposes? Because I don't see where it contributes anything.

there are several text files in the folder and the code iterates through every text file

 

[CJ_London]

interesting thing is on this browser (Opera) your the text is all black. If I view from my phone (Safari) some character blocks are in blue as below

A1010307300000038824 2024178000010000038800000380 O 0000038824-000001S513000000388 06055JFD6OM 000000000 * 85173 01965137 SB 030000000 533SEG5

kind of implies there are some hidden characters there.

Might be an idea to upload one of your text files and I can test on real data

 

[prasadgov]

After looking at that for a while, it seems that your number of insertions is limited (by the number of files). I'm going to suggest something that is a variant of Arnel's suggestion but which will avoid concatenation issues entirely.

Private Sub Command0_Click()
Dim FSO As Object, objFolder As Object, objFile As Object
Dim content As String, rsTmp as DAO.Recordset
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder("C:\Prasad\BC2\TC")
Set rsTmp = CurrentDB.OpenRecordset( "tbltcc", dbOpenTable )
i = 1
For Each objFile In objFolder.Files
If Right(objFile.Name, 3) = "txt" Then
    With FSO.OpenTextFile(objFolder & "\" & objFile.Name, 1)
        content = .ReadAll
        .Close
    End With
    rsTmp.AddNew
    rsTmp!Message = content
    rsTmp.Update
'    CurrentDb.Execute "insert into tbltcc (message) select '" & content & "';"
End If
i = i + 1
Next objFile

rsTmp.Close
Set rsTmp = Nothing
Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing
End Sub

If you do this, there is no string-processing intermediary involved that does any concatenation. It should be as fast as the other method.

I DO have one question of curiosity... what has "i" got to do with anything? Were you using it for single-step tracing purposes? Because I don't see where it contributes anything.

It still imports data from all files in the folder but only until A1010307300000038824, which is the constant first entry in all files followed by other data inside are different.

 

[prasadgov]

this works for me based on the example provided

INSERT INTO tmpTable ( LText )
SELECT txt.F1
FROM (SELECT * FROM [TEXT;DATABASE=D:\Dev\;HDR=No].test.txt)  AS txt;

LText works as memo or text fields (since the char count is 142 chars)

My field is also Long Text.

 

[prasadgov]

interesting thing is on this browser (Opera) your the text is all black. If I view from my phone (Safari) some character blocks are in blue as below

A1010307300000038824 2024178000010000038800000380 O 0000038824-000001S513000000388 06055JFD6OM 000000000 * 85173 01965137 SB 030000000 533SEG5

kind of implies there are some hidden characters there.

Might be an idea to upload one of your text files and I can test on real data

 

[gemma-the-husky]

If there are any ASCII characters in the range 0 to 31, they might interfere with the way the "string" is interpreted. Do you have a layout description? If not, how will you analyse the file contents?

 

[Gasman]

There are a whole bunch of Nulls?