Thanks Antonio, good suggestions and I will work through this.
>>I need to create a table from a CSV (which comes from PayPal, Bank, etc.).
>>
>>Anyone having any suggestions on the best way to do this? Just appending the CSV into an existing table does not give me a list of the column names found in the CSV.
>
>It's interesting that VFP has no support for programmatically IMPORT from CSV, when it is capable of import from more complex file formats.
>
>Anyway, one idea is to read the file and process it as an XML tree and let XMLTOCURSOR() do the dirty work. A basic implementation of this idea (no proper error handling, no great robustness) goes as follows:
>
>
>* set the call parameters as required
>IF CSVtoCursor(GETFILE(),"testcursor",CHR(9),.T.) = 0
>
> SELECT testcursor
> BROWSE
>
>ENDIF
>
>* A function to create a cursor from a CSV file
>*
>FUNCTION CSVtoCursor (CSVFile AS String, CursorName AS String, ValueSeparator AS Character, QuotedStrings AS Boolean) AS Integer
>
> LOCAL XML AS MSXML2.DOMDocument60
> LOCAL RowElement AS MSXML2.IXMLDOMElement
> LOCAL ColumnElement AS MSXML2.IXMLDOMElement
> LOCAL ARRAY FileLines[1]
> LOCAL ARRAY ColumnsData[1]
> LOCAL ARRAY ColumnsNames[1]
> LOCAL LineIndex AS Integer
> LOCAL ColumnIndex AS Integer
> LOCAL ColumnText AS String
> LOCAL ErrorHandler AS Exception
> LOCAL Result AS Integer
>
> TRY
>
> * create a DOM object to hold the temporary cursor
> m.XML = CREATEOBJECT("MSXML2.DOMDocument.6.0")
> m.XML.Appendchild(m.XML.CreateElement("VFPData"))
>
> * go through all lines in the CSV file
> FOR m.LineIndex = 1 TO ALINES(m.FileLines,FILETOSTR(m.CSVFile))
>
> * the first line is a special case
> IF m.LineIndex = 1
>
> * fetch column names and do a basic check for XML name conformity
> FOR ColumnIndex = 1 TO ALINES(m.ColumnsNames,m.FileLines[m.LineIndex],1,m.ValueSeparator)
> * do a basic syntatical adjustment - this should be enough for most cases...
> m.ColumnsNames[m.ColumnIndex] = CHRTRAN(m.ColumnsNames[m.ColumnIndex]," ","_")
> ENDFOR
>
> ELSE
>
> * other lines hold data, so create a row for pass it to the XML DOM
> m.RowElement = m.XML.CreateElement(m.CursorName)
>
> * fetch data from every column
> FOR m.ColumnIndex = 1 TO ALINES(m.ColumnsData,m.FileLines[m.LineIndex],1,m.ValueSeparator)
>
> * remove quotes, if necessary
> m.ColumnText = m.ColumnsData[m.ColumnIndex]
> IF m.QuotedStrings AND LEFT(m.ColumnText,1) == '"' AND RIGHT(m.ColumnText,1) == '"'
> m.ColumnText = SUBSTR(m.ColumnIndex,2,LEN(m.ColumnText) - 2)
> ENDIF
>
> * for each column, create a DOM element
> m.ColumnElement = m.XML.CreateElement(m.ColumnsNames[m.ColumnIndex])
>
> * set its content
> m.ColumnElement.text = m.ColumnText
>
> * and append it to the row
> m.RowElement.appendChild(m.ColumnElement)
>
> ENDFOR
>
> * all columns in the row are processed, so add the row to the DOM
> m.XML.Firstchild.Appendchild(m.RowElement)
> ENDIF
> ENDFOR
>
> * all rows are processed, then just put the XML into a cursor
> XMLTOCURSOR(m.XML.xml,m.CursorName)
>
> m.Result = 0
>
> CATCH TO m.ErrorHandler
>
> * something went wrong...
> m.Result = m.ErrorHandler.ErrorNo
>
> ENDTRY
>
> RETURN m.Result
>
>ENDFUNC
>
