Solved File Read error

P

prasadgov

Member
Local time
Today, 00:43
Joined
Oct 12, 2021
Messages
120
Hi,

My text file looks as below

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


Code: 
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
 
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.
 
you should Read the content of the textfile to a variable and save it using SQL insert.


Code: 
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
 
Last edited:
gemma-the-husky said:
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.
Click to expand...
I changed it to False and it imports all files but it stops after "A1010307300000038824" and does not import the rest of the data
 
maybe it was truncated to 255 chars only.
is "message" a Long Text (Memo)?
 
Last edited:
arnelgp said:
you should Read the content of the textfile to a variable and save it using SQL insert.


Code: 
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
Click to expand...
It gives a runtime error '3075' syntax error in string in query expression "A1010307300000038824'.
 
arnelgp said:
maybe it was truncated to 255 chars only.
is "message" a Long Text (Memo)?
Click to expand...
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'.
 
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?
 
CJ_London said:
to be clear you are expecting to retrieve 13 rows of data from a space delimited text file?
Click to expand...
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.

1719841440360.png

It gives a runtime error '3075' syntax error in string in query expression "A1010307300000038824'.
 
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.
 
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.

Code: 
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.
 
this works for me based on the example provided

SQL: 
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)
 
Last edited:
The_Doc_Man said:
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.
Click to expand...
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
The_Doc_Man said:
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.

Code: 
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.
Click to expand...
there are several text files in the folder and the code iterates through every text file
 
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
 
The_Doc_Man said:
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.

Code: 
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.
Click to expand...
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.
 
CJ_London said:
this works for me based on the example provided

SQL: 
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)
Click to expand...
My field is also Long Text.
 
CJ_London said:
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
Click to expand...
 

Attachments

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?
 
There are a whole bunch of Nulls?
1719847517328.png
 
You must log in or register to reply here.

Similar threads

P
Import text file
Replies
5
Views
402
ebs17
E
C
Count # of CERTAIN files in a folder but not one by one
2
Replies
21
Views
1,996
adhoustonj
adhoustonj
Ocicek
FileSystemObject error when the file names has special letters
2 3 4
Replies
61
Views
8,862
MajP
MajP
G
Fill the listbox only with file name
2
Replies
27
Views
1,991
Gasman
Gasman
J
Retrieve Subfolders Properties
Replies
5
Views
930
static
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom