EXCEL export creating double quotes

[asad9100]

Hi there, I am facing an issue with VBA. I am creating a csv file from one of the sheet and the from csv I am creating a text file for BCP to upload it to SQL server. The issue is that till the creation of CSV the data looks good but when I save it as text file then few columns have double quotes " " with them. I need the data without double quotes. The piece of code where it says "save the csv file". I tried to change the format to xlTextPrinter which solves the proble of double quotes but then the BCP is not able to read the file for upload.

Any help would be great. Thanks in advance..

Her is my VBA code



Public Sub ExportSheetToSQL(Tabname As String, Filename As String, Tablename As String, firstRow As String)
'define variables
Dim WS As Excel.Worksheet
Dim SaveToDirectory As String
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
'get name of the workbook
CurrentWorkbook = ThisWorkbook.FullName
CurrentWorkbookName = ThisWorkbook.Name
CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook
SaveToDirectory = GetTempDirectory & ""
'make sure that the temp directory exists
'If Len(Dir(GetTempDirectory(), vbDirectory)) = 0 Then
'MkDir GetTempDirectory()
'End If
'copy the workbook
'it is necessary to save it as an CVS file
Set CVSWorkbook = Workbooks.Add
With CVSWorkbook
.Title = "CVS"
.Subject = "CVS"
.SaveAs Filename:=SaveToDirectory & "XLS" & Filename & ".xls"
End With
Workbooks(CurrentWorkbookName).Activate
Worksheets(Tabname).Select
Worksheets(Tabname).Copy Before:=CVSWorkbook.Sheets(1)
'clear formats
'it is necessary to get rid of the USD format
'CVSWorkbook.Worksheets(Tabname).Range("A:XZ").ClearFormats
Dim lastRowIndex As Long
lastRowIndex = 0
lastRowIndex = Worksheets(Tabname).Range("A200000").End(xlUp).Row
'save the csv file
CVSWorkbook.SaveAs Filename:=SaveToDirectory & Filename, FileFormat:=xlTextWindows
Dim TargetWorkbook As String
TargetWorkbook = CVSWorkbook.FullName
' MsgBox TargetWorkbook
End Sub

[Paul_Hossler]

It will be easier to review if you attach a small XLSM workbook with data and macros that demonstrate the issue

[asad9100]

Hi Paul_Hossler,

I have attached the sample file with dummy data but it seems that when there is a "," in the data the text file include the data within quotes. Hope this will clarify a bit. Thanks

[Paul_Hossler]

CSV fields that have commas in them are bracketed with quotes

I think the doubling is caused by manipulating two workbooks

This is a little snippet that makes the CSV but doesn't double the quotes (attached)

You'll have to fix the folder and name



Sub Macro1()
Worksheets("DataSheet").Copy
ActiveWorkbook.SaveAs Filename:="C:\Users\Daddy\Downloads\CSV_File.csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate
End Sub

[p45cal]

Although csv files are supposed to be comma separated variable files they're often not! Often, they're tab delimited files.
Are you trying to get something like the attached.
Open this in Notepad, not Excel.
If so, I think it will need vba to create the file. which I should be able to do.

If not, attach a sample text/csv file that BCP can sucessfully upload to the SQL server so that we can examine it and reproduce the output.

[Paul_Hossler]

Now I'm confused. Maybe the requirements aren't as clear as I thought they were


@p45cal - when I had to work for a living, I always preferred TDL just to avoid the whole comma/quote thing

Your's is a TDL file, and mine is a true CSV; both with fields containing a comma bracketed by quotes


However, even though the OP called it a CSV, the FileFormat was TextWindows



'save the csv file
CVSWorkbook.SaveAs Filename:=SaveToDirectory & Filename, FileFormat:=xlTextWindows



If they really meant a tab deiminated file then I think the normal FileSaveAs would make it

Either way, it seems like all OP needs is to integrate the right piece of code




Option Explicit


Sub Macro2()
Worksheets("DataSheet").Copy
ActiveWorkbook.SaveAs Filename:="C:\Users\Daddy\Downloads\TXT_File.txt", FileFormat:=xlTextWindows, CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Activate
End Sub


Just remove the ".zip" since it's a way to load a TXT file

[asad9100]

Hello P45cal,

Thank you so much for your input and efforts. Yes that is exactly what I need but I need that to be with the extension .txt as the BCP can only read the .txt files. I achieved the required format by changing theFileFormat:=xlTextPrinter but then the BCP is unable to find that. Thanks again for your efforts.

[snb]

Simply this:

Sub M_snb()
Sheet1.UsedRange.Copy
With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipboard
CreateObject("scripting.filesystemobject").createtextfile("G:\OF\snb.txt").Write .gettext
End With
End Sub

[asad9100]

Hello Paul,

Thank you so much for your input and efforts. I tried the code you mentioned but somehow I am still getting the same version of text with quotes. Thanks again for your efforts I really appreciate it.

[p45cal]

@p45cal - when I had to work for a living, I always preferred TDL just to avoid the whole comma/quote thing

Your's is a TDL file, and mine is a true CSV; both with fields containing a comma bracketed by quotesPaul, I just double-checked my last attachment - there are no quote marks!

@asad9100, snb's code looks straightforward - it looks like you'll need to change the first line to just the range you want to write and the destination file name and path.

[asad9100]

Hi snb,
Thank you for your input and it was really straight forward, it worked. Thanks again and have a nice weekend. Cheers

[Paul_Hossler]

P45cal --your TDL file is correct and has no quote marks around fields containing commas

That's the whole point of using TDL files

I suspect I tried to make a last minute and wasn't thinking