Solved

Command contains unrecognized phrase/keyword when importing into Foxpro table

Posted on 2008-10-27
11
2,301 Views
Last Modified: 2012-06-27
I am trying to import a CSV into a foxpro table using C# but I keep getting Command contains unrecognized phrase/keyword when I try to exectute

Here is my code, I dont know if I am doint the whole getting data from the CSV right or not


var D1="c1,c2,c3,c4,code,desc,glcomp,n1,n2,n3,rule,table,company,tccomp,ud1,ud2,ud3";
 
 
                foxCmd = new OleDbCommand("INSERT INTO hrtables(" + D1 + ")SELECT " + D1 + " FROM[Text;DATABASE=C:\abra;].[Import.txt])", foxConn);
                foxCmd.CommandType = CommandType.Text;
 
                foxReader = foxCmd.ExecuteReader();

Open in new window

0
Comment
Question by:nemesis1210
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 17

Expert Comment

by:ramrom
ID: 22811560
If [Text;DATABASE=C:\abra;].[Import.txt] is literally passed on to VFP as part of the SELECT FROM clause that is probably the problem.

Or does C# do some magic on that?
0
 
LVL 2

Author Comment

by:nemesis1210
ID: 22811586
if that is the problem how am I supposed to do it?
0
 
LVL 17

Expert Comment

by:ramrom
ID: 22811655
1) What are you using for a connectionString?

2) It is hard to tell from [Text;DATABASE=C:\abra;].[Import.txt] what your goal is.

Please describe it as thoroughly and clearly as possible.

0
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 17

Expert Comment

by:ramrom
ID: 22811659
Is Import.txt a VFP table? If not, what is its format or structure?
0
 
LVL 2

Author Comment

by:nemesis1210
ID: 22811756
Import.txt is a csv file which I want to import into hrtables.dbf connection string is below
foxConn.ConnectionString = @"Provider=vfpoledb.1;Data Source=C:\ABRA\;Collating Sequence=general;";
                foxConn.Open();

Open in new window

0
 
LVL 17

Expert Comment

by:ramrom
ID: 22812333
You can only use SQL SELECT to read from a VFP table. Not from a CSV file.

In VFP we use APPEND FROM import.txt TYPE CSV. Unfortunately one can't use that command thru VFPOLEDB.

Do you have VFP available?

Does C# offer any utilities for reading CSV files?
0
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 22813755
You can try to use EXECSCRIPT to run the commands

foxconn.Execute("EXECSCRIPT[USE C:\abra\hrtables shared]")
foxconn.Execute("EXECSCRIPT[APPEND FROM C:\abra\import.txt TYPE CSV]")

0
 
LVL 17

Assisted Solution

by:ramrom
ramrom earned 250 total points
ID: 22814062
When I try the 2nd line I get error: Feature not available.
0
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 22815443
Ramrom

Yes. you are right. All commands are not included in the OLEDB driver. APPEND FROM seems to be one of those that has not been included.

nemesis1210

Sorry. The execscript I gave is not going to work.
0
 
LVL 2

Assisted Solution

by:cthulthu
cthulthu earned 250 total points
ID: 22816532
After you create your connection string, foxConn, can you run SQL commands against the tables to get information from them?  For instance, in FoxPro, I could do the following sequence:

foxConn = sqlconnect(<data source name>)
nResult = sqlexec("select * from hrtables", foxConn, cursHRTABLES)
sqldisconnect(foxConn)

and get a cursor result of the contents of the table.  While connected, I could also perform SQL INSERTS or UPDATES to the table as well.

Is there a similar set of commands for C# to execute SQL against a database connection?  If so, you can read the contents of your TXT file into memory (like an array), and loop through them, creating the appropriately formed SQL INSERT commands line-by-line and executing them.

Also, do you have to use OLEDB?  Is it possible to use an ODBC connection?
0
 
LVL 2

Accepted Solution

by:
nemesis1210 earned 0 total points
ID: 22820187
Thanks for your input all, however due to time restraints on this project I was exploring multiple possible solutions and managed to achieve the desired result using Visual Foxpro 9
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question