Data export from text file to SQL Server DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to to import text files to SQL server database table(s) using
MS Access ? If so, how ?. I know that the text files can be imported to SQL
Server database table(s) without using MS Access but we have people who are
not familiar with SQL Server at all and we are trying to get them to be
familiar with it.

Thanks for any help.
 
Is there a way to to import text files to SQL server database table(s)
using
MS Access ? If so, how ?. I know that the text files can be imported to
SQL
Server database table(s) without using MS Access but we have people who
are
not familiar with SQL Server at all and we are trying to get them to be
familiar with it.
Click to expand...

If you have a table in Access linked to SQLserver via ODBC then you can
probable use the regular Text Import Wizard.
 
Yes. That is exacly what we are trying to do. Use link table(s). But we are
trying to automate the process. Click of a button, load the data into SQL
Server tables. How is this possible with text import ?

Thanks.
 
Yes. That is exacly what we are trying to do. Use link table(s). But we
are
trying to automate the process. Click of a button, load the data into SQL
Server tables. How is this possible with text import ?
Click to expand...

Have you tried the
Docmd.TransferText command?

I think that's the fastest option.

This also works. I'm assuming a file "c:\1.txt" with three numbers per line
seperated by a space as:
5 6 7
7 8 9
12 23 56

and then this should work (but is not the fastest way I think).

Dim intFileNo As Integer
Dim strLine As String
Dim arr() As String
Dim db As Database

intFileNo = FreeFile()
Open "C:\1.txt" For Input As #intFileNo
Set db = CurrentDb
' Læs alle linjer
'Begintrans 'optional may speed it up
Do While Not EOF(intFileNo)
Line Input #intFileNo, strLine
arr = Split(strLine, " ")
db.Execute ("INSERT INTO tablename (f1,f2,f3) VALUES (" & arr(0) & "," &
arr(1) & "," & arr(2) & ")"), dbFailOnError
Loop
'CommitTrans - optional may speed it up
Erase arr
Close #intFileNo



Jesper Fjølner
 
Thanks for all the help..........


Jesper Fjølner said:
Have you tried the
Docmd.TransferText command?

I think that's the fastest option.

This also works. I'm assuming a file "c:\1.txt" with three numbers per line
seperated by a space as:
5 6 7
7 8 9
12 23 56

and then this should work (but is not the fastest way I think).

Dim intFileNo As Integer
Dim strLine As String
Dim arr() As String
Dim db As Database

intFileNo = FreeFile()
Open "C:\1.txt" For Input As #intFileNo
Set db = CurrentDb
' Læs alle linjer
'Begintrans 'optional may speed it up
Do While Not EOF(intFileNo)
Line Input #intFileNo, strLine
arr = Split(strLine, " ")
db.Execute ("INSERT INTO tablename (f1,f2,f3) VALUES (" & arr(0) & "," &
arr(1) & "," & arr(2) & ")"), dbFailOnError
Loop
'CommitTrans - optional may speed it up
Erase arr
Close #intFileNo



Jesper Fjølner
Click to expand...
 
Back
Top