Save as txt file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Save as txt file

Author

Topic

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-14 : 14:50:07

I am trying to save SQL results from a spread sheet to a comma delimitated file, but I don’t want the first line to have the quotes in front or at the end or be delimitated. I found this code that works great except it does not include the first row. And again I don’t want the first row to have the quotes or be coma delimitated. Can anyone help please?

Here is the spread sheet file
ISA*ZZ*TS810 *00* *ZZ*TEST *ZZ*TEST *000000*0000*U*00400*000000000*0*P*~
1 BB AA BB AA BB AA BB AA BB AA BB
2 BB AA BB AA BB AA BB AA BB AA BB
3 BB AA BB AA BB AA BB AA BB AA BB
4 BB AA BB AA BB AA BB AA BB AA BB
5 BB AA BB AA BB AA BB AA BB AA BB

Here is what I’m getting in the text file (Perfect except no header record)
"1" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB"
"2" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB"
"3" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB"
"4" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB"
"5" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB" , "AA" , "BB"

Here is the code
Public Sub ExportToText()

Dim fso
Dim aArray As Variant

Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoText = fso.CreateTextFile("C:\MyFile.dat", True)

vCol = Left(Columns(Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column).Address(0, 0), 2 + (Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column < 27))

For i = 2 To Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

ReDim aArray(1 To 1, 1 To Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column)

aArray = Range("A" & i & ":" & vCol & i).Value

For x = 1 To Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

If vString = "" Then
vString = Chr(34) & aArray(1, x) & Chr(34)
Else
vString = vString & " , " & Chr(34) & aArray(1, x) & Chr(34)
End If

Next x

fsoText.WriteLine (vString)
vString = ""

Next i

MsgBox ("TEXT EXPORT COMPLETE")
fsoText.Close

Set fso = Nothing
Set aArray = Nothing
End Sub

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-14 : 15:17:02

you'll get much more relevant and quick answer if you post it in a VB forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources