SQL server table to FoxPro table issues

Question

I am trying to get some SQL server tables into FoxPro dbf.

I successfully managed to bcp export data such that when I use FoxPro Import Wizard, the data imports correctly. But when I try to do it at the command line, I can at best import the first few columns and all the other columns disappear.

In the following directory, I put both the .CSV file and the successful DBF import table.

directory with 2 files here

What I need to figure out is how to import these CSV files from the command line in FoxPro.

I tried various combinations of;

APPEND FROM D:\work\oh\output\sqloutput.csv TYPE CSV
APPEND FROM D:\work\oh\output\sqloutput.csv DELIMITED WITH TAB

None of it works.

Any ideas? I know I have done this before, I don't remember what the trick was...

score 4 · Accepted Answer · answered Sep 19 '12 at 23:53

Wow.. I think you are going about it the wrong way... Just do a simple SQLCONNECT() to the SQL Database, run a query to the data source, stored procedure, or whatever and it's in a cursor in VFP... Then, you can just "copy" to a DBF and you are done... Something like

lnH = SQLCONNECT()

(dialog pops up for you of ODBC connections).

if not that, you could do

lnH = SQLStringConnect("Provider=... for sql server, server name, etc")

Then

if lnH > 0
   sqlexec( lnH, "select * from someTableOnServer where whateverConditon", "C_LocalCursor" )

   select C_LocalCursor
   copy to PermanentTable

   sqldisconnect( lnH )
endif

Now, if there are columns that are longer than 10 character column names, and you are NOT using a database container, you might have to adjust by either changing the query to GET the column names, or re-select the data once local to fit into 10 character non-dbc table context.

Another example to build out the query could be -- for simplicity of readability while typing, I use text/endtext such as

text to lcSQLCmd noshow pretext 1+2
   select
         t1.Column1,
         t1.AVeryLongColumnName as AVLColName,
         t1.AnotherLongColumn2  as ALC2,
         t1.SomeFlag,
         t2.ColumnFromAnotherTable as CFATbl,
         t2.AnotherCol
      from
         SQLDatabase.dbo.SQLTable1 t1
            join SQLDatabase.dbo.SQLTable2 t2
               on t1.SomeKey = t2.SomeKey
      where
         t1.SomeCriteria = 'whatever'
      order by
         t1.SomeFlag
endtext

*/ Then, to "clean up" the string for VFP to pass properly, 
*/ strip out the cr/lf from the text such as
lcSQLCmd = chrtran( lcSQLCmd, chr(13)+chr(10), "" )

*/ THEN, pass this command through sqlexec()
sqlexec( lnH, lcSQLCmd, "C_LocalCursor" )

The second is obviously MUCH easier to read what you are trying to get, and notice I've also pre-shortened the long column names to VFP 10 char non-DBC column name limit. Then, just copy out like the first example.

I greatly appreciate your insightful and detailed response. I rarely work in FoxPro anymore and didn't think about this approach at all. (sometimes, when one is very close to a working solution with another approach one doesn't even think about changing strategies.) This worked perfectly and I also learned something. Thank you. — RIDER, Sep 20 '12 at 01:40

score 1 · Answer 2 · answered Sep 19 '12 at 22:38

1

Are all of the fields in the receiving FoxPro table in the same order and the same data type as the columns in the CSV which you are trying to add into it? Are they all big enough to cope with the data? Have you ensured that none of the text fields in the CSV contain the field delimiter within their text?

answered Sep 19 '12 at 22:38

kevinw

2,088
1
14
15

Yes, yes. If a text field accidentally contained a field delimiter, I would expect the import to break mid record, having successfully imported at least the beginning X records.. (I think?) – RIDER Sep 19 '12 at 22:42

SQL server table to FoxPro table issues

2 Answers2