DC_Array2CSV

[Sbryan]

In _dcfunct.prg around line 6952 of the Array2CSV function it has:

Code: Select all

 
cLine += '="' + aData[i,j] + '"'

What is the purpose of the added = sign?


When exporting data which contains a "," and then opening using Excel it causes a new column whenever it sees a comma even though the string is enclosed in quotes. When I remove the "=" it works.

Code: Select all

 
cLine += '"' + aData[i,j] + '"'

I also had to do a strtran and replace any instances of single quotes " with double quotes "".

[rdonnay]

I am trying to remember why I did this. It had a purpose because Excel required it for some reason.

[Sbryan]

Was it something to do with preventing Excel from auto-detecting the format of a field?

[Sbryan]

Code: Select all

ACTIVE,ITEM,DESCRIP,QTY
="N",="01600676",="ABC DEF GHI JKL, 15,600MAH",0.000
="Y",="01623056",="123456, 654321, TEST, TEST",1.000
="Y",="01623057",="A,B,C,D,E,F,G,H,I",1.000

"N","01600676","ABC DEF GHI JKL, 15,600MAH",0.000
"Y","01623056","123456, 654321, TEST, TEST",1.000
"Y","01623057","A,B,C,D,E,F,G,H,I",1.000

"N",="01600676","ABC DEF GHI JKL, 15,600MAH",0.000
"Y",="01623056","123456, 654321, TEST, TEST",1.000
"Y",="01623057","A,B,C,D,E,F,G,H,I",1.000

The top set is the output from Array2CSV(). If you copy these lines to a .CSV file and then open with Excel you'll see that the commas within the strings cause new columns.

Without the = it works fine.

From what I can see it only needs to be used to prevent a string of numbers from being auto converted to a number in Excel.

[rdonnay]

From what I can see it only needs to be used to prevent a string of numbers from being auto converted to a number in Excel.

Yes, that is correct. Thanks for the reminder.

It appears that I will need to change the code.
I'm not sure that your solution will work because the = still needs to be there for a string of numbers.

It will save me some time if you give me an array that fails to work with.

[Sbryan]

Here's a simple one

Code: Select all

aTest:= {{"N","01600676","ABC DEF GHI JKL, 15,600MAH",0.000},;
             {"Y","01623056","123456, 654321, TEST, TEST",1.000},;
             {"Y","01623057","A,B,C,D,E,F,G,H,I",1.000}}
  
aHeader := { 'ACTIVE','ITEM', 'DESCRIP','QTY'}

 DC_Array2CSV("test.csv",aTest,aHeader)

[rdonnay]

Give this a try. It works for me.

Code: Select all

FOR i := 1 TO Len(aData)
  cLine := ''
  FOR j := 1 TO Len(aData[i])
   wtf aData[i,j]
    IF Valtype(aData[i,j]) = 'C' .AND. Val(aData[i,j]) > 0 .AND. !(','$aData[i,j])
      cLine += '="' + aData[i,j] + '"'
    ELSEIF Valtype(aData[i,j]) = 'C'
      cLine += '"' + aData[i,j] + '"'
    ELSEIF Valtype(aData[i,j]) = 'L'
      cLine += IIF(aData[i,j],'Y','N')
    ELSE
      cLine += DC_XtoC(aData[i,j])
    ENDIF
    IF j < Len(aData[i])
      cLine += ','
    ENDIF
  NEXT
  FWrite( nHandle, cLine + CRLF )
NEXT

[Sbryan]

Yes, that works for me too.

Would there be any reason to not handle the quote problem in there too?

Something like:

cLine += '"' + strtran(aData[i,j],'"','""') + '"'

[rdonnay]

Would there be any reason to not handle the quote problem in there too?

I'm not sure I understand the problem here.

[Sbryan]

I'm not sure I understand the problem here.

If a field has a quote symbol " it needs to be double quoted "" or it messes up.

{'Y','0123456','ITEM SIZE 10" X 12"',20}

I didn't put that in my previous sample because I was handling it when I built the array.