Don't capitalize field names on csv export

[Stan Mathews]

Re: Don't capitalize field names on csv export

Is using an array the route I should be going?

No. Just create the header line as you want it.

flds = "MerchantFulfillmentOrderID"+","+..............

[Ed Merritt]

Re: Don't capitalize field names on csv export

Steve, thanks for your help. The file that is created contains order to be uploaded to Amazon.com. What I've found out is the field names must be exactly the same as the Excel template they supply. In other words, all lowercase or uppercase won't work.
A fieldname MerchantFulfillmentOrderID must have caps just as I've written here.

So, I'm back to square one. I'm assuming the solution is to use an array in place of

flds = strtran(lower(tbl.field_name_get()),crlf(),",")'writes all field names

Is using an array the route I should be going?

[Stan Mathews]

Re: Don't capitalize field names on csv export

Ed,

I think this answers your specific case.

dim tbl as P
tbl = table.open("fba_details")
flds = strtran(lower(tbl.field_name_get()),crlf(),",")'writes all field names
file_pointer = file.create("c:\output.txt", FILE_RW_EXCLUSIVE)
tbl.fetch_first()
file_pointer.write_line(flds)
while .NOT. tbl.fetch_eof()
	towrite = alltrim(tbl.Merchantfu)+","
	towrite = towrite + alltrim(tbl.Displayabl)+","
	towrite = towrite + alltrim(tbl.Displayab0)+","
	towrite = towrite + alltrim(tbl.Merchantsk)+","
	towrite = towrite + alltrim(str(tbl.Quantity,8))+","
	towrite = towrite + alltrim(tbl.Merchantf0)+","
	towrite = towrite + alltrim(tbl.Giftmessag)+","
	towrite = towrite + alltrim(tbl.Displayab1)+","
	towrite = towrite + alltrim(str(tbl.Perunitdec,8,2))+","
	towrite = towrite + alltrim(tbl.Displayab2)+","
	towrite = towrite + alltrim(tbl.Deliverysl)+","
	towrite = towrite + alltrim(tbl.Addressnam)+","
	towrite = towrite + alltrim(tbl.Addressfie)+","
	towrite = towrite + alltrim(tbl.Addressfi0)+","
	towrite = towrite + alltrim(tbl.Addressfi1)+","
	towrite = towrite + alltrim(tbl.Addresscit)+","
	towrite = towrite + alltrim(tbl.Addresscou)+","
	towrite = towrite + alltrim(tbl.Addresssta)+","
	towrite = towrite + alltrim(tbl.Addresspos)+","
	towrite = towrite + alltrim(tbl.Addresspho)+","
	towrite = towrite + alltrim(tbl.Notificati)+","
	towrite = towrite + alltrim(convert_type(tbl.Successful,"C"))+","
	towrite = towrite + alltrim(tbl.Orderdetai)
    file_pointer.write_line(towrite)
    tbl.fetch_next()
end while
file_pointer.flush()
file_pointer.close()
tbl.close()

[Stan Mathews]

Re: Don't capitalize field names on csv export

Crap! I got wound up with the Excel portion and forgot the original issue.

Usage would be

ex_lowcase("c:\test.csv","c:\newlow.csv")

and the line

xlApp.ActiveWorkbook.SaveAs(newname,-4143)

should be

xlApp.ActiveWorkbook.SaveAs(newname,6) '6 is csv

[Stan Mathews]

Re: Don't capitalize field names on csv export

Give this a try instead. Created a function which opens an excel file and makes the first row lowercase. Attachment should be an importable version.

Usage would be like.

ex_lowcase("c:\test.xls","c:\newlow.xls")

Where "c:\test.xls" is the file with uppercase in row 1 and "c:\newlow.xls" is the converted file with lowercase in row 1. (Original file is not changed.)

FUNCTION ex_lowcase AS L (excelname AS C, newname AS C )
	ex_lowcase = .F.
	Dim xlApp as p
	dim myCell as c
	xlApp = ole.create("Excel.Application")
	xlApp.Workbooks.Open(excelname)
	'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
	IF xlApp.ActiveWorkbook.ReadOnly
		ui_msg_box("Alert","Halting because file is read-only.",UI_OK)
		xlapp.DisplayAlerts = .F.
		xlApp.Workbooks.Close()
		xlapp.DisplayAlerts = .T.
		xlApp.Quit()
		delete xlApp
		exit function
	END IF

	'establish last row value
	lastrow = xlApp.Activecell.SpecialCells("11").Row 
	lastcol = xlApp.Activecell.SpecialCells("11").Column
	lastcell = xlindx_to_txt(lastcol)+lastrow 
	lasthead = xlindx_to_txt(lastcol)+1 
	xlApp.Rows("1:1").Select()
	xlApp.Selection.Insert(-4121)
	for qx = 1 to lastcol
		xlApp.Cells(1,qx).Select()
		xlApp.ActiveCell.FormulaR1C1 = "=LOWER(R[1]C)"
	next qx
	xlApp.Range("A1:"+lasthead).Select()
    xlApp.Application.CutCopyMode = .F.
    xlApp.Selection.Copy()
    xlApp.Range("A1").Select()
    xlApp.Selection.PasteSpecial(-4163,-4142,.F.,.F.)
    XlApp.Rows("2:2").Select()
    xlApp.Application.CutCopyMode = .F.
    xlApp.Selection.Delete(-4162)
	xlApp.Range("A1:"+lastcell).Select()
	xlapp.DisplayAlerts = .F.
	xlApp.ActiveWorkbook.SaveAs(newname,-4143) '33 is a constant for Excel 4.0, dbf4 is 11
	xlApp.Workbooks.Close()
	xlapp.DisplayAlerts = .T.
	xlApp.Quit()
	ex_lowcase = .T.
END FUNCTION
FUNCTION xlindx_to_txt AS C (colnum AS N )
  If Colnum > 26 Then
    xlindx_to_txt = Chr(Int((Colnum - 1) / 26) + 64) + Chr((mod((Colnum - 1),26) ) + 65)
  Else
    xlindx_to_txt = Chr(Colnum + 64)
  End If
END FUNCTION

[Ed Merritt]

Re: Don't capitalize field names on csv export

Ed,
I'd need a sample of the table. One phony record would do.
Stan

Stan, thanks. A zip is attached.

[Stan Mathews]

Re: Don't capitalize field names on csv export

Ed,

I'd need a sample of the table. One phony record would do.

Stan

[Ed Merritt]

Re: Don't capitalize field names on csv export

..

[Ed Merritt]

Re: Don't capitalize field names on csv export

I have a similar need and have tried Stan and Mike's examples. Not being fluent in xbasic, this frustrating. I wonder why this isn't an option or default in exports?

Here's the xbasic created by the A5 export operation:

export_filename = "fba_export2.txt"
a_tbl = table.open("fba_details")
ON ERROR GOTO ERROR1102201115316500

DIM a5_operation_order as C
a5_operation_order = "Displayableorderid"
query.order = a5_operation_order
DIM a5_operation_filter as C
a5_operation_filter = "Successful_Upload=.F."
query.filter = a5_operation_filter
query.options = "I"
query.Description = "Temporary Query"
i_indx = a_tbl.query_create()

export.type = 0
export.names = .T.
export.file = export_filename
export.options = ""
export.field_sep = "	"
export.record_sep = "<CR><LF>"
export.fields = 21
export.field1 = "merchantfulfillmentorderid"
export.field2 = "displayableorderid"
export.field3 = "displayableorderdate"
export.field4 = "merchantsku"
export.field5 = "quantity"
export.field6 = "merchantfulfillmentorderitemid"
export.field7 = "giftmessage"
export.field8 = "displayablecomment"
export.field9 = "perunitdeclaredvalue"
export.field10 = "displayableordercomment"
export.field11 = "deliverysla"
export.field12 = "addressname"
export.field13 = "addressfieldone"
export.field14 = "addressfieldtwo"
export.field15 = "addressfieldthree"
export.field16 = "addresscity"
export.field17 = "addresscountrycode"
export.field18 = "addressstateorregion"
export.field19 = "addresspostalcode"
export.field20 = "addressphonenumber"
export.field21 = "notificationsemail"

a_tbl.export()


GOTO CONTINUE1102201115316500
ERROR1102201115316500:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Export Operation"+crlf()+error_text_get())
END
CONTINUE1102201115316500:
a_tbl.close()

The fields show as lowercase in xbasic but export as uppercase. I need them to be lowercase. If anyone can help me with this, I'd appreciate it.

[MikeC]

Re: Don't capitalize field names on csv export

Another possible way that I have used is via the *LINE_REPLACE() function.
http://support.alphasoftware.com/alphafivehelpv8/Functions/_LINE_REPLACE().htm

Once you have the field names in lowercase and whatever other format needed, just specify what line you want replaced by it in your csv. This way you don't have to write but one line back to the file.

[Stan Mathews]

Re: Don't capitalize field names on csv export

If you only need to write a portion of the records to the csv, tell me about the filter you currently use.

Also, this line

file_pointer = file.create("c:\a5\output.txt", FILE_RW_EXCLUSIVE)


could be

file_pointer = file.create("c:\a5\output.csv, FILE_RW_EXCLUSIVE) if you like and you can edit the c:\a5\output.

[lgbsteve]

Re: Don't capitalize field names on csv export

Thank you very much. I'll give it a try.

[Stan Mathews]

Re: Don't capitalize field names on csv export

Script your own with the file methods.

<FILE>.WRITE_LINE()

etc

Borrowing heavily from the example in the documentation....

dim tbl as P
tbl = table.open("[COLOR="Red"]c:\a5\a_sports\customer.dbf[/COLOR]")
flds = strtran(lower(tbl.field_name_get()),crlf(),",")'writes all field names
' or flds ="firstname,lastname" 'for example
file_pointer = file.create("c:\a5\output.txt", FILE_RW_EXCLUSIVE)
tbl.fetch_first()
file_pointer.write_line(flds)
while .NOT. tbl.fetch_eof()
    file_pointer.write_line([COLOR="RoyalBlue"]tbl.first_name - " " + tbl.last_name[/COLOR])
    tbl.fetch_next()
end while
file_pointer.flush()
file_pointer.close()
tbl.close()

Change the red portion to your actual table name. Change the blue portion to write the fields you want.

[lgbsteve]

The program I'm importing my csv file into requires the fields to be all lower case. My Alpha Five csv export file contains uppercase field names.