I need a suggestion to export a seperated fileds from sql table in a text file with separated ";"...
Please via VBA for excel and ADO.
I just know the name of server, and name of database and nema of table.
Note:
I have a trusted account. I just have a opened conn to the sql server and database active and bamed MYconn.
export from Sql Server table to delimited text ";"
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: export from Sql Server table to delimited text ";"
Declare an ADODB recordset named rst, and open the recordset on the table or SQL string that you want to export. You can then use code like this:
Code: Select all
Dim strList As String
Dim f As Integer
' Get string containing the records
strList = rst.GetString(Stringformat:=adClipString, _
ColumnDelimeter:=";", RowDelimeter:=vbCrLf)
' Open text file
f = FreeFile
Open "Test.txt" For Output As #f
' Write records to text file
Print #f, strList
' Close text file
Close #f
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: export from Sql Server table to delimited text ";"
Tested... but getstring causes not enought memory, into the sql table are 41 fields and approx 3/4 milions of records. In otheer case a need to export from sql table not all field but only 21 fieldsHansV wrote:Declare an ADODB recordset named rst, and open the recordset on the table or SQL string that you want to export. You can then use code like this:
Code: Select all
Dim strList As String Dim f As Integer ' Get string containing the records strList = rst.GetString(Stringformat:=adClipString, _ ColumnDelimeter:=";", RowDelimeter:=vbCrLf) ' Open text file f = FreeFile Open "Test.txt" For Output As #f ' Write records to text file Print #f, strList ' Close text file Close #f
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: export from Sql Server table to delimited text ";"
You could specify an SQL string that selects the fields you need instead of the table name. But with that many records, you'll still run out of memory.
I don't think there is a really good way to do this from Excel with so much data. I'd set up an export job on SQL Server.
I don't think there is a really good way to do this from Excel with so much data. I'd set up an export job on SQL Server.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: export from Sql Server table to delimited text ";"
tks for suggestion... but not have access on the sql server...HansV wrote:You could specify an SQL string that selects the fields you need instead of the table name. But with that many records, you'll still run out of memory.
I don't think there is a really good way to do this from Excel with so much data. I'd set up an export job on SQL Server.
And this prob still me destroied my mind...
In other case i dont want to store the sql rset in excle sheets but i really store the sql rset into access table.
I have tested with a simple code to save from sql table into access table, similar rst.addnew... rst.update during the loop.
But this choice make a very very large time.
I friend suggest, to speed up operation, to store the sql rset into the txt file and after import into acces table...
Last edited by sal21 on 17 Jul 2010, 15:45, edited 1 time in total.
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: export from Sql Server table to delimited text ";"
You should ask the database administrator to set up the export job for you.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: export from Sql Server table to delimited text ";"
the admin is a "Gosth", not possible to contact itHansV wrote:You should ask the database administrator to set up the export job for you.
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: export from Sql Server table to delimited text ";"
There's always a real person who manages a SQL Server database.
Best wishes,
Hans
Hans