Data export from text file to SQL Server DB

[DXC]

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.

 

[Kevin3NF]

If the structure is the same, I would create a DTS package and a stored
procedure that calls it. have them click a button that send the file name,
etc to the SP.

HTH,

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[Jesper Fjølner]

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.

If you have a table in Access linked to SQLserver via ODBC then you can
probable use the regular Text Import Wizard.

 

[DXC]

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.

 

[Jesper Fjølner]

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 ?

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

 

[DXC]

Thanks for all the help..........

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

 

[Jesper Fjølner]

Thanks for all the help..........

You're welcome. Did it work?

 

[aaron.kempf]

man

dont listen to that crap

use BCP