We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Creating a comma delimited text file from an excel workbook

Derek Schauland
on
Medium Priority
7,856 Views
Last Modified: 2012-06-21
I am trying to create a report in excel from SQL server.  This part of the problem works just fine, however the end result needs to be in the form of a comma delimited text file... and the excel text file it creates is not visible in notepad.

How might I get this to work?

the command button code to create the txt is below

Private Sub CommandButton2_Click()
    Dim filenme As String
   
   
    Rows("7:616").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Columns("C:C").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Application.CutCopyMode = False
    filenme = InputBox(Prompt:="Please enter report date (ie 04022004)", Title:="Save As...")
    'If filenme = "" Then Exit Sub
    ActiveWorkbook.SaveAs Filename:="F:\groups\creDIT\nacm reports\nacm" + filenme + ".txt", FileFormat:= _
        xlText, CreateBackup:=False
End Sub
Comment
Watch Question

Commented:
most likely reason is because your program doesn't know what xlText is...

change the line to
ActiveWorkbook.SaveAs Filename:="F:\groups\creDIT\nacm reports\nacm" + filenme + ".txt", FileFormat:= -4158, CreateBackup:=False
Derek SchaulandCloud Engineer
CERTIFIED EXPERT

Author

Commented:
Looks good... I have a feeling there might be some yelling about fixed length fields in the text file...

I am not even sure that is possible...??

Saving the file with a txt extension does not necessarily mean that the files contents are text.
A contents of a CSV file should be pure ascii format. A link you will find useful:
http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm
If you are using EXCEL objects to create the file then you are propabably saving it in excel format. I am not sure but that may be the problem.

Derek SchaulandCloud Engineer
CERTIFIED EXPERT

Author

Commented:
is there a fileformat I can specify that will generate a csv?
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Derek SchaulandCloud Engineer
CERTIFIED EXPERT

Author

Commented:
seems ok so far... when i hear further I will let you know if it is working


thanks

Commented:
Why not simply BCP it out from SQL Server through DMO or Command Prompt ?
Or ?  Simply DTS from SQL Server.

I'm looking at solving this with 1 line with the BCP from the command prompt,   as BCP will create a comma delited (or any character virtually) file.  I ask,  why not keep it simple ?  

Tim Miltz
Chief Technology Officer
Etaion Software
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.