# How to write into a text file using MS Access VBA code and importing that text file into Access?

I am trying to write into an existing text file. The (text.txt) file contains some numbers such as...

123335
125455
145575
254875

I am trying to import that data into a table called "Invoice".
While importing this data from text file, I need to write "Field Name" on top of the text file and also change the data type to "text".

###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
better to create another text file with the field name

Dim s
Open CurrentProject.Path & "\mytext.txt" For Input As #1
Open CurrentProject.Path & "\mytext2.txt" For Output As #2
Print #2, "FieldName"

Do Until EOF(1)
Line Input #1, s
Print #2, s
Loop
Close #1
Close #2
0
For example (you have to use Microsoft Scripting Runtime reference):
Sub Import()
Dim Fs As New Scripting.FileSystemObject
Dim Text As Scripting.TextStream
Dim S As String

Set Text = Fs.OpenTextFile("C:\text.txt")
S = "Field Name" + vbCrLf + S
Text.Close
Set Text = Fs.CreateTextFile("C:\text.txt", True)
Text.Write S
Text.Close
End Sub

0
Commented:
Assuming that you have all the values in a single string (CSV with the separator character being a vbCrLf), you can use code like this to save a string to a text file:

Public Sub SaveToTextFile(strString As String, strFilename As String)

Dim intChannel As Integer

'Close any open files
For intChannel = 1 To 511
Close #intChannel
Next intChannel

'Get a filenumber (aka: communication channel) to send information to the file
intChannel = FreeFile

'Open/Create/Overwrite the file, send the data to it, then close the file
Open strFilename For Output Access Write As #intChannel

'Write to the text file, then close it
Print #intChannel, strString
Close #intChannel

End Sub

Example:
SaveToTextFile "MyColumnHeader" & vbCrLf & "Hello" & vbCrLf & "There", "C:\Temp\MyFile.txt"

Will yeild a text file in C:\Temp named MyFile.txt (note: if it exists, it WILL be overwritten) and the contents of that file will be:

Hello
There

-----

To import the data, with the assumption that you will be CREATING new records into an existing table, you can do something like this:

Public Sub ImportFile(strPath As String, strName As String)

Dim strSQL As String

strSQL = "INSERT INTO tblDestinationTableName (FieldNameForData)" & _
" FROM [Text;FMT=Delimited;HDR=YES;CharacterSet=437" & _
";DATABASE=" & strPath & "].[" & strName & "] AS vMyData;"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

Example:
ImportFile "C:\Temp", "MyFile.txt"

Will yeild two records in the destination table with "Hello" and "There" in the "FieldNameForData" field.

If my assumptions are incorrect, please let me know and I will help modify the code accordingly.
0
Commented:
... Oh ...

With the import method I have shown, you really DON'T need the field name as the first row of the text file.  Also, I messed up the strSQL statement ...

Should be:
strSQL = "INSERT INTO tblDestinationTableName (FieldNameForData)" & _
" SELECT MyColumnHeader FROM [Text;FMT=Delimited;HDR=YES;CharacterSet=437" & _
";DATABASE=" & strPath & "].[" & strName & "] AS vMyData;"

Or... if you choose not to use a header ....
strSQL = "INSERT INTO tblDestinationTableName (FieldNameForData)" & _
" SELECT F1 FROM [Text;FMT=Delimited;HDR=NO;CharacterSet=437" & _
";DATABASE=" & strPath & "].[" & strName & "] AS vMyData;"
0
Author Commented:
I tried the first code, it's giving an error, "Bad file or Number", Run time error '52'.
Here's what I have exactly in my code:

Private Sub Command0_Click()

Dim s
Open CurrentProject.Path & "C:\test1.txt" For Input As #1
Open CurrentProject.Path & "C:\test2.txt" For Output As #2
Print #2, "Invoice"

Do Until EOF(1)
Line Input #1, s
Print #2, s
Loop
Close #1
Close #2

End Sub
0
Commented:
try this, the path to your file is not correct

Private Sub Command0_Click()

Dim s
Open "C:\test1.txt" For Input As #1
Open "C:\test2.txt" For Output As #2
Print #2, "Invoice"

Do Until EOF(1)
Line Input #1, s
Print #2, s
Loop
Close #1
Close #2

End Sub
0

Experts Exchange Solution brought to you by