Remove the double quote "" around a string when writing to a csv file?

[jxb]

To all

I have a macro which read/process data from a worksheet and write it to a csv file. I have noticed that using the command

Write #1, cell2.Value

to write the data whenever i have a string (say aaa) I get "aaa" .

Is there a way of not having the double quote around each string? If the cell contains a number it 's ok

Thanks in advance

John

 

[HaHoBe]

Hi John,

maybe use

Print #1, cell2.Value

or explain what method you are using.

Holger

 

[jxb]

Hi Holger

I simply open a file using

Open MyFile For Output As #1

then loop through each row of a pre-defined range
'do some validity checks
   'if valid row then loop through each cell of this row
   'and for each cell do
   Write #1, cell2.Value,

'when all done I close the file
Close #1

nothing fancy and works just fine for what I want
the annoying issue is that I get the double quote for all cells containing a string

maybe there is a way of writing entire (selected) range to a csv format? no looping

 

[HaHoBe]

Hi John,

just replace Write with Print to avoid the quotes, and I use Loops (and specify the formatting to meet certain criteria for the content) for each single column. Maybe copy the range you want to export into a new workbook and save as *.csv (MS-DOS) or *.txt(MS-DOS) (both open without showing quotes in my system).

Holger

 

[jxb]

Hi John,

maybe use

Print #1, cell2.Value

or explain what method you are using.

Holger

the Print command does indeed get rid of the "" but I don't get the comma

header 1 header 2 header 3 header 4 header 5
aa2 bb2 <2.3.4.5> 22 23
aa3 bb3 <10:20> 32 33 53

 

[jxb]

Hi John,

just replace Write with Print to avoid the quotes, and I use Loops (and specify the formatting to meet certain criteria for the content) for each single column. Maybe copy the range you want to export into a new workbook and save as *.csv (MS-DOS) or *.txt(MS-DOS) (both open without showing quotes in my system).

Holger

I have been thinking about this option but want to bottom out the Write/Print to a file 1st

 

[HaHoBe]

Hi John,

Sample.xlsm
	A	B	C	D	E	F	G
1	Date	Cust	County	Prod	Sold	Price	Amount
2	01.01.2023	Roberts	NSW	Pears	903	15,00	13.545,00
3	01.01.2023	Roberts	TAS	Oranges	331	15,00	4.965,00
4	01.01.2023	Smith	QLD	Mangos	299	20,00	5.980,00
5	01.01.2023	Roberts	QLD	Oranges	612	15,00	9.180,00
6	01.01.2023	Roberts	VIC	Apples	907	12,50	11.337,50
7	01.01.2023	Pradesh	TAS	Pears	107	18,00	1.926,00
8	01.01.2023	Roberts	VIC	Apples	770	12,50	9.625,00
9	01.01.2023	Smith	NT	Apples	223	12,50	2.787,50
10	01.01.2023	Smith	VIC	Oranges	132	15,00	1.980,00
11	01.01.2023	Pradesh	QLD	Oranges	669	15,00	10.035,00
12	01.01.2023	Roberts	NSW	Mangos	881	20,00	17.620,00
13	01.01.2023	Kee	SA	Pears	624	18,00	11.232,00
14	01.01.2023	Roberts	QLD	Mangos	193	20,00	3.860,00
15	01.01.2023	Smith	SA	Mangos	255	20,00	5.100,00
16	01.01.2023	Kee	QLD	Mangos	6	20,00	120,00
17	01.01.2023	Kee	VIC	Mangos	311	20,00	6.220,00
18	01.01.2023	Roberts	NT	Oranges	9	15,00	135,00
19	01.01.2023	Kee	TAS	Apples	706	12,50	8.825,00
20	01.01.2023	Kee	NT	Mangos	441	20,00	8.820,00
21	01.01.2023	Kee	WA	Oranges	936	15,00	14.040,00
Sample Export to Text

Cell Formulas
Range	Formula
G2:G21	G2	=E2*F2

Some of the columns get exported and reimported by using the following codes:

Sub ExportToTextCSV()
  
  Dim strText             As String
  Dim strPathFileName     As String
  Dim intFF               As Integer
  Dim lngRow              As Long
  
  Const strVarDel         As String = ";"
  Const strTextDel        As String = """"
  Const strDateDel        As String = "#"
  
  strPathFileName = Application.DefaultFilePath & "\Sample Export.csv"
  
  intFF = FreeFile
  
  Open strPathFileName For Output As #intFF
  lngRow = 2
  
  Do
    With ActiveSheet
      strText = strDateDel & Format(.Cells(lngRow, 1), "yyyy-mmm-dd") & strDateDel & strVarDel
      strText = strText & strTextDel & .Cells(lngRow, 2) & strTextDel & strVarDel
      strText = strText & strTextDel & .Cells(lngRow, 4) & strTextDel & strVarDel
      strText = strText & Format(.Cells(lngRow, 6), "0.00") & strVarDel
      strText = strText & Format(.Cells(lngRow, 7), "0.00")
    End With
  
    Print #intFF, strText
  
    lngRow = lngRow + 1
  
  Loop Until IsEmpty(ActiveSheet.Cells(lngRow, 1))
  
  Close #intFF

End Sub

Sub ImportFromTextFileCSV()
   
  Dim lngCol              As Long
  Dim varSplit            As Variant
  Dim strText             As String
  Dim strPathFileName     As String
  Dim intFF               As Integer
  Dim lngRow              As Long
  Dim varPart             As Variant
  
  Const strVarDel         As String = ";"
  Const strTextDel        As String = """"
  Const strDateDel        As String = "#"
  
  strPathFileName = Application.DefaultFilePath & "\Sample Export.csv"
  
  intFF = FreeFile
  
  Open strPathFileName For Input As #intFF
  lngRow = 2

  ActiveSheet.Cells.Clear

  Do
    Line Input #intFF, strText
    varSplit = Split(strText, strVarDel)
    lngCol = 1
    
    For Each varPart In varSplit
      Select Case Left(varPart, 1)
        Case strTextDel
          ActiveSheet.Cells(lngRow, lngCol) = Mid(varPart, 2, Len(varPart) - 2)
        Case strDateDel
          ActiveSheet.Cells(lngRow, lngCol) = DateValue(Mid(varPart, 2, Len(varPart) - 2))
        Case Else
          ActiveSheet.Cells(lngRow, lngCol) = varPart
      End Select
      lngCol = lngCol + 1
    Next varPart
    lngRow = lngRow + 1
  Loop Until EOF(intFF)
  
  Close #intFF

End Sub

Holger